> 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.
Best regards,>
> 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.
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com
