Re: serial column - Mailing list pgsql-general

I would tend to agree with Tom.

A table is by definition an unordered set of records.  Forcing keys to
have meaning of this type implies that there is a relationship between
each record in the set.  That's information you should be storing as
part of the record.  If order is important, design the database so that
it knows that order relationship exists.

An ordered list is just a hierarchal database wherein every record has
exactly one parent (or none if it's root) and exactly one child (or none
if it's end leaf), but the relational model does a rather poor job of
handling hierarchal relationships.  You might consider the two-way
linked list approach.  That is, each record knows the item before it and
the item after it, like so:

TABLE mainTable
{
    id serial PRIMARY KEY,
    foo text,
    bar integer,
    zen numeric
}

TABLE mainTableRelationships
{
    parentID integer,
    childID integer,
    CONSTRAINT "mainTableRelationships_pkey" PRIMARY KEY
("parentID", "childID"),
    CONSTRAINT "parentID_key" UNIQUE ("parentID"),
    CONSTRAINT "childID_key" UNIQUE ("childID"),
    CONSTRAINT "mainTable_parentID_fkey" FOREIGN KEY ("parentID")
          REFERENCES "mainTable" ("id"),
    CONSTRAINT "mainTable_childID_fkey" FOREIGN KEY ("childID")
          REFERENCES "mainTable" ("id")
}

Of course, there's really little difference between doing things this
way and ordering by the SERIAL field and numbering them appropriately on
output, except that this above way is hideously more complex.

Another option would be to create a temporary table ordered correctly,
truncate the existing table, delete the sequence (or change the default
on the primary key), copy the data back, and then re-create the sequence
(or change default back to nextval) and then set nextval to MAX()+1.
This is rather ugly, however, since you're still forcing the database to
do relationships it doesn't know about, so you technically violate first
normal form by having a multi-valued field (it identifies uniqueness and
order).

--
Brandon Aiken
CS/IT Systems Engineer

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Sunday, September 24, 2006 7:31 PM
To: Bob Pawley
Cc: Ragnar; Postgresql
Subject: Re: [GENERAL] serial column

Bob Pawley <rjpawley@shaw.ca> writes:
> I am using the numbers to identify devices.
> If a device is deleted or replaced with another type of device I want
the
> numbering to still be sequential.

It sounds to me like you oughtn't be storing these numbers in the
database at all.  You just want to attach them at display time --- they
are certainly utterly meaningless as keys if they can change at any
moment.

            regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings