Re: Adding Missing Data to a Table

On Tue, 29 Mar 2011, Michael Ewan wrote:

> Are you planning on using 'record' as a PK or simply an index? Will it be an
> FK constraint on some other table.

Michael,

As a PK. There will be other tables linked to it.

> Is there a combination of columns that would be unique so you could use
> them as a composite primary key.

I suppose I could use location + sample_date + parameter but a single
column would be simpler.

> It is generally a bad idea to use a synthetic key as a primary key.

Yeah, that's Joe Celko's view, too. But, there is no natural key in these
data. There's a reference number (called 'record') and a sample number
(one or both probably assigned by the analytical lab).

> ... there is no significance to the record, then don't use it.

It's been assigned by the client company. I doubt they'd throw in a column
for which they had no use.

> The only way you can safely update that column is using a unique primary
> key, and if you create a unique primary key, you don't need the 'record'
> column anymore.

What I want is to use the record key as the unique primary key. This is
NOT a postgresql assigned rowid or OID, this is part of the data. Think
column name 'record' is to be the unique primary key (redundant, I know).
However, to the best of my knowledge I cannot add the table constraint of
primary key to that column until the NULLs are replaced by values. How best
to do that is what I need to learn.

Is this a job for PL/pgSQL or PL/ggPython?

Rich