On Thu, Aug 11, 2011 at 11:47 AM, Rich Shepard <rshepard@appl-ecosys.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