Re: PG Admin - Mailing list pgsql-general

> Your missing the point.
>
> I am creating a design system for industrial control.
>
> The control devices need to be numbered. The numbers need to be sequential.
> If the user deletes a device the numbers need to regenerate to again become
> sequential and gapless.

As I understand it, it really doesn't matter if the gap-less sequence is stored in the DB!! All you want is when you SELECT, the result should have gap-less sequennce of IDs associted to the device name, just as pgAdmid GUI is doing. If that is the case, then I think I have a solution.

After a lot of thinking, and failed experiments with generate_series(), CREATE AGGREGATE, etc etc, a simple solution dawned upon me (yes, closing your laptop and think-walking in the open helps). Can the following query help you?

postgres=# select (select count(*) from device i where i.name < o.name) +1 as ID, name from device o;
 id |  name
----+---------
  1 | device0
  2 | device1
  3 | device2
  4 | device3
  5 | device4
  6 | device5
  7 | device6
  8 | device7
  9 | device8
 10 | device9
(10 rows)

postgres=#

In case you do not have unique device names, you can create a serial column, and use that column in the count(*) subquery instead of the name. This looks like a ROWNUM pseudo-column in ORACLE's query results.

Following is a complete test case:

postgres=# create table device( id serial, name varchar(10));
NOTICE:  CREATE TABLE will create implicit sequence "device_id_seq" for serial column " device.id"
CREATE TABLE
postgres=# insert into device(name) select 'device' || a from generate_series(0,9) as s(a);
INSERT 0 10
postgres=# delete from device where mod(id,2) = 0;
DELETE 5
postgres=# insert into device(name) select 'device' || a from generate_series(0,9) as s(a);
INSERT 0 10
postgres=# delete from device where id >= 10 and mod(id,2) <> 0;
DELETE 5
postgres=# insert into device(name) select 'device' || a from generate_series(0,
9) as s(a);
INSERT 0 10
postgres=# select * from device;
 id |  name
----+---------
  1 | device0
  3 | device2
  5 | device4
  7 | device6
  9 | device8
 12 | device1
 14 | device3
 16 | device5
 18 | device7
 20 | device9
 21 | device0
 22 | device1
 23 | device2
 24 | device3
 25 | device4
 26 | device5
 27 | device6
 28 | device7
 29 | device8
 30 | device9
(20 rows)

postgres=# select (select count(*) from device i where i.id < o.id) + 1 as rownum, id, name from device o;
 rownum | id |  name
--------+----+---------
      1 |  1 | device0
      2 |  3 | device2
      3 |  5 | device4
      4 |  7 | device6
      5 |  9 | device8
      6 | 12 | device1
      7 | 14 | device3
      8 | 16 | device5
      9 | 18 | device7
     10 | 20 | device9
     11 | 21 | device0
     12 | 22 | device1
     13 | 23 | device2
     14 | 24 | device3
     15 | 25 | device4
     16 | 26 | device5
     17 | 27 | device6
     18 | 28 | device7
     19 | 29 | device8
     20 | 30 | device9
(20 rows)

postgres=#

Hope this helps.