Re: yowch: dumpRules(): SELECT failed for table website.

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