serial column

On Sun, 2006-09-24 at 13:50 -0700, Bob Pawley wrote:

I need to develop a serial column that always starts at 1 and is
sequential even after deletes.

Any ideas???

Did you try the:

create table tbl
(
id SERIAL
);

or even with primary key...

create table tbl
(
id SERIAL primary key
);

--
Regards,
Gevik Babakhani

Yes

But the only way of insuring that the serial starts at 1 and is sequential
is to recreate the table.

I've tried creating and dropping the table but this generates other issues
which I haven't been able to resolve.

Bob

----- Original Message -----
From: "Gevik Babakhani" <pgdev@xs4all.nl>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Postgresql" <pgsql-general@postgresql.org>
Sent: Sunday, September 24, 2006 2:00 PM
Subject: Re: [GENERAL] serial column

On sun, 2006-09-24 at 13:50 -0700, Bob Pawley wrote:

I need to develop a serial column that always starts at 1 and is
sequential even after deletes.

what exactly do you mean?

say you have rows where your
columns has values 1,2,3 and 4.

you now delete the row where
the value is 2.

what do you want to happen?

a) the rows with values 3 and 4
are changed tocontain 2 and 3 ?

b) the next 2 values to be inserted
to be 2 and then 5 ?

c) something else ?

gnari

Choice a.

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.

Bob

----- Original Message -----
From: "Ragnar" <gnari@hive.is>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Postgresql" <pgsql-general@postgresql.org>
Sent: Sunday, September 24, 2006 2:24 PM
Subject: Re: [GENERAL] serial column

The SERIAL is always sequential. SERIAL internally creates a SEQUENCE
and *binds* it to your table. even if you delete a record and insert a
new one , the sequence will continue to increment. however there will be
gaps between the values.

Isn't this the behavior you expect?

It's the behavior I expect - but the gaps aren't acceptable.

Bob

----- Original Message -----
From: "Gevik Babakhani" <pgdev@xs4all.nl>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Postgresql" <pgsql-general@postgresql.org>
Sent: Sunday, September 24, 2006 2:42 PM
Subject: Re: [GENERAL] serial column

On sun, 2006-09-24 at 14:29 -0700, Bob Pawley wrote:

Choice a.

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.

have you tried to implement ths using
triggers?

gnari

On Sun, 2006-09-24 at 14:49 -0700, Bob Pawley wrote:

It's the behavior I expect - but the gaps aren't acceptable.

Bob

Then using the SERIAL or SEQUENCE won't do you any good.

A possible solution for this would be to regenerate the entire column's
values every time a record gets deleted starting form 1. but then again
this would be very slow if you have a very large table

Another solution would be to have two triggers, one for delete and one
for insert plus an extra *reserve* table to reserve the deleted value.
The delete trigger would save the *deleted* values in the reserve table
and when a new record is inserted the insert trigger first would check
the reserve table for deleted values (that are stored by the delete
trigger) if a value exist then it would use that value or increment that
last value.

However if you want to use a *no gap* sequence as a primary key, you
should be aware that you will destroy the integrity of you data.

--
Regards,
Gevik Babakhani

Do you have a for instance??

Bob
----- Original Message -----
From: "Ragnar" <gnari@hive.is>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Postgresql" <pgsql-general@postgresql.org>
Sent: Sunday, September 24, 2006 3:10 PM
Subject: Re: [GENERAL] serial column

"A possible solution for this would be to regenerate the entire column's
values every time a record gets deleted starting form 1. but then again
this would be very slow if you have a very large table"

I don't anticipate the table to be more than a few hundred rows - certainly
fewer than 1,000.

Could you point to some documentation for regenerating a column's values
other than manual??

Bob

----- Original Message -----
From: "Gevik Babakhani" <pgdev@xs4all.nl>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Postgresql" <pgsql-general@postgresql.org>
Sent: Sunday, September 24, 2006 3:19 PM
Subject: Re: [GENERAL] serial column

