From: | "Roderick A(dot) Anderson" <raanders(at)cyber-office(dot)net> |
---|---|
To: | Rich Shepard <rshepard(at)appl-ecosys(dot)com> |
Cc: | Postgresql PDX_Users <pdxpug(at)postgresql(dot)org> |
Subject: | Re: Adding Missing Data to a Table |
Date: | 2011-03-30 04:43:45 |
Message-ID: | 4D92B501.2050809@cyber-office.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pdxpug |
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
--
From | Date | Subject | |
---|---|---|---|
Next Message | Rich Shepard | 2011-03-30 12:47:53 | Re: Adding Missing Data to a Table |
Previous Message | Roderick A. Anderson | 2011-03-30 04:26:14 | Re: Adding Missing Data to a Table |