Re: [SQL] UPDATE & LIMIT together?
Hmm,, thanks so far, it helped. The query is horrible slow on full tables (>100.000 rows). Is there any other solution? I just want to have the 'next 10 entries' WHERE state=10 and update state=20. (so that on the next request i or another process only gets the new entires in queue). My query now looks like: UPDATE queue SET state=20 WHERE (id) IN (SELECT id FROM queue LIMIT 10) -tp Bruce Momjian([EMAIL PROTECTED])@Wed, Aug 28, 2002 at 01:01:36PM -0400: > > You have to use a subquery returning the tables primary key to the > UPDATE: > > UPDATE tab SET x=1 > WHERE (primkey, col) IN ( > SELECT primkey,col FROM tab > ORDER BY col > LIMIT 10) > > --- > > tp wrote: > -- Start of PGP signed section. > > Hi > > > > I want to SELECT at max. 10 rows and SET a variable for the > > select 10 rows with the same query. > > > > Under mysql i can use: > > UPDATE table SET uniq_iq=12345 LIMIT 10 > > SELECT * FROM table WHERE uniq_id=1234; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] convert sum (interval) to seconds
How can I convert sum(interval) to seconds? select sum(interval_field) from tablename where condition=condition; ID | LOGIN | INTERVAL -- 1 | JOHN | 00:00:18 2 | JOHN | 00:45:10 If I say, select sum(interval) from tablename where login='john'; it will give me 00:45:28 (That's 45 mins, and 28 secs). How can I convert that to seconds? Can I possibly do it in just one query? Thank you ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] convert sum (interval) to seconds
On Tue, 2002-09-03 at 11:45, Doppelganger wrote: > How can I convert sum(interval) to seconds? ... > If I say, > select sum(interval) from tablename where login='john'; > it will give me 00:45:28 (That's 45 mins, and 28 secs). > How can I convert that to seconds? Can I possibly do it > in just one query? Thank you Here's one way; I'm not sure if there may not be something more efficient, though: junk=# select sum(if) from i; sum -- 00:45:28 (1 row) junk=# select extract (minute from sum(if)) * 60 + extract (second from sum(if)) from i; ?column? -- 2728 (1 row) -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "And he said unto his disciples, Therefore I say unto you, Take no thought for your life, what ye shall eat; neither for the body, what ye shall put on. For life is more than meat, and the body is more than clothing. Consider the ravens, for they neither sow nor reap; they have neither storehouse nor barn; and yet God feeds them; how much better you are than the birds! Consider the lilies, how they grow; they toil not, they spin not; and yet I say unto you, that Solomon in all his glory was not arrayed like one of these. If then God so clothe the grass, which is to day in the field, and tomorrow is cast into the oven; how much more will he clothe you, O ye of little faith? And seek not what ye shall eat, or what ye shall drink, neither be ye of doubtful mind. But rather seek ye the kingdom of God; and all these things shall be added unto you." Luke 12:22-24; 27-29; 31. ---(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] RFC822 Checker
On Fri, Aug 30, 2002 at 02:07:28PM +, Matthew Price wrote: > Does anyone know of an SQL function that will check a string for > compliance with the RFC822 mail address spec? I have a script that > sends mail from a db (no, I am not a spammer) but I often have mails > sitting in my queue because the MTA (correctly) refuses to process > some of the bogus things that users enter by mistake. you might want to check: ftp://ftp.cpan.org/CPAN/authors/Tom_Christiansen/scripts/ckaddr.gz since this is in pure perl, making a pl/perl function should be pretty simple. depesz -- hubert depesz lubaczewski http://www.depesz.pl/ Mój Boże, spraw abym milczał, dopóki się nie upewnię, że naprawdę mam coś do powiedzenia. (c) 1998 depesz msg07278/pgp0.pgp Description: PGP signature
Re: [SQL] convert sum (interval) to seconds
Oliver Elphick <[EMAIL PROTECTED]> writes: > On Tue, 2002-09-03 at 11:45, Doppelganger wrote: >> How can I convert sum(interval) to seconds? > Here's one way; > junk=# select extract (minute from sum(if)) * 60 + extract (second > from sum(if)) from i; EXTRACT(EPOCH FROM interval) is designed for this ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] tree structures in sql - my point of view (with request of comment from joe celko)
hi i recently spent some time on tree-structures in sql. i started with simple id/parent_id approach, used by nearly everyone, then i stopped at joe celko's nested sets, but i found it not very usable. then i found my own (maybe someone wrote it before, but i haven't read it, so idea is mine) way. in my way we have two tables: create table data (id serial, name text); create table structure (parent_id int8, child_id int8, depth int8); structure table represents all paths in tree. for example for this tree: sql / \ postgresqloracle-__ |/|\ linux scolinux windows / \ glibc1 glibc2 (sorry for used data - it is just template, and personally i don't like oracle). so, for this tree we would populate the tables this way: data: id | name + 1 | sql 2 | postgresql 3 | oracle 4 | linux 5 | sco 6 | linux 7 | windows 8 | glibc1 9 | glibc2 structure: parent_id | child_id | depth ---+--+--- 1 |1 | 0 2 |2 | 0 3 |3 | 0 4 |4 | 0 5 |5 | 0 6 |6 | 0 7 |7 | 0 8 |8 | 0 9 |9 | 0 1 |2 | 1 1 |3 | 1 1 |4 | 2 2 |4 | 1 1 |5 | 1 1 |6 | 1 1 |7 | 1 3 |5 | 2 3 |6 | 2 3 |7 | 2 1 |8 | 3 1 |9 | 3 3 |8 | 2 3 |9 | 2 6 |8 | 1 6 |9 | 1 (records with depth 0 are technologically not necessary, but they simplify and speedup some queries). with this data layout (easily indexable) you can fetch any data with just one select statement (no recursion needed in any case): - fetching parent - fetching childs - fetching "from id up" - fetching "from id down" also when you need to get indirect parents/childs or when you need only some of the data (from me, up, but not more then to my grand-grand-grand-father). i'd like to get some comments on this - how do you see my idea, is it worth it, do you know any better way to store trees in sql? best regards depesz -- hubert depesz lubaczewski http://www.depesz.pl/ Mój Boże, spraw abym milczał, dopóki się nie upewnię, że naprawdę mam coś do powiedzenia. (c) 1998 depesz msg07280/pgp0.pgp Description: PGP signature
Re: [SQL] [GENERAL] Why must the function that a trigger calls return
What would the trigger return the value too. You did not call the trigger
the system called the trigger. Also triggers are not meant for use in this
way they are meant for data manipulation before or after a table is
inserted,updated or deleted.
You would have to run select currval('seq_name') as the next query.
On Thu, 29 Aug 2002, Greg Patnude wrote:
> This seem rather limiting... Suppose I want a trigger that after insert,
> returns the currval(sequence) of the newly inserted row automatically
> without having to run another query ???
>
> GP
>
>
>
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>
--
Darren Ferguson
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Update Help
Hi, Let say I have 2 Product table, both of them has columns ProductID and Price What is the update command if I want to update all Prices of first table to be equal with Price in second table? Please Help. Yudie
Re: [SQL] tree structures in sql - my point of view (with request
While I don't have a time to comment your message I want to point to contrib/ltree package which is extremely fast :-) http://www.sai.msu.su/~megera/postgres/gist/ltree Oleg On Tue, 3 Sep 2002, Hubert depesz Lubaczewski wrote: > hi > i recently spent some time on tree-structures in sql. > i started with simple id/parent_id approach, used by nearly everyone, > then i stopped at joe celko's nested sets, but i found it not very > usable. > then i found my own (maybe someone wrote it before, but i haven't read > it, so idea is mine) way. > in my way we have two tables: > create table data (id serial, name text); > create table structure (parent_id int8, child_id int8, depth int8); > > structure table represents all paths in tree. > for example for this tree: > > sql >/ \ > postgresql oracle-__ > |/|\ > linux scolinux windows >/ \ > glibc1 glibc2 > > (sorry for used data - it is just template, and personally i don't like > oracle). > so, for this tree we would populate the tables this way: > data: > id | name > + > 1 | sql > 2 | postgresql > 3 | oracle > 4 | linux > 5 | sco > 6 | linux > 7 | windows > 8 | glibc1 > 9 | glibc2 > > structure: > parent_id | child_id | depth > ---+--+--- > 1 |1 | 0 > 2 |2 | 0 > 3 |3 | 0 > 4 |4 | 0 > 5 |5 | 0 > 6 |6 | 0 > 7 |7 | 0 > 8 |8 | 0 > 9 |9 | 0 > 1 |2 | 1 > 1 |3 | 1 > 1 |4 | 2 > 2 |4 | 1 > 1 |5 | 1 > 1 |6 | 1 > 1 |7 | 1 > 3 |5 | 2 > 3 |6 | 2 > 3 |7 | 2 > 1 |8 | 3 > 1 |9 | 3 > 3 |8 | 2 > 3 |9 | 2 > 6 |8 | 1 > 6 |9 | 1 > > (records with depth 0 are technologically not necessary, but they > simplify and speedup some queries). > > with this data layout (easily indexable) you can fetch any data with > just one select statement (no recursion needed in any case): > - fetching parent > - fetching childs > - fetching "from id up" > - fetching "from id down" > also when you need to get indirect parents/childs or when you need only > some of the data (from me, up, but not more then to my > grand-grand-grand-father). > > i'd like to get some comments on this - how do you see my idea, is it > worth it, do you know any better way to store trees in sql? > > best regards > > depesz > > Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(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] Update Help
On Tue, 2002-09-03 at 17:38, [EMAIL PROTECTED] wrote: > Let say I have 2 Product table, both of them has columns ProductID and Price > What is the update command if I want to update all Prices of first table to be equal with Price in second table? UPDATE table1 SET price = (SELECT price FROM table2 WHERE table1.product = table2.product); -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "And he said unto his disciples, Therefore I say unto you, Take no thought for your life, what ye shall eat; neither for the body, what ye shall put on. For life is more than meat, and the body is more than clothing. Consider the ravens, for they neither sow nor reap; they have neither storehouse nor barn; and yet God feeds them; how much better you are than the birds! Consider the lilies, how they grow; they toil not, they spin not; and yet I say unto you, that Solomon in all his glory was not arrayed like one of these. If then God so clothe the grass, which is to day in the field, and tomorrow is cast into the oven; how much more will he clothe you, O ye of little faith? And seek not what ye shall eat, or what ye shall drink, neither be ye of doubtful mind. But rather seek ye the kingdom of God; and all these things shall be added unto you." Luke 12:22-24; 27-29; 31. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Outer Join with For Update
Hi all, I work with a case tool named Genexus. This tool generate the applications in VB, VFox, Java, C, etc. in DB2, Oracle, M$ SQL and now supports postgresql in VB and JAVA generator, making a good job (excepting when it use a "optimize for", "/*fastfirstrows*/" or "fast(n)" for the other 3 DB ... postgresql does not have this optimization clauses, making certains querys slow when compared with others). But I found a problem (this query is the same for DB2 and postgres and the database tables/data are equal): SELECT A.id, B.desc from A left join B ON B.id=A.id FOR UPDATE OF A,B If I have a null rellation in B, the error in postgres is: "SELECT FOR UPDATE cannot be applied to the nullable side of an OUTER JOIN" DB2 simply ignore the FOR UPDATE ... I think that is the correct form...I don´t know when the LEFT ou RIGHT JOIN will be null... I remove the check in initsplan.c (line 324/325), but postgres returns : "ERROR: ExecutePlan: (junk) `ctid2' is NULL!" Like other case tools, I don´t have the possibility to change the query in code forever... Any tips?!?! Thank´s all and sorry for this big email and a poor english... :) Alexandre Arruda Paes Aldeia Digital Brazil ---(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] tree structures in sql - my point of view (with request of comment from joe celko)
Hubert, > i recently spent some time on tree-structures in sql. > i started with simple id/parent_id approach, used by nearly everyone, > then i stopped at joe celko's nested sets, but i found it not very > usable. I'll be posting an article on implementing nested set trees "real soon now". My experieince: Adjacency list trees are easier to understand conceptually, there are more tools on freshmeat.net for them, and they are the most efficient form of tree for graphical display. Nested Set trees are hard to wrap your mind around, lack a lot in the way of code samples on freshmeat, are harder to build GUI tools for, but are much, much faster for determining branch membership and branch parenthood. So which model you use depends on what you intend to do with the tree. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] UPDATE & LIMIT together?
tp wrote: > Hmm,, > > thanks so far, it helped. > > The query is horrible slow on full tables (>100.000 rows). > Is there any other solution? I just want to have the 'next 10 entries' > WHERE state=10 and update state=20. > (so that on the next request i or another process only gets the > new entires in queue). > > > My query now looks like: > UPDATE queue SET state=20 WHERE (id) IN (SELECT id FROM queue LIMIT 10) Without an ORDER BY, I am not sure what that LIMIT is returning. I don't know of a faster way. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Update Help
[EMAIL PROTECTED] writes: > Let say I have 2 Product table, both of them has columns ProductID and > Price What is the update command if I want to update all Prices of first > table to be equal with Price in second table? Possibly you mean something like this: UPDATE first_table SET price = (SELECT price FROM second_table WHERE second_table.productid = first_table.productid); Possibly the answer is also to redesign your schema to avoid redundant data. -- Peter Eisentraut [EMAIL PROTECTED] ---(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] Outer Join with For Update
"alexandre :: aldeia digital" <[EMAIL PROTECTED]> writes: > SELECT A.id, B.desc from A left join B ON B.id=A.id FOR UPDATE OF A,B > If I have a null rellation in B, the error in postgres is: > "SELECT FOR UPDATE cannot be applied to the nullable side of an OUTER JOIN" Yup. > DB2 simply ignore the FOR UPDATE ... > I think that is the correct form... That seems obviously wrong to me. What good is a FOR UPDATE if it fails to lock down the rows that created your result? regards, tom lane ---(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] tree structures in sql - my point of view (with request
Yep! ltree is Fst , and i use it at the moment. But will it work in INGRES,DB2 or ORACLE ? what if tommorow my boss ask me to use ORACLE? I have similar issues in using intarray & arrays in PGSQL though reasons of shifting to others dbs are diminishing with every major release of PG ;-) regds mallah. > While I don't have a time to comment your message I want to point to contrib/ltree >package > which is extremely fast :-) > > http://www.sai.msu.su/~megera/postgres/gist/ltree > > Oleg > On Tue, 3 Sep 2002, Hubert depesz Lubaczewski wrote: > >> hi >> i recently spent some time on tree-structures in sql. >> i started with simple id/parent_id approach, used by nearly everyone, then i >stopped at joe >> celko's nested sets, but i found it not very usable. >> then i found my own (maybe someone wrote it before, but i haven't read it, so idea >is mine) >> way. >> in my way we have two tables: >> create table data (id serial, name text); >> create table structure (parent_id int8, child_id int8, depth int8); >> >> structure table represents all paths in tree. >> for example for this tree: >> >>sql >> / \ >> postgresql oracle-__ >> |/|\ >> linux scolinux windows >> / \ >>glibc1 glibc2 >> >> (sorry for used data - it is just template, and personally i don't like oracle). >> so, for this tree we would populate the tables this way: >> data: >> id | name >> + >> 1 | sql >> 2 | postgresql >> 3 | oracle >> 4 | linux >> 5 | sco >> 6 | linux >> 7 | windows >> 8 | glibc1 >> 9 | glibc2 >> >> structure: >> parent_id | child_id | depth >> ---+--+--- >> 1 |1 | 0 >> 2 |2 | 0 >> 3 |3 | 0 >> 4 |4 | 0 >> 5 |5 | 0 >> 6 |6 | 0 >> 7 |7 | 0 >> 8 |8 | 0 >> 9 |9 | 0 >> 1 |2 | 1 >> 1 |3 | 1 >> 1 |4 | 2 >> 2 |4 | 1 >> 1 |5 | 1 >> 1 |6 | 1 >> 1 |7 | 1 >> 3 |5 | 2 >> 3 |6 | 2 >> 3 |7 | 2 >> 1 |8 | 3 >> 1 |9 | 3 >> 3 |8 | 2 >> 3 |9 | 2 >> 6 |8 | 1 >> 6 |9 | 1 >> >> (records with depth 0 are technologically not necessary, but they simplify and >speedup some >> queries). >> >> with this data layout (easily indexable) you can fetch any data with just one >select statement >> (no recursion needed in any case): >> - fetching parent >> - fetching childs >> - fetching "from id up" >> - fetching "from id down" >> also when you need to get indirect parents/childs or when you need only some of the >data (from >> me, up, but not more then to my >> grand-grand-grand-father). >> >> i'd like to get some comments on this - how do you see my idea, is it worth it, do >you know >> any better way to store trees in sql? >> >> best regards >> >> depesz >> >> > > Regards, > Oleg > _ > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > Sternberg Astronomical Institute, Moscow University (Russia) > Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ > phone: +007(095)939-16-83, +007(095)939-23-83 > > > ---(end of broadcast)--- TIP 2: you >can get off > all lists at once with the unregister command >(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] tree structures in sql - my point of view (with request
[EMAIL PROTECTED] wrote: > > Yep! ltree is Fst , and i use it at the moment. > > But will it work in INGRES,DB2 or ORACLE ? > what if tommorow my boss ask me to use ORACLE? > > I have similar issues in using intarray & arrays in PGSQL > > though reasons of shifting to others dbs are diminishing > with every major release of PG ;-) No it will only work on PostgreSQL. If those other apps had loadable types, you might be able to do it by rewriting the code to match their API. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Update help
Hi, Let say I have 2 Product table, both of them has columns ProductID and Price What is the update command if I want to update all Prices of first table to be equal with Price in second table? Please Help. Yudie
