On Wed, May 24, 2000 at 03:33:39AM -0700, Alfred Perlstein wrote:
> I'm wondering if there's a way to get a unique value into a table?
>
> this caused some problems:
>
> CREATE TABLE "data" (
> "d" varchar(256) PRIMARY KEY,
> "d_id" serial
> );
>
> because after I reloaded the table from:
>
> insert into data select * from data_backup;
>
> then tried to insert into 'data' using only values for 'd' then it barfed
> because it was trying to use values from the serial that were already
> in the table.
>
> is there a way around this? using OID doesn't seem right, but seems to
> be the only "safe" way to get a truly unique key to use as a forien key
> that I've seen.
>
> any suggestions?
>
Right, I assume this is after you recreated the table? That created a new
sequence behind the serial for d_id, which needs to be updated after you
insert explicit values into the id field. here's my standard fix for that
SELECT setval('data_d_id_seq',max(d_id)) from data;
The name of the sequence is <tablename>_<serial field name>_seq,
trimmed to fit in NAMEDATALEN (default 30). If you created the table
with a different name, that's how the sequence is named (they're not
automatically renamed, or dropped, with their associated table)
I do this whenever I load data into a table manually. Hmm, it might be
possible to setup a trigger (or rule?) to handle the non-default case
(i.e., whenever a serial values is actually provided) and do this
automatically. It'd only need to fire if the inserted/updated value is
greater than currval of the sequence. Hmm...
Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
