Postgres 16

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

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE available_combinations (
   a integer NOT NULL,
   b integer NOT NULL,
   c integer NOT NULL UNIQUE,
   UNIQUE(a,b)                      );

CREATE TABLE test_table (
   pkey integer generated by default as identity PRIMARY KEY,
   c integer REFERENCES available_combinations (c) NOT NULL  );

CREATE VIEW v_test_table AS SELECT pkey,a,b,c
FROM test_table t NATURAL JOIN available_combinations a;

CREATE TABLE



CREATE TABLE



CREATE VIEW


INSERT INTO available_combinations  
VALUES
(1, 2, 22),
(1, 2, 22),
(1, 2, 22),
(1, 3, 34),
(2, 3, 26),
(2, 3, 26)
on conflict do nothing
returning *;

INSERT INTO test_table (c) VALUES
(22),
(22),
(22),
(34),
(26),
(26)
RETURNING *;


a
b
c


1
2
22

1
3
34

2
3
26



INSERT 0 3




pkey
c


1
22

2
22

3
22

4
34

5
26

6
26



INSERT 0 6


--should be rejected
INSERT INTO available_combinations (a, b, c) VALUES
(1,2,23)
returning *;

ERROR:  duplicate key value violates unique constraint "available_combinations_a_b_key"
DETAIL:  Key (a, b)=(1, 2) already exists.


--should be rejected, because of pre-existing (a,b) as well as c
INSERT INTO available_combinations (a, b, c) VALUES
(2,3,34)
returning *;

ERROR:  duplicate key value violates unique constraint "available_combinations_c_key"
DETAIL:  Key (c)=(34) already exists.


--should be rejected
INSERT INTO available_combinations (a, b, c) VALUES
(7,9,22)
returning *;

ERROR:  duplicate key value violates unique constraint "available_combinations_c_key"
DETAIL:  Key (c)=(22) already exists.


INSERT INTO available_combinations (a, b, c) VALUES
(7,8,33),
(7,9,35),
(8,7,36),
(8,8,37);

select * from available_combinations;

INSERT 0 4




a
b
c


1
2
22

1
3
34

2
3
26

7
8
33

7
9
35

8
7
36

8
8
37



SELECT 7


INSERT INTO test_table (c) VALUES
(33),
(33),
(35),
(35),
(35),
(35),
(36),
(36),
(37),
(37);

select * from test_table;

INSERT 0 10




pkey
c


1
22

2
22

3
22

4
34

5
26

6
26

7
33

8
33

9
35

10
35

11
35

12
35

13
36

14
36

15
37

16
37



SELECT 16


select * from v_test_table;


pkey
a
b
c


1
1
2
22

2
1
2
22

3
1
2
22

4
1
3
34

5
2
3
26

6
2
3
26

7
7
8
33

8
7
8
33

9
7
9
35

10
7
9
35

11
7
9
35

12
7
9
35

13
8
7
36

14
8
7
36

15
8
8
37

16
8
8
37



SELECT 16

pkey	a	b	c
1	1	2	22
2	1	2	22
3	1	2	22
4	1	3	34
5	2	3	26
6	2	3	26
7	7	8	33
8	7	8	33
9	7	9	35
10	7	9	35
11	7	9	35
12	7	9	35
13	8	7	36
14	8	7	36
15	8	8	37
16	8	8	37

pkey	a	b	c
1	1	2	22
2	1	2	22
3	1	2	22
4	1	3	34
5	2	3	26
6	2	3	26
7	7	8	33
8	7	8	33
9	7	9	35
10	7	9	35
11	7	9	35
12	7	9	35
13	8	7	36
14	8	7	36
15	8	8	37
16	8	8	37

pkey	a	b	c
1	1	2	22
2	1	2	22
3	1	2	22
4	1	3	34
5	2	3	26
6	2	3	26
7	7	8	33
8	7	8	33
9	7	9	35
10	7	9	35
11	7	9	35
12	7	9	35
13	8	7	36
14	8	7	36
15	8	8	37
16	8	8	37