[lug] (OT) postgresql

[Sexton, George]

The way you should really do this is to create a sequence:

CREATE SEQUENCE users_seq;

CREATE TABLE Users (
	user_id				int NOT NULL PRIMARY KEY DEFAULT NEXTVAL('users_seq'),
	user_name			VARCHAR(12) NOT NULL,
	....

to read the value just inserted:

select CurrVal('users_seq') as newKey


Select max() is incredibly bad. In a multi-user application there are no
protections that two people won't each execute the select max(), and then
perform their insert.

It also will lock the table for the duration of the statement.

Do not do select max().

George Sexton
MH Software, Inc.
Home of Connect Daily Web Calendar Software
http://www.mhsoftware.com/connectdaily.htm
Voice: 303 438 9585


-----Original Message-----
From: lug-admin at lug.boulder.co.us [mailto:lug-admin at lug.boulder.co.us]On
Behalf Of j davis
Sent: 10 November, 2002 3:21 PM
To: lug at lug.boulder.co.us
Subject: [lug] (OT) postgresql



Hello,
  is there a postgresql select query i can enter to specify
the last row of a table...i.e. i want to make a new invoice number based
on the inv_num field of the last customer entry into a table.the only thing
i can think off is to run a loop on the data base using perl to increment
a var + 1 for every row in the table...then use this var to get a query
to the last row...hope this is coherent :)

thanks,
jd

jd at taproot.bz
http://www.taproot.bz

"Who you trying to get crazy with essay?
          Don't you know I'm loco?"

-Cypress Hill



_________________________________________________________________
Add photos to your e-mail with MSN 8. Get 2 months FREE*.
http://join.msn.com/?page=features/featuredemail

_______________________________________________
Web Page:  http://lug.boulder.co.us
Mailing List: http://lists.lug.boulder.co.us/mailman/listinfo/lug
Join us on IRC: lug.boulder.co.us port=6667 channel=#colug