On Sun, 2006-09-24 at 15:29 -0700, Bob Pawley wrote:

"A possible solution for this would be to regenerate the entire column's
values every time a record gets deleted starting form 1. but then again
this would be very slow if you have a very large table"

I don't anticipate the table to be more than a few hundred rows - certainly
fewer than 1,000.

Could you point to some documentation for regenerating a column's values
other than manual??

Bob

I am afraid there is no built-in way to do that.
perhaps you could create a function that:
step1: creates a sequence (with random name)....
step2: update table set field=netval('random_seq_name');
step3: drop sequence...

--
Regards,
Gevik Babakhani

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

Thanks

I'll give that a try.

Bob
----- Original Message -----
From: "Gevik Babakhani" <pgdev@xs4all.nl>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Postgresql" <pgsql-general@postgresql.org>
Sent: Sunday, September 24, 2006 4:13 PM
Subject: Re: [GENERAL] serial column

On Sunday 24 September 2006 02:29 pm, Bob Pawley wrote:

Choice a.

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.

Bob

Do you have some other way of tracking a device? I am just trying to figure
out how you know which device number 2 (as an example) you are looking at. I
am assuming these devices exist as actual entities. So are these numbers
applied to the actual device and if so are you going to be constantly
renumbering them?

--
Adrian Klaver
aklaver@comcast.net

To some degree I don't care about the actual number other than roughly
following the device ID.

At some point later in the design the numbers will be updated to project
numbers and then frozen.

Bob

----- Original Message -----
From: "Adrian Klaver" <aklaver@comcast.net>
To: <pgsql-general@postgresql.org>
Cc: "Bob Pawley" <rjpawley@shaw.ca>
Sent: Sunday, September 24, 2006 6:11 PM
Subject: Re: [GENERAL] serial column

The numbering system is more complex than just assigning a number. It
invloves about thirty procedures which I have put together and find that it
works well.

I would like to keep the numbering as a database system which will be
possible if I can figure out a way of generating sequential numbers without
possibility of a gap.

Perhaps a manually built table is the answer??

Bob

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Ragnar" <gnari@hive.is>; "Postgresql" <pgsql-general@postgresql.org>
Sent: Sunday, September 24, 2006 4:30 PM
Subject: Re: [GENERAL] serial column

You might want to take a look at-
http://www.varlena.com/GeneralBits/
The procedure as shown does not account for renumbering after a delete, but it
might serve as a starting point.

On Sunday 24 September 2006 07:03 pm, Bob Pawley wrote:

The numbering system is more complex than just assigning a number. It
invloves about thirty procedures which I have put together and find that it
works well.

I would like to keep the numbering as a database system which will be
possible if I can figure out a way of generating sequential numbers without
possibility of a gap.

Perhaps a manually built table is the answer??

Bob

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Ragnar" <gnari@hive.is>; "Postgresql" <pgsql-general@postgresql.org>
Sent: Sunday, September 24, 2006 4:30 PM
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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--
Adrian Klaver
aklaver@comcast.net

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

Actually, I am not trying to "force keys" nor, I don't beleive, am I trying
to force an hierarchal structure within the database.

The numbers I want to assign to devices are nothing more than merely another
attribute of the device - perhaps akin to a number in a street address. The
problem, from my viewpoint, is that this attribute needs to always start at
1 and be sequential without gaps.

(I am however, partly relying on an hierarchal order within the database.
When I assign numbers to devices, the lowest number is assigned,
sequentially, to the device that has the lowest serial ID number. )

Thanks for your comments - everything helps at my stage.

Bob Pawley

----- Original Message -----
From: "Brandon Aiken" <BAiken@winemantech.com>
To: <pgsql-general@postgresql.org>
Sent: Monday, September 25, 2006 7:47 AM
Subject: Re: [GENERAL] serial column

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq