[SQL] PostgreSQL help
Hey!! I have a problem and i hope this is the correct section to post it!!! When i use the COPY Table Name FROM Location command to insert values to a table using a txt file, the programme gives me errors when he finds letter as "ò, è, à" inside the txt file. But when i use the insert command and I write some word with any of the letters it seems to work. Why is that ?? What can i do to resolve it?? i use the programme PgAdmin 3 to write my commands... Thank you. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] PostgreSQL help
Check your database encoding, client encoding, and the encoding you use in your file. If your database is UNICODE, pgadmin will convert accordingly, but your file has to be in the right encoding. On Mon, 29 Aug 2005 12:27:41 +0200, Shavonne Marietta Wijesinghe <[EMAIL PROTECTED]> wrote: Hey!! I have a problem and i hope this is the correct section to post it!!! When i use the COPY Table Name FROM Location command to insert values to a table using a txt file, the programme gives me errors when he finds letter as "ò, è, à" inside the txt file. But when i use the insert command and I write some word with any of the letters it seems to work. Why is that ?? What can i do to resolve it?? i use the programme PgAdmin 3 to write my commands... Thank you. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Negative lookbehind assertions in regexs
I'd like a regex that matches 'CD' but not 'ABCD' in any part of the regex. In Perl I'd use a negative lookbehind assertion (?Julian ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Negative lookbehind assertions in regexs
On Mon, Aug 29, 2005 at 14:11:37 +0100,
Julian Scarfe <[EMAIL PROTECTED]> wrote:
> I'd like a regex that matches 'CD' but not 'ABCD' in any part of the regex.
>
> Is there a workaround that allows me to do this as a single regex?
>
> I know I could and together a ~ and !~ like this
>
> # select ('CD' ~ 'CD') and ('CD' !~ 'ABCD');
> ?column?
> --
> t
> (1 row)
>
> # select ('ABCD' ~ 'CD') and ('ABCD' !~ 'ABCD');
> ?column?
> --
> f
> (1 row)
The above code won't work because there could be both CD and ABCD in the
string. What you want to do is match all of the valid possibilities.
Something like:
(^.?CD)|([^B]CD)|([^A]BCD)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[SQL] sqlstate 02000 while declaring cursor/freeing prepared statements
I'm new to the PostgreSQL community so please pardon what is probably a silly question. Also, this is my first attempt at posting so you might have seen this already (Sorry!) ... I'm in the process of porting Informix ESQL to PostgreSQL. I occasionally get sqlcode = 100 and sqlstate = 02000 when declaring cursors or freeing prepared statements. Is this normal? For example: $declare loop1 cursor with hold for select distinct ( tabname ) from meta ; results in sqlca.sqlcode = 100 and sqlca.sqlstate = '02000' -- Andrew Rost National Operational Hydrologic Remote Sensing Center (NOHRSC) National Weather Service, NOAA 1735 Lake Dr. West, Chanhassen, MN 55317-8582 Voice: (952)361-6610 x 234 Fax: (952)361-6634 [EMAIL PROTECTED] http://www.nohrsc.nws.gov ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Numerical variables in pqsql statements
Michael, PL/pgSQL variable interpolation works similarly to that in other popular programming languages. If you have a statement -- whether it's PERFORM, SELECT INTO, or EXECUTE -- a variable will get interpolated during parsing if not escaped in a string. Per the documentation, dynamic values in dynamic queries require special handling since they might themselves contain quotes: http://www.postgresql.org/docs/8.0/static/plpgsql- statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN But your temp table statement below is fine if you know that there will never be quotes in refer_num. And it would work similarly if you built it dynamically via string concatenation as a query to be executed with quote_literal escaping for refer_num. Typically, you need to protect yourself against user input to a function. If you're computing values in the function body that you know to be safe or passing in safe values generated elsewhere in the application, you're less likely to need to quote your variables explicitly or to build queries dynamically. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax) On Aug 21, 2005, at 7:43 PM, Michael Schmidt wrote: Folks, I'm sure this is dumb, but I'm a little confused about use of numerical variables in pqsql. It was my impression one had to use EXECUTE on a concatenated string including quote_literal() for the variable containing the value. This can be quite a bit of trouble. I just wrote a function that included the statement : CREATE TEMPORARY TABLE author_names AS SELECT ra.ref_auth_key, a.last_name, a.first_name FROM ref_auth ra INNER JOIN authors a ON (ra.author_num = a.author_key) WHERE ra.ref_num = refer_num; where refer_num is integer. This worked (surprisingly, to me). So, what is the rule regarding variables? Would this query work if I concatenated a string together, including quote_literal (refer_num) and then EXECUTEd it? Thanks and sorry to be so stupid. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] sqlstate 02000 while declaring cursor/freeing prepared statements
On Mon, Aug 29, 2005 at 02:28:24PM -0500, andy rost wrote: > I'm in the process of porting Informix ESQL to PostgreSQL. I > occasionally get sqlcode = 100 and sqlstate = 02000 when declaring > cursors or freeing prepared statements. Is this normal? For example: > > $declare loop1 cursor with hold for > select distinct ( tabname ) > from meta ; > > results in sqlca.sqlcode = 100 and sqlca.sqlstate = '02000' Could you provide a little more context? Are you using ECPG? What version of PostgreSQL are you using? When you say that you "occasionally" see this behavior, do you mean that you see it consistently with some commands and not with others, or do you mean that the same command sometimes does it and sometimes not? Do you have a reproducible test case? That is, everything that somebody could do to reproduce the behavior on their own system? -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] question
Matt,
In PostgreSQL 8.0.3, I see:
postgres=# select nullif( '1', '' );
nullif
1
(1 row)
postgres=# select nullif( '', '' ) is null;
?column?
--
t
(1 row)
What behavior are you expecting?
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open Source: Open Your i™
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)
On Aug 24, 2005, at 12:05 AM, Matt A. wrote:
I have a rating section on a website. It has radio
buttons with values 1-5 according to the rating level.
Lastly there is a null option for n/a. We use null for
n/a so it's excluded from the AVG() calculations.
We used nullif('$value','') on inserts in mssql. We
moved to postgres and love it but the nullif() doesn't
match empty strings to each other to return null other
than a text type, causing an error. This is a major
part of our application.
AKA nullif('1','') would insert 1 as integer even
though wrapped in ''. Also nullif('','') would
evaluate NULL and insert the "not a value"
accordingly.
Is there a workaround for this so it doesn't break our
rating system? We cannot always enter a value for a
integer column. Is there any other way to accomplish
this? Please help.
__
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail
---(end of
broadcast)---
TIP 2: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] sqlstate 02000 while declaring cursor/freeing prepared
Sure. I'm using ECPG (ecpg -t -r no_indicator -C INFORMIX) in a TRU64 operating system for PostgreSQL version 8.0.2. By occasionally, I mean that I don't observe this problems for each declare and free statement that I've encoded - only for a subset of those commands. But I do observe this problem consistently within that subset - the same command fails every time for a small number of declares and frees. Unfortunately, I do not have a case that can be easily reproduced. The commands that fail are part of a large system. It seems like an odd error for these kinds of commands. Thanks ... Andy Michael Fuhr wrote: On Mon, Aug 29, 2005 at 02:28:24PM -0500, andy rost wrote: I'm in the process of porting Informix ESQL to PostgreSQL. I occasionally get sqlcode = 100 and sqlstate = 02000 when declaring cursors or freeing prepared statements. Is this normal? For example: $declare loop1 cursor with hold for select distinct ( tabname ) from meta ; results in sqlca.sqlcode = 100 and sqlca.sqlstate = '02000' Could you provide a little more context? Are you using ECPG? What version of PostgreSQL are you using? When you say that you "occasionally" see this behavior, do you mean that you see it consistently with some commands and not with others, or do you mean that the same command sometimes does it and sometimes not? Do you have a reproducible test case? That is, everything that somebody could do to reproduce the behavior on their own system? -- Andrew Rost National Operational Hydrologic Remote Sensing Center (NOHRSC) National Weather Service, NOAA 1735 Lake Dr. West, Chanhassen, MN 55317-8582 Voice: (952)361-6610 x 234 Fax: (952)361-6634 [EMAIL PROTECTED] http://www.nohrsc.nws.gov ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] sqlstate 02000 while declaring cursor/freeing prepared
I worked on my problem a little further and have a little more information to share. The declare statement that fails consistently follows a select statement that returns zero rows (and sqlcode 100 and sqlstate '02000'). If I ommit the select statement from the code or set sqlcode to 0 before calling the declare statement, the declare statement works fine. It appears as though the declare statement is not updating the sqlca structure. Is this by design for the ecpg options that I'm using? Did I pick up bad habits while using Informix? Thanks ... Andy andy rost wrote: Sure. I'm using ECPG (ecpg -t -r no_indicator -C INFORMIX) in a TRU64 operating system for PostgreSQL version 8.0.2. By occasionally, I mean that I don't observe this problems for each declare and free statement that I've encoded - only for a subset of those commands. But I do observe this problem consistently within that subset - the same command fails every time for a small number of declares and frees. Unfortunately, I do not have a case that can be easily reproduced. The commands that fail are part of a large system. It seems like an odd error for these kinds of commands. Thanks ... Andy Michael Fuhr wrote: On Mon, Aug 29, 2005 at 02:28:24PM -0500, andy rost wrote: I'm in the process of porting Informix ESQL to PostgreSQL. I occasionally get sqlcode = 100 and sqlstate = 02000 when declaring cursors or freeing prepared statements. Is this normal? For example: $declare loop1 cursor with hold for select distinct ( tabname ) from meta ; results in sqlca.sqlcode = 100 and sqlca.sqlstate = '02000' Could you provide a little more context? Are you using ECPG? What version of PostgreSQL are you using? When you say that you "occasionally" see this behavior, do you mean that you see it consistently with some commands and not with others, or do you mean that the same command sometimes does it and sometimes not? Do you have a reproducible test case? That is, everything that somebody could do to reproduce the behavior on their own system? -- Andrew Rost National Operational Hydrologic Remote Sensing Center (NOHRSC) National Weather Service, NOAA 1735 Lake Dr. West, Chanhassen, MN 55317-8582 Voice: (952)361-6610 x 234 Fax: (952)361-6634 [EMAIL PROTECTED] http://www.nohrsc.nws.gov ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] sqlstate 02000 while declaring cursor/freeing prepared
On Mon, Aug 29, 2005 at 04:39:36PM -0500, andy rost wrote:
> I worked on my problem a little further and have a little more
> information to share. The declare statement that fails consistently
> follows a select statement that returns zero rows (and sqlcode 100 and
> sqlstate '02000'). If I ommit the select statement from the code or set
> sqlcode to 0 before calling the declare statement, the declare statement
> works fine.
>
> It appears as though the declare statement is not updating the sqlca
> structure. Is this by design for the ecpg options that I'm using? Did I
> pick up bad habits while using Informix?
Apparently since PostgreSQL doesn't actually have an OPEN statement,
the ECPG code generator doesn't issue the DECLARE until the code
OPENs the cursor. Observe:
% cat foo.pgc
int
main(void)
{
EXEC SQL CONNECT TO DEFAULT;
printf("before DECLARE\n");
EXEC SQL DECLARE curs CURSOR FOR SELECT 1;
printf("after DECLARE, before OPEN\n");
EXEC SQL OPEN curs;
printf("after OPEN\n");
EXEC SQL CLOSE curs;
EXEC SQL DISCONNECT;
return 0;
}
% ecpg foo.pgc
% cat foo.c
...
printf("before DECLARE\n");
/* declare curs cursor for select 1 */
#line 7 "foo.pgc"
printf("after DECLARE, before OPEN\n");
{ ECPGdo(__LINE__, 0, 1, NULL, "declare curs cursor for select 1 ",
ECPGt_EOIT, ECPGt_EORT);}
#line 9 "foo.pgc"
printf("after OPEN\n");
...
Notice that "after DECLARE" actually comes *before* the DECLARE
statement is issued, so references to sqlcode and sqlstate would
see values from a previous command. I don't know if DECLARE is
supposed to affect error codes or not; I'd have to consult the
standard.
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
