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),
   UNIQUE(a,b,c)--this is a natural consequence of the previous two UNIQUEs but
                --it's required for foreign keys to link complete combinations
);

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

CREATE TABLE



CREATE TABLE


INSERT INTO available_combinations  
SELECT DISTINCT a,b,c
FROM (VALUES
(1, 2, 22),
(1, 2, 22),
(1, 2, 22),
(1, 3, 34),
(2, 3, 26),
(2, 3, 26)) as v(a,b,c)
RETURNING *;

INSERT INTO test_table (a, b, c) VALUES
(1, 2, 22),
(1, 2, 22),
(1, 2, 22),
(1, 3, 34),
(2, 3, 26),
(2, 3, 26)
RETURNING *;


a
b
c


2
3
26

1
3
34

1
2
22



INSERT 0 3




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



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


2
3
26

1
3
34

1
2
22

7
8
33

7
9
35

8
7
36

8
8
37



SELECT 7


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

select * from test_table;

INSERT 0 10




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