Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE vendors (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL);

insert into vendors (name) values ('vendor1'),('vendor2'),('vendor3') returning *;

CREATE TABLE orders (
id SERIAL PRIMARY KEY,
vendor_id INT REFERENCES vendors(id),
description text);

CREATE VIEW v_orders AS 
SELECT *,row_number()over(partition by vendor_id order by id) AS order_number
FROM orders;

CREATE TABLE




id
name


1
vendor1

2
vendor2

3
vendor3



INSERT 0 3



CREATE TABLE



CREATE VIEW


insert into orders (vendor_id,description) values
(1,'v1order1'),
(1,'v1order2'),
(2,'v2order1'),
(3,'v3order1'),
(3,'v3order2');

select vendor_id,order_number,description,id from v_orders;

INSERT 0 5




vendor_id
order_number
description
id


1
1
v1order1
1

1
2
v1order2
2

2
1
v2order1
3

3
1
v3order1
4

3
2
v3order2
5



SELECT 5


delete from orders where id in (1,5);
update orders set vendor_id=1 where id=4;
--now vendor1's first order is number 2
--vendor3 has only order 1 and 3, a gap on number 2

DELETE 2



UPDATE 1


insert into orders (vendor_id,description) values
(1,'v1order3'),
(2,'v2order2'),
(3,'v3order3');

select vendor_id,order_number,description,id from v_orders;
--no gaps or inconsistencies, 
--because numbering is always fresh, based on the current state

INSERT 0 3




vendor_id
order_number
description
id


1
1
v1order2
2

1
2
v3order1
4

1
3
v1order3
6

2
1
v2order1
3

2
2
v2order2
7

3
1
v3order3
8



SELECT 6

vendor_id	order_number	description	id
1	1	v1order2	2
1	2	v3order1	4
1	3	v1order3	6
2	1	v2order1	3
2	2	v2order2	7
3	1	v3order3	8