Re: Adding Missing Data to a Table

Rich Shepard wrote:
> I'm now moving into areas of postgres I've not before visited so now and
> then I'll ask for directions.
>
> I have a table with 49365 rows. The first column, which I would like to
> use as an index, is called 'record' and of type VARCHAR(10). There are
> 39159
> rows in which record IS NULL. I would like to add values to 'record'
> starting with 30000. There's no significance to the value of this column
> other than as a unique row identifier, and the highest current value is
> 28569. What is the most efficient way to automate this?

Sorry I originally sent this reply to Wes instead of the list.

If I've followed what you want/need correctly.

ALTER TABLE your_table ADD COLUMN tmp_col SERIAL;

UPDATE your_table SET tmp_col = tmp_col + 300000;

UPDATE your_table SET record = tmp_col WHERE record IS NULL;

ALTER TABLE your_table DROP COLUMN tmp_col;

SELECT max(record) FROM your_table;

I'll leave the steps to make the record column a PRIMARY KEY, the
SEQUENCE to generate it and the UNIQUE CONSTRAINT to enforce it to you
or others.

Rod
--

>
> Rich
>
>