Re: Filling Missing Primary Key Values

On Thu, Aug 11, 2011 at 11:47 AM, Rich Shepard <rshepard(at)appl-ecosys(dot)com> wrote:
>  I've a table (from a client, not created here) with a column that should
> be the primary key, but not all rows have a value for this attribute. The
> column format is VARCHAR(12) and has a variety of values, such as 96-A000672
> and 9612-0881 (probably assigned by different analytical laboratories).
>
>  A simple sequence of numbers would do the job of replacing NULL values.
> What is the most parsimonious way to replace NULLs with unique values for
> this column? I also need to add such values for a new set of data that I'm
> in the process of translating from spreadsheet format to the table
> structure.
>
The simplest seems to me to be a sequence and use nextval() to
populate the null values. The major advantage would be that the
sequence could stay around in case you need it again. So for example:

create sequence my_varchar_values;

UPDATE my_table set my_varchar =
nextval('my_varchar_values')::varchar(12) where my_varchar IS NULL;

You could also use windowing functions to get rid of the sequence, but
the queries become a lot more complicated. For example, see
http://stackoverflow.com/questions/4358613/using-window-functions-in-an-update-statement

Best Wishes,
Chris Travers