Re: Filling Missing Primary Key Values - Mailing list pgsql-general

From David Johnston
Subject Re: Filling Missing Primary Key Values
Date
Msg-id 085A2A56-CBF0-4BD3-B6AB-D68F660AA224@yahoo.com
Whole thread Raw
In response to Re: Filling Missing Primary Key Values  (Chris Travers <chris.travers@gmail.com>)
List pgsql-general
On Aug 11, 2011, at 15:08, Chris Travers <chris.travers@gmail.com> wrote:

> 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 technical aspect is covered but consider using one or two characters as a prefix related to the data source.
You'vealready taken the hit for using a text data type so you might as well take advantage of it.  Even if you have a
sourcefield this can be useful.  The only costraint is you limit the number of sequence values you can use (per
source).

If you use the sequence you can always reset it between imports.

David J.

pgsql-general by date:

Previous
From: Chris Travers
Date:
Subject: Re: Filling Missing Primary Key Values
Next
From: Steve Atkins
Date:
Subject: Re: Filling Missing Primary Key Values