Re: Sequences without blank holes - Mailing list pgsql-general

MaRcElO PeReIrA wrote:
> Hi guys,
>
> I have been using the following table (short, short,
> short version):
>
> CREATE TABLE products (
>     prod_id SERIAL,
>     description TEXT
> );
>
> BUT, there is lots os users blaming because the holes
> in the [prod_id] field (and, of course it as supposed
> to be like this, because sequences only increase their
> values and never rollback).
>
> So, a real SELECT statement would return:
>
> $ select * from products;
> prod_id |     description
> --------+---------------------
>      1  | S470DXBLM
>     12  | S470DXABM
>     33  | RG250DX
> --------+---------------------
> (3 rows)
>
> and it is ok to me, but not to the users.
>
> How can I assure a ''sequence WITHOUT holes''?

Whatever you do, continue to use a serial field.

Now, it sounds like the users are just complaining about asthetics or
something, because both you and I know that it will work fine with the
way it is currently set up. But, you want to keep the users happy...

So, set up the table like this:

  rec_id | prod_id | description
--------+---------------------
      1  |      1  | S470DXBLM
     12  |      2  | S470DXABM
     33  |      3  | RG250DX
--------+---------------------

rec_id would be your primary key. prod_id would be what you show to the
users. Now, say someone deletes the middle record - you end up with:

  rec_id | prod_id | description
--------+---------------------
      1  |      1  | S470DXBLM
     33  |      3  | RG250DX
--------+---------------------

So, you still have a gap to be filled when the next record is added.
What you will want to do is keep the "prod_id" of the record that was
deleted - store it in a "holding" table of deleted "prod_id" records:

  rec_id | prod_id
--------+---------
      1  |      2
--------+---------

Then, when you add a record to your main table, select for the lowest
prod_id from the deleted items table, and use that prod_id to insert
into your main table, and delete the record from the deleted items
table. So, your main table would then look something like this:

  rec_id | prod_id | description
--------+---------------------
      1  |      1  | S470DXBLM
     33  |      3  | RG250DX
     34  |      2  | XYZ123
--------+---------------------

If the deleted items table is empty when you go to insert, select the
largest prod_id from the main table, and increment it by one before
adding the record.

Now, you only show "prod_id" and "description" to the users, and voila,
no more gaps (at least as far as they are concerned - rec_id will still,
of course, have gaps, as it should).

Hope this helps...

Andrew Ayers
Phoenix, Arizona

-- CONFIDENTIALITY NOTICE --

This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain
informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the
intendedaddressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use,
copy,disclose or distribute to anyone the message or any information contained in the message. If you have received
thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you.