Re: Adding Missing Data to a Table

Rich Shepard wrote:
> On Tue, 29 Mar 2011, Rod wrote:
>
>> 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,
>
> I just realized that this would require a schema change. I defined the
> 'record' column as VARCHAR(10) because, while it is numeric, it will not be
> arithmetically manipulated. To have a column data type of SERIAL and define
> a sequence requires a change.

A CAST should handle getting the data from the tmp_col back into record.
The serial was a simple way to get the column incrementally filled.

And it appears I got caught up in the solution and forgot the record
column was (going to be?) coming with the data from this point on. No
need to create the SEQUENCE. Just a UNIQUE INDEX will keep it all straight.

> I'll think about this. Perhaps I create a new column of type SERIAL and
> copy into it all rows from record. Then drop the records column and rename
> the serial column to record. Then follow your script.
>
> As I wrote initially, all this manipulation is new to me. My uses of
> postgres the past dozen or so years have been very simple. Now I'm setting
> up a biological database that links existing hydrologic, hydrographic, and
> water quality tables to others for fish and macroinvertebrates. Plowing new
> ground, so to speak.

I feel your pain.

Rod
--