[SQL] Need help with complex query
Suppose I have a relation called sales with the following schema: sales-Schema = (cust, prod, mn, dy, yr, quant, st) An instance of the relation would look something like this: custprodmn dy yr quant st == == == = == Knuth Milk02 29 200012 CA Jones Fruits 03 31 200145 WY Knuth Jam 12 21 200241 MN Kruskal Jelly 11 30 199912 NJ Hamming Milk03 15 199847 GA Knuth Milk02 29 200012 CA Kruskal Jelly 11 30 19995 NJ Knuth Milk06 23 200212 CA Knuth Bread 02 21 194913 CA Note: The relation can have duplicate rows. Now, I want this query: For each customer-product combination, find the minimum quantity sold along with its respective date. If there are more than one minimum sales quantity for a customer product combination, print only one of them. So the query should return the following: custprodmn dy yr quant == == == = Jones Fruits 03 31 200145 Knuth Jam 12 21 200241 Hamming Milk03 15 199847 Kruskal Jelly 11 30 19995 Knuth Milk06 23 200212 Knuth Bread 02 21 194913 I wrote the following query: select cust, prod, mn, dy, yr, quant from (select cust, prod, min(quant) from sales group by cust, prod) as x(c, p, q), sales where cust = x.c and prod = x.p and quant = x.q; And I got the following relation: custprodmn dy yr quant == == == = Knuth Milk02 29 200012 Jones Fruits 03 31 200145 Knuth Jam 12 21 200241 Hamming Milk03 15 199847 Knuth Milk02 29 200012 Kruskal Jelly 11 30 19995 Knuth Milk06 23 200212 Knuth Bread 02 21 194913 which is not what I want because the Knuth-Bread combination is repeated; I only want one of them. I have tried many other variations of the query, but the best I've done is something like this (by selection distinct quantities out of the above table): custprodmn dy yr quant st == == == = == Jones Fruits 03 31 200145 WY Knuth Jam 12 21 200241 MN Hamming Milk03 15 199847 GA Knuth Milk02 29 200012 CA Kruskal Jelly 11 30 19995 NJ Knuth Milk06 23 200212 CA Knuth Bread 02 21 194913 CA Can anyone help me out? Thanks in advance. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Datatype conversion help
Suppose I have an integer between 0 and 99 and I want to covert it to string, and pad leading zeros if neccessary. For example, 1 => 01 10 => 10 I've tried to_char(in_val, '99'), and that returns a string that is two charecters, but there isn't a leading zero incase I have the number 2 as input. Any ideas? Thanks. Yasir ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Datatype conversion help
Thank you so much! But my problem is that when I do to_char(mn, '00') || '-' || to_char(dy, '00') || '-' || to_char(yr, '') where mn, dy, and yr are ints, is that the output has a space after the the dash. For example, I get 07- 25- 1994 instead of what I want: 07-25-1994 Thanks, Yasir On Tue, 8 Jul 2003, Richard Rowell wrote: > Date: 08 Jul 2003 15:21:33 -0500 > From: Richard Rowell <[EMAIL PROTECTED]> > To: Yasir Malik <[EMAIL PROTECTED]> > Subject: Re: [SQL] Datatype conversion help > > On Tue, 2003-07-08 at 15:07, Yasir Malik wrote: > > I've tried to_char(in_val, '99'), and that returns a string that is two > > select to_char(9,'00'); > ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Datatype conversion help
Yes, Mr. Nachbaur helped me out. Thanks. I don't think I can do to_char(, 'MM-DD-) because the date fields are originally stored as separate integers in my schema (they have to be that way). I still can't understand why the extra space was added after the dash. It just made my life more miserable. Yasir On Tue, 8 Jul 2003, David Olbersen wrote: > Date: Tue, 8 Jul 2003 14:02:55 -0700 > From: David Olbersen <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Subject: Re: [SQL] Datatype conversion help > > Yasir, > > If this is a date you're playing with, simply use: > > to_char( , 'MM-DD-' ) > > to get what you want. > > -- > David Olbersen > iGuard Engineer > St. Bernard Software > 11415 West Bernardo Court > San Diego, CA 92127 > 1-858-676-2277 x2152 > > > > -Original Message- > > From: Yasir Malik [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, July 08, 2003 1:29 PM > > To: [EMAIL PROTECTED] > > Subject: Re: [SQL] Datatype conversion help > > > > > > Thank you so much! But my problem is that when I do > > to_char(mn, '00') || '-' || to_char(dy, '00') || '-' || to_char(yr, > > '') > > > > where mn, dy, and yr are ints, is that the output has a space > > after the > > the dash. For example, I get > > 07- 25- 1994 > > > > instead of what I want: > > 07-25-1994 > > > > Thanks, > > Yasir > > > > On Tue, 8 Jul 2003, Richard Rowell wrote: > > > > > Date: 08 Jul 2003 15:21:33 -0500 > > > From: Richard Rowell <[EMAIL PROTECTED]> > > > To: Yasir Malik <[EMAIL PROTECTED]> > > > Subject: Re: [SQL] Datatype conversion help > > > > > > On Tue, 2003-07-08 at 15:07, Yasir Malik wrote: > > > > I've tried to_char(in_val, '99'), and that returns a > > string that is two > > > > > > select to_char(9,'00'); > > > > > > > ---(end of > > broadcast)--- > > TIP 8: explain analyze is your friend > > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Need help with complex query
Mr. Haller That was what I was exactly looking for. The guys at experts-exchange.com or any other website could come up with that answer. I figured it out without using DISTINCT ON, but the solution was horrendous. I have already turned in my assignment, however. Thank you so much for your help. I hope to continue to learn from professionals like you. Thank you so much, Yasir On Wed, 9 Jul 2003, Christoph Haller wrote: > Date: Wed, 09 Jul 2003 16:46:43 +0200 > From: Christoph Haller <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Subject: Re: [SQL] Need help with complex query > > Yasir, > You were quite close already. > That's exactly what DISTINCT ON was invented for (non-standard > compliant) > select DISTINCT ON (cust, prod) cust, prod, mn, dy, yr, quant > from (select cust, prod, min(quant) > from sales > group by cust, prod) as x(c, p, q), sales > where cust = x.c and prod = x.p and quant = x.q order by cust, prod; > cust | prod | mn | dy | yr | quant > -++++--+--- > Hamming | Milk | 3 | 15 | 1998 |47 > Jones | Fruits | 3 | 31 | 2001 |45 > Knuth | Bread | 2 | 21 | 1949 |13 > Knuth | Jam| 12 | 21 | 2002 |41 > Knuth | Milk | 2 | 29 | 2000 |12 > Kruskal | Jelly | 11 | 30 | 1999 | 5 > (6 rows) > Regards, Christoph > > > > > Suppose I have a relation called sales with the following schema: > > sales-Schema = (cust, prod, mn, dy, yr, quant, st) > > > > An instance of the relation would look something like this: > > cust prodmn dy yr quant st > > == == == = == > > Knuth Milk02 29 200012 CA > > Jones Fruits 03 31 200145 WY > > Knuth Jam 12 21 200241 MN > > Kruskal Jelly 11 30 199912 NJ > > Hamming Milk03 15 199847 GA > > Knuth Milk02 29 200012 CA > > Kruskal Jelly 11 30 19995 NJ > > Knuth Milk06 23 200212 CA > > Knuth Bread 02 21 194913 CA > > > > Note: The relation can have duplicate rows. > > > > Now, I want this query: For each customer-product combination, find > the > > minimum quantity sold along with its respective date. If there are > more > > than one minimum sales quantity for a customer product combination, > print > > only one of them. So the query should return the following: > > > > cust prodmn dy yr quant > > == == == = > > Jones Fruits 03 31 200145 > > Knuth Jam 12 21 200241 > > Hamming Milk03 15 199847 > > Kruskal Jelly 11 30 19995 > > Knuth Milk06 23 200212 > > Knuth Bread 02 21 194913 > > > > I wrote the following query: > > select cust, prod, mn, dy, yr, quant > > from (select cust, prod, min(quant) > > from sales > > group by cust, prod) as x(c, p, q), sales > > where cust = x.c and prod = x.p and quant = x.q; > > > > And I got the following relation: > > cust prodmn dy yr quant > > == == == = > > Knuth Milk02 29 200012 > > Jones Fruits 03 31 200145 > > Knuth Jam 12 21 200241 > > Hamming Milk03 15 199847 > > Knuth Milk02 29 200012 > > Kruskal Jelly 11 30 19995 > > Knuth Milk06 23 200212 > > Knuth Bread 02 21 194913 > > > > which is not what I want because the Knuth-Bread combination is > repeated; > > I only want one of them. I have tried many other variations of the > query, > > but the best I've done is something like this (by selection distinct > > quantities out of the above table): > > cust prodmn dy yr quant st > > == == == = == > > Jones Fruits 03 31 200145 WY > > Knuth Jam 12 21 200241 MN > > Hamming Milk03 15 199847 GA > > Knuth Milk02 29 200012 CA > > Kruskal Jelly 11 30 19995 NJ > > Knuth Milk06 23 200212 CA > > Knuth Bread 02 21 194913 CA > > > > Can anyone help me out? Thanks in advance. > > > > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Datatype conversion help
I used trim and here's what I came up with: to_date(trim(to_char(yr, '') || trim(to_char(mn, '00')) || trim(to_char(dy, '00'))), 'MMDD') Apparently to_char adds a space to the charecter you are casting. Yasir On Wed, 9 Jul 2003, Dmitry Tkach wrote: > Date: Wed, 09 Jul 2003 18:40:37 -0400 > From: Dmitry Tkach <[EMAIL PROTECTED]> > To: Yasir Malik <[EMAIL PROTECTED]> > Cc: [EMAIL PROTECTED] > Subject: Re: [SQL] Datatype conversion help > > What about lpad? > > select lpad (7, 2, 0) || '-' || lpad (9, 2, '0') || '-2003'; > ?column? > > 07-09-2003 > (1 row) > > > I hope, it helps... > > Dima > > Yasir Malik wrote: > > >Thank you so much! But my problem is that when I do > >to_char(mn, '00') || '-' || to_char(dy, '00') || '-' || to_char(yr, > >'') > > > >where mn, dy, and yr are ints, is that the output has a space after the > >the dash. For example, I get > >07- 25- 1994 > > > >instead of what I want: > >07-25-1994 > > > >Thanks, > >Yasir > > > >On Tue, 8 Jul 2003, Richard Rowell wrote: > > > > > > > >>Date: 08 Jul 2003 15:21:33 -0500 > >>From: Richard Rowell <[EMAIL PROTECTED]> > >>To: Yasir Malik <[EMAIL PROTECTED]> > >>Subject: Re: [SQL] Datatype conversion help > >> > >>On Tue, 2003-07-08 at 15:07, Yasir Malik wrote: > >> > >> > >>>I've tried to_char(in_val, '99'), and that returns a string that is two > >>> > >>> > >>select to_char(9,'00'); > >> > >> > >> > > > >---(end of broadcast)--- > >TIP 8: explain analyze is your friend > > > > > > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Datatype conversion help
I will surely use your suggestion in my future programs. Thanks, Yasir On Wed, 9 Jul 2003, Dmitry Tkach wrote: > Date: Wed, 09 Jul 2003 18:51:48 -0400 > From: Dmitry Tkach <[EMAIL PROTECTED]> > To: Yasir Malik <[EMAIL PROTECTED]> > Cc: [EMAIL PROTECTED] > Subject: Re: [SQL] Datatype conversion help > > Yasir Malik wrote: > > >I used trim and here's what I came up with: > >to_date(trim(to_char(yr, '') || trim(to_char(mn, '00')) || > >trim(to_char(dy, '00'))), 'MMDD') > > > >Apparently to_char adds a space to the charecter you are casting. > > > > > I know :-) > And lpad doesn't - that's why I suggested it :-) > > Dima > > > > >On Wed, 9 Jul 2003, Dmitry Tkach wrote: > > > > > > > >>Date: Wed, 09 Jul 2003 18:40:37 -0400 > >>From: Dmitry Tkach <[EMAIL PROTECTED]> > >>To: Yasir Malik <[EMAIL PROTECTED]> > >>Cc: [EMAIL PROTECTED] > >>Subject: Re: [SQL] Datatype conversion help > >> > >>What about lpad? > >> > >>select lpad (7, 2, 0) || '-' || lpad (9, 2, '0') || '-2003'; > >> ?column? > >> > >> 07-09-2003 > >>(1 row) > >> > >> > >>I hope, it helps... > >> > >>Dima > >> > >>Yasir Malik wrote: > >> > >> > >> > >>>Thank you so much! But my problem is that when I do > >>>to_char(mn, '00') || '-' || to_char(dy, '00') || '-' || to_char(yr, > >>>'') > >>> > >>>where mn, dy, and yr are ints, is that the output has a space after the > >>>the dash. For example, I get > >>>07- 25- 1994 > >>> > >>>instead of what I want: > >>>07-25-1994 > >>> > >>>Thanks, > >>>Yasir > >>> > >>>On Tue, 8 Jul 2003, Richard Rowell wrote: > >>> > >>> > >>> > >>> > >>> > >>>>Date: 08 Jul 2003 15:21:33 -0500 > >>>>From: Richard Rowell <[EMAIL PROTECTED]> > >>>>To: Yasir Malik <[EMAIL PROTECTED]> > >>>>Subject: Re: [SQL] Datatype conversion help > >>>> > >>>>On Tue, 2003-07-08 at 15:07, Yasir Malik wrote: > >>>> > >>>> > >>>> > >>>> > >>>>>I've tried to_char(in_val, '99'), and that returns a string that is two > >>>>> > >>>>> > >>>>> > >>>>> > >>>>select to_char(9,'00'); > >>>> > >>>> > >>>> > >>>> > >>>> > >>>---(end of broadcast)--- > >>>TIP 8: explain analyze is your friend > >>> > >>> > >>> > >>> > >> > >> > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] virus warning
Thank god that I use Pine. Yasir On Fri, 19 Sep 2003, Paul Thomas wrote: > Date: Fri, 19 Sep 2003 23:14:54 +0100 > From: Paul Thomas <[EMAIL PROTECTED]> > To: "pgsql-sql @ postgresql . org" <[EMAIL PROTECTED]> > Subject: Re: [SQL] virus warning > > On 19/09/2003 16:37 scott.marlowe wrote: > > > > Keep in mind, if you check the headers on the emails you'll see that they > > > > are forged. I've been getting about 20 emails a day telling me a message > > > > I know I didn't send was infected with a virus. > > > > I got 432 last night inbound, some with names forged from this list, > > others from names unknown. > > > > But I don't think it's not the folks on this list, I think it's a > > windows worm that looks in people's email, harvests names at random, and > > forged email based on it. > > Does seem to be. Just download another 200+. Mildly anoying for me with a > DSL line. I really feel for those on dial-up :( > > -- > Paul Thomas > +--+-+ > | Thomas Micro Systems Limited | Software Solutions for the Smaller > Business | > | Computer Consultants | > http://www.thomas-micro-systems-ltd.co.uk | > +--+-+ > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] DateDiff in PostgreSQL
Hi, You can use the age() function to find the difference between dates, and use the extract() function to get the years, months, days, etc. Yasir On Fri, 7 Nov 2003, George A.J wrote: > Date: Fri, 7 Nov 2003 05:34:09 -0800 (PST) > From: George A.J <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Subject: [SQL] DateDiff in PostgreSQL > > > Hi, > > i am converting a MSSQL Server database to PostgreSQL. > > Using PostgreSQL version 7.3.2. > > Is there any function like the DateDiff() in MSSQL Server. > > ie, a function that returns difference of two dates(timestamp) in days or months or > year.. > > The - operator for timestamp retuns the intervel in days only. > > Please help > > jinujose > > > - > Do you Yahoo!? > Protect your identity with Yahoo! Mail AddressGuard ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] DateDiff in PostgreSQL
Hi, You can use the age() function to find the difference between dates, and use the extract() function to get the years, months, days, etc. Yasir On Fri, 7 Nov 2003, George A.J wrote: > Date: Fri, 7 Nov 2003 05:34:09 -0800 (PST) > From: George A.J <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Subject: [SQL] DateDiff in PostgreSQL > > > Hi, > > i am converting a MSSQL Server database to PostgreSQL. > > Using PostgreSQL version 7.3.2. > > Is there any function like the DateDiff() in MSSQL Server. > > ie, a function that returns difference of two dates(timestamp) in days or months or > year.. > > The - operator for timestamp retuns the intervel in days only. > > Please help > > jinujose > > > - > Do you Yahoo!? > Protect your identity with Yahoo! Mail AddressGuard ---(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] help me...
Hi, I'm usually wrong, but you can create a view for Query1 and Query2. That is do create view Query1 [your query for query1]; create view Query2 [your query for query2]; Then what you are doing should work. Are views offered in Access? Regards, Yasir On Sat, 8 Nov 2003, [iso-8859-1] ron_tabada wrote: > Date: Sat, 8 Nov 2003 06:02:15 + (GMT) > From: "[iso-8859-1] ron_tabada" <[EMAIL PROTECTED]> > Reply-To: [EMAIL PROTECTED] > To: [EMAIL PROTECTED] > Subject: [SQL] help me... > > Hello everyone, Good day! Could anyone help me translate this query in Microsoft Access to Postgresql. I'm having a difficulty. Pls... > Query1: > SELECT items.description, Sum(supplieditems.qty) AS SumOfqty > FROM items INNER JOIN supplieditems ON items.itemno = supplieditems.itemno > GROUP BY items.description; > Query2: > SELECT [items].[description], Sum([customer].[qty]) AS SumOfqty > FROM (items INNER JOIN OtherItem ON [items].[itemno]=[OtherItem].[Itemno]) INNER JOIN customer ON [OtherItem].[Itemno]=[customer].[itemcode] > GROUP BY [items].[description]; > Query3: > SELECT [Query1].[SumOfqty], [Query2].[SumOfqty], [Query1]![SumOfqty]-[Query2]![SumOfqty] AS remain > FROM Query1, Query2; > I have translated Query1 and Query2 in POSTGRESQL but I don't know how to implement Query3. > > > - > Want to chat instantly with your online friends?Get the FREE Yahoo!Messenger ---(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] help me...
That's what I said! :) Yasir On Sun, 9 Nov 2003, Christopher Browne wrote: > Date: Sun, 09 Nov 2003 21:59:14 -0500 > From: Christopher Browne <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Subject: Re: [SQL] help me... > > The world rejoiced as [EMAIL PROTECTED] (ron_tabada) wrote: > > Hello everyone, Good day! Could anyone help me translate this query > > in Microsoft Access to Postgresql. I'm having a difficulty. Pls... > > > > Query1: > > SELECT items.description, Sum(supplieditems.qty) AS SumOfqty > > FROM items INNER JOIN supplieditems ON items.itemno = supplieditems.itemno > > GROUP BY items.description; > > > > Query2: > > SELECT [items].[description], Sum([customer].[qty]) AS SumOfqty > > FROM (items INNER JOIN OtherItem ON [items].[itemno]=[OtherItem].[Itemno]) INNER > > JOIN customer ON > > [OtherItem].[Itemno]=[customer].[itemcode] > > GROUP BY [items].[description]; > > > > Query3: > > SELECT [Query1].[SumOfqty], [Query2].[SumOfqty], > > [Query1]![SumOfqty]-[Query2]![SumOfqty] AS remain > > FROM Query1, Query2; > > > > I have translated Query1 and Query2 in POSTGRESQL but I don't know > > how to implement Query3. > > Apparently you have discovered the nearest equivalent to "VIEWs" in > Access. > > I can suggest two ways: > > 1. Define "query1" and "query2" as PostgreSQL views, as with... > > create view query1 as > SELECT items.description, Sum(supplieditems.qty) AS SumOfqty > FROM items INNER JOIN supplieditems ON items.itemno = supplieditems.itemno > GROUP BY items.description; > > create view query2 as [omitted details]. > > Query 3 should work perfectly well when it has the two views to work > with. > > 2. Subselects... > > SELECT Query1.SumOfqty, Query2.SumOfqty, Query1.SumOfqty-Query2.SumOfqty AS remain > FROM > (select stuff for query 1) as query1, > (select stuff for query 2) as query2; > > Approach #1. seems more appropriate, as it uses the views to keep the > queries all simple. > -- > let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];; > http://www.ntlug.org/~cbbrowne/nonrdbms.html > Incrementally extended heuristic algorithms tend inexorably toward the > incomprehensible. > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] help me...
Hi, I'm usually wrong, but you can create a view for Query1 and Query2. That is do create view Query1 [your query for query1]; create view Query2 [your query for query2]; Then what you are doing should work. Regards, Yasir On Sat, 8 Nov 2003, [iso-8859-1] ron_tabada wrote: > Date: Sat, 8 Nov 2003 06:02:15 + (GMT) > From: "[iso-8859-1] ron_tabada" <[EMAIL PROTECTED]> > Reply-To: [EMAIL PROTECTED] > To: [EMAIL PROTECTED] > Subject: [SQL] help me... > > Hello everyone, Good day! Could anyone help me translate this query in Microsoft > Access to Postgresql. I'm having a difficulty. Pls... > Query1: > SELECT items.description, Sum(supplieditems.qty) AS SumOfqty > FROM items INNER JOIN supplieditems ON items.itemno = supplieditems.itemno > GROUP BY items.description; > Query2: > SELECT [items].[description], Sum([customer].[qty]) AS SumOfqty > FROM (items INNER JOIN OtherItem ON [items].[itemno]=[OtherItem].[Itemno]) INNER > JOIN customer ON [OtherItem].[Itemno]=[customer].[itemcode] > GROUP BY [items].[description]; > Query3: > SELECT [Query1].[SumOfqty], [Query2].[SumOfqty], > [Query1]![SumOfqty]-[Query2]![SumOfqty] AS remain > FROM Query1, Query2; > I have translated Query1 and Query2 in POSTGRESQL but I don't know how to implement > Query3. > > > - > Want to chat instantly with your online friends? Get the FREE Yahoo!Messenger ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Bit strings
Hello, For a data warehousing project I will have to implement bitmaps. I would perfer to stay away from C, Java, etc., and would like to use PostgreSQL instead. I have a question about bit string type: does the time it takes to do the & or | of two bit strings run in constant time (as it does in C)? Thanks, Yasir ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Getting last insert value
Hello, I have a table with many fields, and one of the fields is of type serial. I will do many inserts, and there will be many repeats of the other fields in the table, but, obviously, the serial field will be unique for all the rows. How can I get the value of serial field of the last row I inserted. I know I can select the maximum value from the serial field, but is there a better way, preferably something that takes constant time. Thanks, Yasir Malik ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Getting last insert value
Thank you all for your help.
Yasir Malik
On Sat, 15 Nov 2003, Greg Stark wrote:
> Date: 15 Nov 2003 12:50:28 -0500
> From: Greg Stark <[EMAIL PROTECTED]>
> To: Guillaume LELARGE <[EMAIL PROTECTED]>
> Cc: Yasir Malik <[EMAIL PROTECTED]>, [EMAIL PROTECTED]
> Subject: Re: [SQL] Getting last insert value
>
>
> Guillaume LELARGE <[EMAIL PROTECTED]> writes:
>
> > Doing a "select currval() from my_table" after your insert should work.
>
> That's "select currval('my_table_pkcol_seq')" actually.
>
> The above would have called the currval() function for every record of the
> table which isn't what you want and in any case currval takes an argument.
>
> --
> greg
>
>
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
>
---(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] Addition and subtraction on BIT type
Hello, Is there a way to do addition and subtraction on BIT types? For example, for creat table test (a BIT(3)); insert into test values (B'101'); select a + 1 from test; fails and select a::smallint + 1 from test; also fails. In addition, is there a way to change the bit of a bit string? For example change a 1 to a 0 or vice versa. Any suggestions? Thanks, Yasir ---(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] Addition and subtraction on BIT type
Hello, I think I am almost at a solution to my last question. I can do select int4(a) from test; to convert to an integer. So now addition and subtraction can be done between bit types. But how do I convert back to BIT type? If I do select bit(int4(b'1001')); I get the following message: ERROR: parser: parse error at or near "int4" at character 12 Can anyone tell me why the bit function is not working? It's under the pg_catalog schema. Thanks, Yasir On Sun, 16 Nov 2003, Yasir Malik wrote: > Date: Sun, 16 Nov 2003 11:18:03 -0500 > From: Yasir Malik <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Subject: Addition and subtraction on BIT type > > Hello, > Is there a way to do addition and subtraction on BIT types? For example, > for > creat table test (a BIT(3)); > insert into test values (B'101'); > > select a + 1 from test; fails > > and select a::smallint + 1 from test; also fails. > > In addition, is there a way to change the bit of a bit string? For > example change a 1 to a 0 or vice versa. > > Any suggestions? > Thanks, > Yasir > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Addition and subtraction on BIT type
Thank you for your reply. select int4(b'1001')::bit(32); gives the same result as what you gave. select int4(b'1001')::bit(4); gives the upper four bits, which are all zeroes. How would I get the lower four bits? I building bitmaps using plpgsql, and therefore, I will be doing a lot bit manipulation. Thanks, Yasir On Sun, 16 Nov 2003, Stephan Szabo wrote: > Date: Sun, 16 Nov 2003 21:40:45 -0800 (PST) > From: Stephan Szabo <[EMAIL PROTECTED]> > To: Yasir Malik <[EMAIL PROTECTED]> > Cc: [EMAIL PROTECTED] > Subject: Re: [SQL] Addition and subtraction on BIT type > > On Sun, 16 Nov 2003, Yasir Malik wrote: > > > I think I am almost at a solution to my last question. I can do > > select int4(a) from test; > > to convert to an integer. So now addition and > > subtraction can be done between bit types. But how do I convert back to > > BIT type? If I do > > select bit(int4(b'1001')); > > > > I get the following message: > > ERROR: parser: parse error at or near "int4" at character 12 > > > > Can anyone tell me why the bit function is not working? It's under the > > pg_catalog schema. > > It's also the name of a type that takes a precision in parentheses, so > you'd have to say "bit"(...) with the quotes. As a note, I think > that's going to effectively return you a bit(32), so > > sszabo=# select "bit"(int4(b'1001')); >bit > -- > 1001 > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [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] Addition and subtraction on BIT type
Thank you for your reply. select int4(b'1001')::bit(32); gives the same result as what you gave. select int4(b'1001')::bit(4); gives the upper four bits, which are all zeroes. How would I get the lower four bits? I building bitmaps using plpgsql, and therefore, I will be doing a lot bit manipulation. Thanks, Yasir On Sun, 16 Nov 2003, Stephan Szabo wrote: > Date: Sun, 16 Nov 2003 21:40:45 -0800 (PST) > From: Stephan Szabo <[EMAIL PROTECTED]> > To: Yasir Malik <[EMAIL PROTECTED]> > Cc: [EMAIL PROTECTED] > Subject: Re: [SQL] Addition and subtraction on BIT type > > On Sun, 16 Nov 2003, Yasir Malik wrote: > > > I think I am almost at a solution to my last question. I can do > > select int4(a) from test; > > to convert to an integer. So now addition and > > subtraction can be done between bit types. But how do I convert back to > > BIT type? If I do > > select bit(int4(b'1001')); > > > > I get the following message: > > ERROR: parser: parse error at or near "int4" at character 12 > > > > Can anyone tell me why the bit function is not working? It's under the > > pg_catalog schema. > > It's also the name of a type that takes a precision in parentheses, so > you'd have to say "bit"(...) with the quotes. As a note, I think > that's going to effectively return you a bit(32), so > > sszabo=# select "bit"(int4(b'1001')); >bit > -- > 1001 > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Arrays - a good idea?
Hello, I don't think there's any reason you should use arrays. You can do everything using tables, and it probably would be easier to use tables instead. Extracting information from arrays is also more difficult if you're using something like JDBC or the connectivity available in PHP. I don't know the criteria of when arrays are necessary, but I cannot think of an example where arrays are absolutely necessary Regards, Yasir On Tue, 18 Nov 2003, Paul Ganainm wrote: > Date: Tue, 18 Nov 2003 22:05:00 - > From: Paul Ganainm <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Subject: [SQL] Arrays - a good idea? > > > > Hi all, > > > Even though PostgreSQL supports arrays, is it a good idea to use them? I > mean, they > > a) don't conform to the relational model > > and > > b) are not transportable > > > so if one is designing an app, should one use them? > > When should they not/never be used? What are the criteria for justifying > their use? > > > Paul... > > > -- > > plinehan__AT__yahoo__DOT__com > > C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro > > Please do not top-post. > > > ---(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 > ---(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] Arrays - a good idea?
Hello, I don't think there's any reason you should use arrays. You can do everything using tables, and it probably would be easier to use tables instead. Extracting information from arrays is also more difficult if you're using something like JDBC or the connectivity available in PHP. I don't know the criteria of when arrays are necessary, but I cannot think of an example where arrays are absolutely necessary Regards, Yasir On Tue, 18 Nov 2003, Paul Ganainm wrote: > Date: Tue, 18 Nov 2003 22:05:00 - > From: Paul Ganainm <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Subject: [SQL] Arrays - a good idea? > > > > Hi all, > > > Even though PostgreSQL supports arrays, is it a good idea to use them? I > mean, they > > a) don't conform to the relational model > > and > > b) are not transportable > > > so if one is designing an app, should one use them? > > When should they not/never be used? What are the criteria for justifying > their use? > > > Paul... > > > -- > > plinehan__AT__yahoo__DOT__com > > C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro > > Please do not top-post. > > > ---(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 > ---(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] Addition and subtraction on BIT type
Hello, Is there a way to do addition and subtraction on BIT types? For example, for creat table test (a BIT(3)); insert into test values (B'101'); select a + 1 from test; fails and select a::smallint + 1 from test; also fails. In addition, is there a way to change the bit of a bit string? For example change a 1 to a 0 or vice versa. Any suggestions? Thanks, Yasir ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Getting last insert value
Guillaume,
Thank you very much for your response.
Yasir
On Sat, 15 Nov 2003, Guillaume LELARGE wrote:
> Date: Sat, 15 Nov 2003 17:41:41 +
> From: Guillaume LELARGE <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED]
> Subject: Re: [SQL] Getting last insert value
>
> Le Samedi 15 Novembre 2003 16:40, vous avez écrit :
> > Doing a "select currval() from my_table" after your insert should work.
> >
> Actually, this is
> select currval('my_sequence')
> Sorry about this.
>
> > For more details, see
> > http://www.postgresql.org/docs/7.3/interactive/functions-sequence.html
>
>
> --
> Guillaume
> .
>
>
> ---(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
>
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Anti log in PostgreSQL
The antilog of x is 10^x, so all you need to do is used the ^ operator. If you are doing the antilog for some other base, there is formula to do that as well, but I'm forgetting it. Regards, Yasir On Fri, 26 Dec 2003, Martin Marques wrote: > Date: Fri, 26 Dec 2003 19:34:35 -0300 > From: Martin Marques <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED], > Sai Hertz And Control Systems <[EMAIL PROTECTED]>, > [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: Re: [SQL] Anti log in PostgreSQL > i> El Vie 26 Dic 2003 19:12, Sai Hertz And Control Systems escribió: > > Dear all , > > > > In one of our project I require to calculate antilog of (3.3234) > > But I could not find any functions in Documentation for the same. > > > > In mathematics I would have written it something like > > > > A = antilog (3·3234) = 2144 > > As I can understand, this is a 10 base log, so that what you want is > 10^(3.3234)? > > For that you have the exponential operator ^. > > -- > select 'mmarques' || '@' || 'unl.edu.ar' AS email; > - > Martín Marqués |[EMAIL PROTECTED] > Programador, Administrador, DBA | Centro de Telemática >Universidad Nacional > del Litoral > - > > > ---(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 > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Trouble with composite types
Hello, I don't know why this message is not going through! I have the following types: create type street_type as ( street_number smallint, street_name text, apt_number smallint ); create type address_type as ( street street_type, citytext, state char(2), zip_codechar(5) ); When I enter that into the command prompt, I the following message: ERROR: Attribute "street" has composite type street_type Why is it giving me error message for something I know is true? Also, how do I add a member function to a type? Thanks, Yasir ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] designer tool connect to PostgreSQL
Well, there's phpPgAdmin. It's available at http://phppgadmin.sourceforge.net/ Yasir On Mon, 8 Mar 2004 [EMAIL PROTECTED] wrote: > Date: Mon, 8 Mar 2004 10:13:53 +0800 > From: [EMAIL PROTECTED] > To: [EMAIL PROTECTED] > Subject: [SQL] designer tool connect to PostgreSQL > > Hi, > > i use postgresql as my database. does anyone know the designer tool that > can connect to postgeSQL ??? meaning to say the tools > can handle design task like create table , etc . appreciate if u can give > the specific URL. thanks in advance. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Object-relational features
Hello, For my object-relational database class I decided to use PostgreSQL because it is my favorite database and it calls it self a ORDBMS. Little did I know that it supports supports very little OR features. For example, using "create type as" is totally worthless because you can't use it as a field type in a table; you can't compose in another "create type as"; and you can't inherit another composite type. The only way to create a true type is to use "create type" and write C code as a shared object, so I'm basically doing everything C, which is not something I want to do. I've searched the mailing lists and have found little said about the OR features. Am I missing something here? Does PostgreSQL support OR features similar to Oracle 9i (which is what I'm forced to use). I really do not want to use Oracle because I have to switch over to my Windows partition, and Oracle takes about 100 MB of virtual memory on my 256 MB machine. Thanks, Yasir ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Object-relational features
Dr. DeSoi
Thanks for your reply.
What is an "immutable strict" and what is an "internal" language?
Does Postgres plan on implenting types as Oracle does? For example:
create type AddrType as (street char(20), city char(20), zip char(5));
create type CustType as (name varchar(2), addr AddrType);
create table Cust of CustType;
Which is superior to Postgres's way of implementing types (writing a C
function).
Thanks,
Yasir
On Mon, 15 Mar 2004, John DeSoi wrote:
> Date: Mon, 15 Mar 2004 08:28:50 -0500
> From: John DeSoi <[EMAIL PROTECTED]>
> To: Yasir Malik <[EMAIL PROTECTED]>
> Cc: PostgreSQL <[EMAIL PROTECTED]>
> Subject: Re: [SQL] Object-relational features
>
>
> On Mar 13, 2004, at 12:30 PM, Yasir Malik wrote:
>
> > For
> > example, using "create type as" is totally worthless because you can't
> > use
> > it as a field type in a table; you can't compose in another "create
> > type
> > as"; and you can't inherit another composite type. The only way to
> > create
> > a true type is to use "create type" and write C code as a shared
> > object,
> > so I'm basically doing everything C, which is not something I want to
> > do.
>
>
> I'm not sure if this is what you are looking for, but it shows how to
> create a column type based on the text type. So your selects will
> return the column type as your custom type and you can process the
> content accordingly. From reading the docs (and asking on the list) I
> did not think this was possible either without writing external code in
> C. But a post about something else finally provided the clues I needed
> to get it working.
>
> Best,
>
> John DeSoi, Ph.D.
>
>
>
> test=# create or replace function lispin(cstring, oid, int4) returns
> lisp as 'varcharin' language 'internal' immutable strict;
> NOTICE: type "lisp" is not yet defined
> DETAIL: Creating a shell type definition.
> CREATE FUNCTION
> test=# create or replace function lispout(lisp) returns cstring as
> 'varcharout' language 'internal' immutable strict;
> NOTICE: argument type lisp is only a shell
> CREATE FUNCTION
> test=# create type lisp (input=lispin, output=lispout,
> internallength=variable);
> CREATE TYPE
> test=# create table tst (a lisp);
> CREATE TABLE
> test=# insert into tst (a) values ('1');
> INSERT 18499 1
> test=# insert into tst (a) values ('(+ 5 5)');
> INSERT 18500 1
> test=# select * from tst;
> a
> -
> 1
> (+ 5 5)
> (2 rows)
>
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faqs/FAQ.html
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] a wierd query
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Yes you can. Try this:
(select string_A from main_table) union (select string_B from main_table)
Yasir
On Thu, 13 May 2004, [EMAIL PROTECTED] wrote:
> Date: Thu, 13 May 2004 04:07:08 -0400
> From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED]
> Subject: [SQL] a wierd query
>
> hi
>
> i have a wierd problem and i require an equally weird query.
> 1) backgound
> Table test:
> CREATE TABLE main_table (
>string_A varchar( 20),
>string_B varchar( 20),
>);
> -- both columns are identical in nature and usage
> INSERT INTO main_table VALUES('abcd','qrst');
> INSERT INTO main_table VALUES('efgh','efgh');
> INSERT INTO main_table VALUES('ijkl','abcd');
> INSERT INTO main_table VALUES('abcd','ijkl');
> INSERT INTO main_table VALUES('qrst','uvwx');
>
> 2) problem:
>
> i require a query that gives me a result set of the form
>
>'abcd'
>'efgh'
>'ijkl'
>'qrst'
>'uvwx'
>
>that is i require the dictinct values from (visualizing each column
> result as a set) the union of the two columns
>
> 3) questions
>
>a) is a query like this possible that can give me the desired result
>b) if so what would it be.
>
> 4) remarks
>
> i can get the solution using a temporary table and with repeated
> "insert into temporary select $column from main_table"
>
>
> thanks in advance
>
> ashok
>
>
> mail2web - Check your email from the web at
> http://mail2web.com/ .
>
>
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.3 (NetBSD)
iQEVAwUBQKN2/+J7vYSSIbWdAQKklQf+JPhyMpbhEVX/4t70r1m6RFPXkm2VgbOz
Dyxkjhbko07c+YcnVbHmk/8D0d+1L0Qx23vytCfvqRS29O5tzwDFrSfHCZQ8WE4C
H7P0377jfa/LxgAeaUNnDfhhGj+qUI649i2QDSzdalVVwKtUl/aKdw0+evveuUXZ
QBYvVeoFU9KrnqBbQNW6AQOM8vfnYG3cxcb87krRy/b2EgZE462o2O3jGhqvlmrU
8eKJCrEnv4t53IOI3J2WECKbuSomTrUAqfUWbpL6g7zrOpkuCTqzTuOrx+7ISMTR
zyY36zUDeOB/A7u3PEh+wQz/Yqdq1Gu9GQ3kIsgao1WA+K3tj1ceKA==
=zMMM
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Replace function ...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > I need to write a function which has to replace a character with two or three other > characters > You can use the replace function as specified below: replace(string text, from text, to text) text Replace all occurrences in @string of substring @from with substring @to. replace( 'abcdefabcdef', 'cd', 'XX') ==> abXXefabXXef Yasir -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (NetBSD) iQEVAwUBQKkT1+J7vYSSIbWdAQL/5QgAmCdJrYh4YjJFWKeWNk+OEuhpvRNYbRlM tH5dezmUrR4fkM4l2mfrmuIkTT+rqQNyRR3E5PhKyfkbnHdw7/WPalqGSbX1aIfP YZI/RdxH/Aqy+hO+zcUQzSjpYSEyKrmTVGLmRf+tyYg0QdZdAQFd6O9aKWMeCo28 TFKRFAlg+glT/YiN1JiwzF/QygBNmo3g+JDWZ+U8Au+y0fHh/3KeoyLx9ipMSqs/ uBiy8eNjOdAF3ihoydDw2uBpzjwtGv9eeR0Myh8m4Zvx9K239NVSz4s9a+2R1Onf Mr5PK7Te5TG8TLcjJyo35x8yC/57oufNvWoi+Q6Y7cmSxeiEcHzJiw== =xChX -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] ANSI SQL-99 SYNTAX and "WITH"
> Good morning,afternoon, and evening Everyone, > > My research into PostgreSQL documentation has yet to yeild any information about > PostgreSQL's support of ANSI SQL-99 syntax, and in particular, the "WITH" clause - > which if I understand correctly, allows recursive joins in simple syntax without the > use of cursors and/or otherwise klugey SQL. > > As you may have guessed, I hope PostgreSQL currently supports the "WITH" clause > syntax or, will at least support it imminently. No, PostgreSQL does not support the WITH clause. I wanted to use the WITH clause in an assignment, but I ended up using a view. Maybe that will work for you. Yasir ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] create function atof?
> Is it possible to create a database function that mimics the C function atof? > I'm guessing it should look something like this: You can do this to convert a string to a float: select '3.14'::float + 1; ?column? -- 4.14 (1 row) Is that what you want? Yasir ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] plpgsql syntax
for example create function a_func() return integer as . end a_func after as and before end a_func marks the definition body. Would this be better than the ' '? I guess having a parser that gave better error messages would be nice, too. Yasir ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] inserting values into types
CREATE TYPE qwerty_UDT AS (abc INT); CREATE TABLE t (col1 qwerty_UDT); INSERT INTO t (col1) VALUES (qwerty_UDT(123)); ERROR: function qwerty_udt(integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. It seems as though you want to create your own user-defined types. That isn't easy in PostgreSQL. You have to create an external C struct and write a function to convert a PostgreSQL string to a C struct and return it to the database (called an input function), and create another function that takes an object from the database and returns a string to the database in order to print out the object (called an output function). The only thing PostgreSQL knows about the object is size of the object and the input and output functions. You also need to be the database administrator. Go here for more information: http://www.postgresql.org/docs/current/static/xtypes.html I had to create an object-oriented database for one of my classes, and although I wanted to use PostgreSQL, I didn't want to deal with so low level stuff. Oracle is much better for object-oriented features. Regards, Yasir ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Date/Time Conversion
beta_jgw=# update scenario1.time_test set local_hour = extract(hour from to_timestamp(to_char(gmt_date,'-MM-DD')||' '||to_char(gmt_hour,'99')||':00:00-00','-MM-DD HH24:MI:SS') at time zone 'EST'); This sounds like a stupid answer, but shouldn't that be :00:00:00? Regards, Yasir ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Migrated Oracle database to PostgreSQL ??
1. How to migrate Procedures and Packages from Oracle to PostgreSQL 2. How to find error messages in PostgreSQL thrown from an Exception (as we can get messages in Oracle with "SQLERRM" keyword) for example -- --Raise an exception RAISE EXCEPTION 'No Data found for record id % ' ,recordid --Insert exception messages into database EXCEPTION IF RAISE_EXCEPTION THEN --my_insert_function(SQLERRM); All your answers can be found here: http://www.postgresql.org/docs/8.0/static/plpgsql.html I can't say if PL/pgSQL is as good as PL/SQL, though. Yasir ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Date/Time Conversion
beta_jgw=# update scenario1.time_test set local_hour = extract(hour from to_timestamp(to_char(gmt_date,'-MM-DD')||' '||to_char(gmt_hour,'99')||':00:00-00','-MM-DD HH24:MI:SS') at time zone 'EST'); Wild guess, but shouldn't that be :00:00:00? Regards, Yasir ---(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] PHP postgres connections
Hi,
I need to connect to 2 differents Postgres 8.0.0
databases located in the same machine using the same
PHP script with an "db wrapper object" instance
(pg_Connect)... simply a PHP page with contemporarily
2 database connections...
I don't think this is the right place to ask this, but there's an example
on php.net for using the pg_connect():
$dbconn = pg_connect("dbname=mary");
//connect to a database named "mary"
$dbconn2 = pg_connect("host=localhost port=5432 dbname=mary");
// connect to a database named "mary" on "localhost" at port "5432"
$dbconn3 = pg_connect("host=sheep port=5432 dbname=mary user=lamb
password=foo");
//connect to a database named "mary" on the host "sheep" with a username
and password
$conn_string = "host=sheep port=5432 dbname=test user=lamb password=bar";
$dbconn4 = pg_connect($conn_string);
//connect to a database named "test" on the host "sheep" with a username
and password
?>
I don't know if that answers your question.
Can I use however persistent connections ?
pg_pconnect() works the same way.
Regards,
Yasir
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] using pg_tables and tablename in queries
The current behavior is by design.
We use the table as a logging repository. It can get very large 250 000
records. Because of the large number of records that we have in the table we
found it was much faster to perform inserts on a smaller table. Our current
system rolls the tables over every 12 hours or so, creating a new table with
the following behavior:
CREATE TABLE mytable_temp {...}
ALTER TABLE mytable RENAME TO mytable_back_datetime;
ALTER TABLE mytable_temp RENAME TO mytable;
I want to join the mytable_back_datetime tables together in order to perform
queries against my huge set of data to generate some reports. I'm probably
going to create a temporary table with a few indexes to make the reports run
faster... however I need to join the tables all together first.
I would create a function that creates a string with a query that includes
all the tables you need, and call execute on the string. You would loop
through the all tables from pg_tables and keep on appending the table name
you need.
Regards,
Yasir
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] regular expression
This isn't a PostgreSQL issue, but rather one of understanding how regular expressions work in general. See the previously mentioned "Pattern Matching" section of the PostgreSQL documentation for the PostgreSQL-specific details, and use a search engine to find a regular expression tutorial; they're frequently used in Perl and other languages so a lot of learning material exists. If you can find a copy in a library or bookstore, the book _Mastering Regular Expressions_ by Jeffrey E. F. Friedl, published by O'Reilly, is a good resource. If you have Perl installed, do a 'man perlretut'. It is quite a wonderful tutorial. That is what I used to learn regular expressions. Yasir ---(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] how to use recursion to find end nodes of a tree
Hello All,
I have been having a really hard time trying to come up with a pl/pgsql
recursive function to returns the end nodes of a tree.
Here is an example table definition:
CREATE TABLE parent_child (
parent_id integer NOT NULL,
child_id integer NOT NULL
);
INSERT INTO parent_child (parent_id, child_id) VALUES (1, 2);
INSERT INTO parent_child (parent_id, child_id) VALUES (1, 3);
INSERT INTO parent_child (parent_id, child_id) VALUES (1, 4);
INSERT INTO parent_child (parent_id, child_id) VALUES (2, 5);
INSERT INTO parent_child (parent_id, child_id) VALUES (2, 6);
INSERT INTO parent_child (parent_id, child_id) VALUES (4, 7);
INSERT INTO parent_child (parent_id, child_id) VALUES (4, 8);
INSERT INTO parent_child (parent_id, child_id) VALUES (4, 9);
INSERT INTO parent_child (parent_id, child_id) VALUES (9, 10);
This produces the following tree of data:
1
___|___
| | |
2 3 4
_|_ _|_
| | | | |
5 6 7 8 9
|
10
I want to create a function that returns the terminating nodes of
of this tree below a certain level i.e. if I input 1 to the function
I need it to return 5,6,3,7,8,10. If I input 4 to the function I would
get 7,8,10. I have written recursive functions which return all nodes
on a branch of a tree but I can't think of a way to return the end nodes
does anyone know of a solution?
I haven't programmed in PL/pgSQL in a while, but I'll write some pseudo
code. I think the code should be similar:
func(int node)
{
dynamic_array s;
dynamic_array leaves;
int top, count, leaf_id, popped, child;
leaf_id = top = 0;
s[top] = node;
count = 1;
// to a depth first search
while(count != 0)
{
popped = s[top];
top--;
count--;
foreach(select pc.child_id into child from parent_child pc where
pc.parent_id = popped)
{
select * from parect_child pc where parent_id = child;
// a count of zero indicates that child node has no children
if(count_of_above_query = 0)
{
leaves[leaf_id] = child;
leaf_id++;
}
else
{
// not a leaf, so add it to the stack for the next time through
// the loop
top++;
s[top] = child;
count++;
}
}
}
return leaves;
}
Regards,
Yasir
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Am I crazy or is this SQL not possible
What I would like to do is simply get the last date_sent and it's status for every broadcast. I can't do a GROUP BY because I can't put an aggregate on the status column. SELECT MAX(date_sent), status FROM broadcast_history GROUP BY broadcast_id You could try the following: select status from broadcast_history bh where bh.date_sent = (select max(bh2.date_sent) from broadcast_history bh2); This reminds me of an interview question: I was asked how to get a maximum column from a table without using max. How would you do that? Thanks, Yasir ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Am I crazy or is this SQL not possible
select max(date_sent) from table; would equal select date_sent from broadcast_history order by date_sent DESC limit 1; That sounds like a hack. Is limit a SQL-99 standard? Is there are another way to do this? Sorry to take over your topic, Collin. Thanks, Yasir ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Am I crazy or is this SQL not possible
It is a hack, but when someone wants you to do something in a way different from the norm, aren't they asking for a hack? SQL Server does something like select top (1) from I am thinking this is NOT a SQL-99 standard. This was an interview with Amazon, and I don't think Amazon wanted a hack. I hope Amazon doesn't use hacks. There has to be another way. It would be cruel if they expected me know some database specific functionality. Here's what I gave them: select value from table t where t.value > (select t2.value from table t2); which would be fine if the sub-select returned simply a list, but that isn't even valid SQL (I didn't get the job, BTW). Yasir ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
