Re: [SQL] PostgreSQL and Delphi 6

2005-07-07 Thread Mathew Winters
I am a bit late posting, I use Zeos DBO controls, they can be found on sourceforge.. I actually use the V5 of the components with some bug fixes I done myself, but my project is too far along to change to the newer components.. They are free and all source provided.. :) Postgres Admin wrote:

list admin note:Fwd: RE: [SQL] two sums in one query

2005-07-07 Thread Kenneth Gonsalves
this guy is still polluting this list: -- Forwarded Message -- Subject: RE: [SQL] two sums in one query Date: Friday 08 Jul 2005 11:33 am From: AntiSpam UOL <[EMAIL PROTECTED]> To: lawgon <[EMAIL PROTECTED]> http://antispam.uol.com.br"; style=

[SQL] two sums in one query

2005-07-07 Thread Kenneth Gonsalves
hi i have a table like this: serialno varchar, debit integer, credit integer, amount numeric the columns 'debit' and 'credit' refer to the acount number. I want to query the table to select all the rows where either debit = account or credit=account and to get two sums - one of the amounts wher

Re: [SQL] Make COUNT(*) Faster?

2005-07-07 Thread Tom Lane
Chris Browne <[EMAIL PROTECTED]> writes: > [EMAIL PROTECTED] (Varun Mehta) writes: >> If I run an EXPLAIN on this query I can see that it is doing a >> sequential scan, which seems quite needless, as surely this >> information is cached in some secret location. > [ example scenario snipped ] > If

Re: [SQL] Make COUNT(*) Faster?

2005-07-07 Thread Chris Browne
[EMAIL PROTECTED] (Varun Mehta) writes: > If I run an EXPLAIN on this query I can see that it is doing a > sequential scan, which seems quite needless, as surely this > information is cached in some secret location. That would in fact surely *NOT* be the case. If you have multiple users performin

Re: [SQL] getting back autonumber just inserted

2005-07-07 Thread Michael Fuhr
On Fri, Jul 08, 2005 at 05:03:37AM +0200, PFC wrote: > >On Fri, Jul 08, 2005 at 01:56:26AM +0200, PFC wrote: > >>Hm, interesting, you mean the return value of lastval() also depends > >>if you set your constraints to deferred or immediate ? > > > >My mind's ablank trying to contrive a situation w

Re: [SQL] getting back autonumber just inserted

2005-07-07 Thread PFC
On Fri, Jul 08, 2005 at 01:56:26AM +0200, PFC wrote: >Do you mean with lastval()? Here's what happens: Hm, interesting, you mean the return value of lastval() also depends if you set your constraints to deferred or immediate ? My mind's ablank trying to contrive a situation where that wou

Re: [SQL] Make COUNT(*) Faster?

2005-07-07 Thread Michael Fuhr
On Thu, Jul 07, 2005 at 03:48:39PM -0700, Varun Mehta wrote: > > I've started using PostgreSQL pretty recently, and I am quite > disturbed about the performance of a simple SELECT COUNT(*) FROM > table. What should (in my mind) be a nearly instantaneous operation > instead takes nearly 700m

Re: [SQL] getting back autonumber just inserted

2005-07-07 Thread Michael Fuhr
On Fri, Jul 08, 2005 at 01:56:26AM +0200, PFC wrote: > >Do you mean with lastval()? Here's what happens: > > Hm, interesting, you mean the return value of lastval() also depends > if you set your constraints to deferred or immediate ? My mind's ablank trying to contrive a situation where that

Re: [SQL] getting back autonumber just inserted

2005-07-07 Thread PFC
What happens if an INSERT trigger inserts something into another table which also has a sequence ? Using what, lastval()? The app will get very confused, because it'll get the value from the sequence used in the trigger. Using currval there is no problem, but you already kne

Re: [SQL] getting back autonumber just inserted

2005-07-07 Thread PFC
Do you mean with lastval()? Here's what happens: Hm, interesting, you mean the return value of lastval() also depends if you set your constraints to deferred or immediate ? I wond ---(end of broadcast)--- TIP 6: Have you searched our list

[SQL] Make COUNT(*) Faster?

2005-07-07 Thread Varun Mehta
Hello all you PostgreSQL/SQL gurus! I've started using PostgreSQL pretty recently, and I am quite disturbed about the performance of a simple SELECT COUNT(*) FROM table. What should (in my mind) be a nearly instantaneous operation instead takes nearly 700ms in a table with only 87k rows of

