From: | "Roderick A(dot) Anderson" <raanders(at)cyber-office(dot)net> |
---|---|
To: | pdxpug(at)postgresql(dot)org |
Subject: | Re: Adding Missing Data to a Table |
Date: | 2011-03-30 04:26:14 |
Message-ID: | 4D92B0E6.2010101@cyber-office.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pdxpug |
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
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Roderick A. Anderson | 2011-03-30 04:43:45 | Re: Adding Missing Data to a Table |
Previous Message | Rich Shepard | 2011-03-29 22:37:33 | Re: Adding Missing Data to a Table |