Yeah, you're looking for a "sequence" IE:
CREATE SEQUENCE seq_Users_NDX;
CREATE TABLE Users ( User_NDX Integer default nextval('seq_Users_NDX), UserName Text
);
Then you would insert into the table using:
INSERT into Users (UserName) VALUES ('edwestfield', 'ctassell');
The only problem is that it doesn't work with COPY, only INSERT. When
copying large amounts of data into a table, I just write a simple PERL/C
program that gets the nextval of the sequence, locks the table, copies in
all the records incrementing the internal sequence counter for each record,
then uses setval (I think that's the name of the function) to set the
sequence to whatever the last value was I used, and unlock the table. Not
pretty, but it works. :)
At 05:07 PM 5/19/99, E Westfield wrote:
>Greetings
>Looking for how to build a table with a built in unique sequential numeric
>key (primary optional) and then copy from a flat file to that same field.
>Can not see on the _CREATE TABLE_ nor the _TYPE_ how to do so. Is the OID
>mentioned in the _COPY_ a possible.
>
>I wish to have a new unique ID for any additions to the table without
>haveing to programmatically create one. This is done in other databases so
>I am sure it is available in Postgresql. Thank you for you help in this
>slow learning one.
>
>
>
>--
>E Westfield
>SCRIBE Network Coordinator
>http://www.faire.net/SCRIBE
>
>
>
>