Re: [SQL] getting back autonumber just inserted

2005-07-07 Thread Michael Fuhr
On Fri, Jul 08, 2005 at 12:26:30AM +0200, PFC wrote: > > >That's a different issue than whether currval() is subject to > >interference from other transactions. And just wait until PostgreSQL > >8.1 comes out and people start using lastval() -- then it could get > >*really* confusing which sequen

Re: [SQL] getting back autonumber just inserted

2005-07-07 Thread Alvaro Herrera
On Fri, Jul 08, 2005 at 12:26:30AM +0200, PFC wrote: > > >That's a different issue than whether currval() is subject to > >interference from other transactions. And just wait until PostgreSQL > >8.1 comes out and people start using lastval() -- then it could get > >*really* confusing which sequen

Re: [SQL] getting back autonumber just inserted

2005-07-07 Thread PFC
That's a different issue than whether currval() is subject to interference from other transactions. And just wait until PostgreSQL 8.1 comes out and people start using lastval() -- then it could get *really* confusing which sequence value you're getting. What happens if an INSERT trigger ins

Re: [SQL] getting back autonumber just inserted

2005-07-07 Thread Michael Fuhr
On Thu, Jul 07, 2005 at 02:47:23PM -0600, Larry Meadors wrote: > > If you have a trigger on your table that inserts a record in a table > and shares the same sequence, what value do you get back, the > triggered curval, or the currently inserted one? That's a different issue than whether currval()

Re: [SQL] getting back autonumber just inserted

2005-07-07 Thread Michael Fuhr
On Thu, Jul 07, 2005 at 01:14:33PM -0700, Theodore Petrosky wrote: > > you have to use currval inside a transaction... Not true. Have you observed otherwise? > begin; > insert something that increments the counter; > select currval('sequence_name'); > end; > > using currval inside a transactio

Re: [SQL] getting back autonumber just inserted

2005-07-07 Thread mail TechEvolution
THX to all guys, it is working great !! greetZ wes ---(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] getting back autonumber just inserted

2005-07-07 Thread Scott Marlowe
On Thu, 2005-07-07 at 15:14, Theodore Petrosky wrote: > you have to use currval inside a transaction... > > begin; > insert something that increments the counter; > select currval('sequence_name'); > end; > > using currval inside a transaction guarantees that the > value is correct for your inser

Re: [SQL] getting back autonumber just inserted

2005-07-07 Thread Theodore Petrosky
you have to use currval inside a transaction... begin; insert something that increments the counter; select currval('sequence_name'); end; using currval inside a transaction guarantees that the value is correct for your insert statement and has not changed by another insert statement. Ted ---

Re: [SQL] getting back autonumber just inserted

2005-07-07 Thread Alvaro Herrera
On Thu, Jul 07, 2005 at 08:21:12PM +0200, mail TechEvolution wrote: > hi Alvaro Herrera > > >>You use the currval() function, using the name of the involved sequence > >>as parameter. There is a pg_get_serial_sequence() function, to which > >>you give the table name and column name > > thx for t

Re: [SQL] getting back autonumber just inserted

2005-07-07 Thread mail TechEvolution
hi Alvaro Herrera You use the currval() function, using the name of the involved sequence as parameter. There is a pg_get_serial_sequence() function, to which you give the table name and column name thx for the information and is there a function i can use to get the last added autonumber wi

Re: [SQL] getting back autonumber just inserted

2005-07-07 Thread Alvaro Herrera
On Thu, Jul 07, 2005 at 07:50:16PM +0200, mail TechEvolution wrote: > hello > > i ame a newbie to PostGreSQL, I ame using PostGreSQL 8.0 > (windowsinstaller) on a xp prof platform > > i would like to get back the autonumber from the last record inserted, > for other SQL db (m$ sql db ...) i cou

[SQL] getting back autonumber just inserted

2005-07-07 Thread mail TechEvolution
hello i ame a newbie to PostGreSQL, I ame using PostGreSQL 8.0 (windowsinstaller) on a xp prof platform i would like to get back the autonumber from the last record inserted, for other SQL db (m$ sql db ...) i could use: SELECT @@ IDENTITY can someone help me by informing me what the SQL sy