Skip site navigation (1) Skip section navigation (2)

Re: Adding Missing Data to a Table

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 (view raw or flat)
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
> 
> 


In response to

pdxpug by date

Next:From: Roderick A. AndersonDate: 2011-03-30 04:43:45
Subject: Re: Adding Missing Data to a Table
Previous:From: Rich ShepardDate: 2011-03-29 22:37:33
Subject: Re: Adding Missing Data to a Table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group