Re: [SQL] sql: "LIKE" problem
Her Goo wrote: > I am using "postgresql-7.3.2-1" now! > > I met a problem when using "LIKE" in "WHERE" clause. > For example, a table named "t_test", and its data looks like below: > > # SELECT * FROM t_test; > id | c_name > + > 1 | abc\ > 2 | abc\de > (2 rows) > # SELECT * FROM t_test WHERE c_name LIKE 'abc\d%'; > id | c_name > + > (0 rows) > > I don't know why the result is "0 rows"(why not is "1 rows"), > And I have trid "LIKE 'abc\\d%'", the result is also "0 rows". You'll need to escape the backslash twice. Once for the SQL-level parsing and once for the LIKE pattern-matching itself. SELECT * FROM t_test WHERE c_name LIKE 'abcd%'; -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] lower and unicode
Are you using PostgreSQL on Windows? If so, you should read the FAQ here http://pginstaller.projects.postgresql.org/FAQ_windows.html#2.6. On Windows, UNICODE (UTF8) is not supported because Windows natively supports only UTF16 (I'm just repeating something I have read somewhere). Miroslav pginfo wrote: Hi , I tested the lower with unicode on 8.0.1 and find it not to work. If I have only latin symbols it is working well, but if I try to use also cyrillic the lower simpli ignore this symbols and all stay as is. I readet that this will work on 8.x . Exists some one using lower/upper + unicode and where can I find info about unicode status with pg? regards, ivan. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] finding schema of table that called a trigger
Jeff Hoffmann wrote: Is there a variable defined that has the schema of the table that called the trigger (like TG_RELNAME = table name)? I didn't see anything in the documentation. Is the only way to get that to look it up with TG_RELID? I must admit I don't know of one. Hmm - it would obviously be simple enough to add to the interface, but I presume there is some overhead for each parameter you supply. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] How to cast VARCHAR to BYTEA and vice-versa?
On Tue, 2005-03-08 at 07:31 -0800, Moran.Michael wrote: > Hello all, > > I have a table with a VARCHAR column that I need to convert to a BYTEA. > > How do I cast VARCHAR to BYTEA? have you looked at the encode() and decode() functions ? http://www.postgresql.org/docs/7.4/interactive/functions-string.html gnari ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] group by before and after date
I have 2 tables 1 has a date field and component need by that date and the other has all the upcoming orders. I am trying to build a query that will give me the Date and ComponentNeed and also how many components have been ordered before that date and how many after. PostGreSQL is telling me I need to group on DatePromisedBy. I have tried a number of different possibilities which haven't worked and now I have run into brain freeze. Any help would be appreciated. You could : SELECT ..., sum( stuff ), ..., (DatePromisedBy > a_particular_date) as after GROUP BY after You'll get two lines, one the sum of things before a_particular_date, one of things after a_particular_date. Look in the 'after' field to know which is which. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Newbie wonder...
If you want to add a SERIAL field to an existing table, create a sequence and then create an integer field with default nextval(seq) and postgres will fill it automatically. The order in which it will fill it is not guaranteed though ! However, you might also like to de-dupe your data once it's in the additional tables, thus you might need more complicated measures. (2) How should I go to create a sequence for an existing table? For all futures data entry, after this conversion, I want the unique ID for each row to come from a sequence, but if I know how to create a table using serial, I am not sure how to modify one for this. Thanks, Bernard ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] lower and unicode
Hi, I am uusing pg 8.0.1 on FreeBSD 5.3 but I am ready t use the version taht supports correct unicode. regards, ivan. Miroslav Šulc wrote: Are you using PostgreSQL on Windows? If so, you should read the FAQ here http://pginstaller.projects.postgresql.org/FAQ_windows.html#2.6. On Windows, UNICODE (UTF8) is not supported because Windows natively supports only UTF16 (I'm just repeating something I have read somewhere). Miroslav pginfo wrote: Hi , I tested the lower with unicode on 8.0.1 and find it not to work. If I have only latin symbols it is working well, but if I try to use also cyrillic the lower simpli ignore this symbols and all stay as is. I readet that this will work on 8.x . Exists some one using lower/upper + unicode and where can I find info about unicode status with pg? regards, ivan. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to cast VARCHAR to BYTEA and vice-versa?
"Moran.Michael" wrote: > > Hello all, > > I have a table with a VARCHAR column that I need to convert to a BYTEA. > > How do I cast VARCHAR to BYTEA? > > The following doesn't seem to work as it yields the 'cannot cast varchar to > bytea' error message: > > varchar_data::bytea > > On the same topic, how do I do the reverse, that is, how to I cast from > BYTEA back to VARCHAR? > > Thanks, > -Michael Moran > > As far as I remember varchar_data::TEXT::BYTEA works. Vice versa dto. Be aware BYTEA data may contain character codes unconvertable to VARCHAR, especially '\0'. Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] lower and unicode
pginfo wrote: Hi, I am uusing pg 8.0.1 on FreeBSD 5.3 but I am ready t use the version taht supports correct unicode. I think that should be fine. I use PostgreSQL 8.0.1 on Linux (Gentoo) without these problems (I used cs_CZ.utf8 to init my db). What you write seems to me that you have your database initialized to something else than xy_XY.utf8 which must be used to have databases working correctly with UNICODE. Try 'locale -a' and choose your locale that has *.utf8 to initialize the database. Then things should be O.K. :-) I don't remember I've seen some note in documentation that users must use for databases the same encoding as they used for initdb. If this is true, it would be time saving to mention this in documentation (if it is not already there). regards, ivan. Miroslav begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] How does the planner treat a table function.
Hi, we have got some tables (uw?) and functions. One function is defined like get_abc(): SELECT a,b,c from table_x; What happens if I query something like SELECT a,b from get_abc() where a=5; while table_x is rather big? Will PSQL at first query all records of table_x and then apply a where a=5 OR will PSQL integrate it to a shorter query? (In case there will be of course an intex over a to prevent sequential search) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] lower and unicode
Hi, I am using --no-locale by init db. I readet that if I am using some locale the pg will work very slow. Have you noticed some speed penalty by using cs_CZ.utf8. regards, ivan. Miroslav Šulc wrote: pginfo wrote: Hi, I am uusing pg 8.0.1 on FreeBSD 5.3 but I am ready t use the version taht supports correct unicode. I think that should be fine. I use PostgreSQL 8.0.1 on Linux (Gentoo) without these problems (I used cs_CZ.utf8 to init my db). What you write seems to me that you have your database initialized to something else than xy_XY.utf8 which must be used to have databases working correctly with UNICODE. Try 'locale -a' and choose your locale that has *.utf8 to initialize the database. Then things should be O.K. :-) I don't remember I've seen some note in documentation that users must use for databases the same encoding as they used for initdb. If this is true, it would be time saving to mention this in documentation (if it is not already there). regards, ivan. Miroslav ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] lower and unicode
pginfo wrote: Hi, I am using --no-locale by init db. I readet that if I am using some locale the pg will work very slow. I don't remember reading it will be very slow. I just remember that I've read it will slow down some things (which I think is logical in this case). Have you noticed some speed penalty by using cs_CZ.utf8. When using PostgreSQL, I need correct collating order for strings so I have no other choice. I didn't do any speed tests but I think if you need correct behaviour for your locale, you should use your locale. Just try it and you will see the results. You can then make a decision for yourself. I think you won't be the only one using locale for your database ;-) regards, ivan. Miroslav begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Newbie wonder...
On Mon, 2005-03-14 at 10:02 +0100, PFC wrote: > If you want to add a SERIAL field to an existing table, create a > sequence > and then create an integer field with default nextval(seq) and postgres > will fill it automatically. The order in which it will fill it is not > guaranteed though ! With 8.0, Alter Table supports most complicated commands including the addition of SERIAL directly. alter table abc add column bob serial NOT NULL UNIQUE; -- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] How to cast VARCHAR to BYTEA and vice-versa?
Christoph, is that recent functionality? I'm running 7.4 and I get: ERROR: cannot cast type text to bytea I get similar errors when trying to go the other direction. On Mon, 14 Mar 2005 11:32:26 +0100, Christoph Haller <[EMAIL PROTECTED]> wrote: > "Moran.Michael" wrote: > > > > Hello all, > > > > I have a table with a VARCHAR column that I need to convert to a BYTEA. > > > > How do I cast VARCHAR to BYTEA? > > > > The following doesn't seem to work as it yields the 'cannot cast varchar to > > bytea' error message: > > > > varchar_data::bytea > > > > On the same topic, how do I do the reverse, that is, how to I cast from > > BYTEA back to VARCHAR? > > > > Thanks, > > -Michael Moran > > > > > As far as I remember > varchar_data::TEXT::BYTEA > works. Vice versa dto. > Be aware BYTEA data may contain character codes > unconvertable to VARCHAR, especially '\0'. > > Regards, Christoph > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq > ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Inserting values in arrays
I have the following issue.
Given the following tables:
CREATE TABLE test ( details varchar[]);
CREATE TABLE test2 ( textvalue1 varchar,
textvalue2 varchar);
INSERT INTO test2 VALUES ('Hello1',
'World1');
INSERT INTO test2 VALUES ('hello2',
'World2');
I would like to insert a row in test
for each row of the test2 table i.e.
INSERT INTO test (details) SELECT test2.textvalue1,
test2.textvalue2 FROM test2
and I am expecting the following rows
in test
{'Hello1', 'World1'}
{'Hello2', 'World2'}
The above syntax is giving an error.
How can this be done in postgres ?
Postgres version I am using is 7.3.4
Regards
Robert
[SQL] Generic Function
Hi, Can I built a generic function like: CREATE FUNCTION f (text) RETURNS TEXT as $$ return 'select * from $1'; $$ I know its impossible as writed. Also I have looked for EXECUTE procedure but it not run the correct function. Is there a way to construct this clause? Using plpgsql/pltcl/anything ??? Thanks ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] postgresql 8.01, plpgsql
i have the following function in plpgsql giving stynax errors all over the place. i have doen createlang on the db, as far as i can see i'm right. is there anything obviously wrong? one thing to note is i followed this example http://www.zigo.dhs.org/postgresql/#insert_or_update and it gives the same errors. "ERROR: unterminated dollar-quoted string at or near "$$ BEGIN LOOP" CREATE OR REPLACE FUNCTION insert_update_daily_takings (ID BIGINT, TillName VARCHAR, Tape NUMERIC(10,2), Cash NUMERIC(10,2), GM NUMERIC(10,2), VenueManager NUMERIC(10,2), AsstManager NUMERIC(10,2), BarManager NUMERIC(10,2), PRCards NUMERIC(10,2), otherPromo NUMERIC(10,2), Functions NUMERIC(10,2), Accounts NUMERIC(10,2), Spill NUMERIC(10,2), Orings NUMERIC(10,2), Variance NUMERIC(10,2) ) RETURNS VOID AS $$ BEGIN LOOP UPDATE daily_takings SET till_name = TillName, tape = Tape, cash = Cash, promo_manager = GM, venue_manager = VenueManager, asst_manager = AsstManager, bar_manager = BarManager, pr_cards = PRCards, other_promo = otherPromo, functions = Functions, accounts = Accounts, spill = Spill, o_rings = Orings, variance = Variance WHERE id = ID AND till_name = TillName; IF found THEN RETURN; END IF; BEGIN INSERT INTO daily_takings (id, till_name, tape, cash, promo_manager, venue_manager, asst_manager, bar_manager, pr_cards, other_promo, functions, accounts, spill, o_rings, variance) VALUES (ID, TillName, Tape, Cash, GM, VenueManager, AsstManager, BarManager, PRCards, otherPromo, Functions, Accounts, Spill, Orings, Variance); RETURN; EXCEPTION WHEN unique_violation THEN NULL END; END LOOP; END; $$ LANGUAGE plpgsql; ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] postgresql 8.01, plpgsql
On Tue, Mar 15, 2005 at 11:35:13AM +1000, Timothy Smith wrote: > i have the following function in plpgsql giving stynax errors all over > the place. When I load the function you posted I get this: test=> \i foo.sql psql:foo.sql:87: ERROR: syntax error at or near "END" at character 2851 psql:foo.sql:87: LINE 83: END; psql:foo.sql:87: ^ If I add a semicolon to the NULL statement in the exception handler then it loads successfully. > one thing to note is i followed this example > http://www.zigo.dhs.org/postgresql/#insert_or_update and it gives the > same errors. I get no errors loading the example in the "Insert or Update" section of that page. > "ERROR: unterminated dollar-quoted string at or near "$$ > BEGIN > LOOP" This looks like the parser is reaching EOF before the end of the function body. How are you loading the function? I usually store code in a file and use "psql -f filename" from the shell or "\i filename" from the psql prompt. If you're not already doing that, what happens when you try it? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] postgresql 8.01, plpgsql
Michael Fuhr wrote: On Tue, Mar 15, 2005 at 11:35:13AM +1000, Timothy Smith wrote: i have the following function in plpgsql giving stynax errors all over the place. When I load the function you posted I get this: test=> \i foo.sql psql:foo.sql:87: ERROR: syntax error at or near "END" at character 2851 psql:foo.sql:87: LINE 83: END; psql:foo.sql:87: ^ If I add a semicolon to the NULL statement in the exception handler then it loads successfully. one thing to note is i followed this example http://www.zigo.dhs.org/postgresql/#insert_or_update and it gives the same errors. I get no errors loading the example in the "Insert or Update" section of that page. "ERROR: unterminated dollar-quoted string at or near "$$ BEGIN LOOP" This looks like the parser is reaching EOF before the end of the function body. How are you loading the function? I usually store code in a file and use "psql -f filename" from the shell or "\i filename" from the psql prompt. If you're not already doing that, what happens when you try it? ok i tried loading it from a file like you suggested and this is what i got for MY function \i /home/timothy/function psql:/home/timothy/function:35: ERROR: unterminated dollar-quoted string at or near "$$ BEGIN LOOP UPDATE daily_takings SET till_name = TillName, tape = Tape, cash = Cash, promo_manager = GM, venue_manager = VenueManager, asst_manager = AsstManager, bar_manager = BarManager, pr_cards = PRCards, other_promo = otherPromo, functions = Functions, accounts = Accounts, spill = Spill, o_rings = Orings, variance = Variance WHERE id = ID AND till_name = TillName;" at character 604 psql:/home/timothy/function:37: ERROR: syntax error at or near "IF" at character 3 psql:/home/timothy/function:38: ERROR: syntax error at or near "IF" at character 7 psql:/home/timothy/function:70: ERROR: syntax error at or near "INSERT" at character 13 psql:/home/timothy/function:71: ERROR: syntax error at or near "RETURN" at character 4 psql:/home/timothy/function:74: ERROR: syntax error at or near "EXCEPTION" at character 3 psql:/home/timothy/function:75: ERROR: syntax error at or near "LOOP" at character 6 psql:/home/timothy/function:76: WARNING: there is no transaction in progress COMMIT psql:/home/timothy/function:78: ERROR: unterminated dollar-quoted string at or near "$$ LANGUAGE plpgsql;" at character 1 and similarly for the example i got \i /home/timothy/function psql:/home/timothy/function:1: ERROR: unterminated dollar-quoted string at or near "$$ BEGIN LOOP UPDATE db SET b = data WHERE a = key;" at character 63 psql:/home/timothy/function:1: ERROR: syntax error at or near "IF" at character 2 psql:/home/timothy/function:1: ERROR: syntax error at or near "IF" at character 6 psql:/home/timothy/function:2: ERROR: syntax error at or near "INSERT" at character 8 psql:/home/timothy/function:2: ERROR: syntax error at or near "RETURN" at character 2 psql:/home/timothy/function:2: ERROR: syntax error at or near "EXCEPTION" at character 2 ---(end of broadcast)--- TIP 3: 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] re postgresql 8.01 plpgsql
problem sovled. i was still in psql from 7.4, i only upgraded this morning and left it on there. it was the source of all my grief. ---(end of broadcast)--- TIP 3: 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] postgresql 8.01, plpgsql
On Tue, Mar 15, 2005 at 01:38:00PM +1000, Timothy Smith wrote: > ok i tried loading it from a file like you suggested and this is what i > got for MY function > > \i /home/timothy/function > psql:/home/timothy/function:35: ERROR: unterminated dollar-quoted > string at or near "$$ Could you attach the files you're reading? It might be helpful if we could see everything you're trying to execute. What version of PostgreSQL are you using? Presumably 8.X since you're using dollar quoting. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] select multiple immediate values, but in multiple rows
Hello all. I'd like to write a query does a set subtraction A - B, but A is is a set of constants that I need to provide in the query as immediate values. I thought of something like select a from (1,2,3.4) except select col_name from table; but I don't know the syntax to specify my set of constants. I thought of doing select 1 union select 2 union select 3 union ... except ... but I figure there must be a better way. The size of the set would be anywhere between 1 and about 5 or 6 elements. Is there a better way to do this? Thanks. Luca ---(end of broadcast)--- TIP 8: explain analyze is your friend
