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 a(i integer PRIMARY KEY);
CREATE TABLE b(
j integer
, CONSTRAINT fkey_ij FOREIGN KEY (j) REFERENCES a (i)
ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE
CREATE TABLE
-- simple fix 1.: INSERT in order
INSERT INTO a(i) VALUES(2);
INSERT INTO b(j) VALUES(2);
INSERT 0 1
INSERT 0 1
-- simple fix 2: single command
WITH ins1 AS (
INSERT INTO b(j) VALUES(3)
)
INSERT INTO a(i) VALUES(3);
INSERT 0 1
-- ... without repeating input values
WITH ins1 AS (
INSERT INTO b(j) VALUES(4)
RETURNING j
)
INSERT INTO a(i)
SELECT j FROM ins1;
INSERT 0 1
TABLE b;
| j |
|---|
| 2 |
| 3 |
| 4 |
SELECT 3
BEGIN; -- original: fails
SET CONSTRAINTS ALL DEFERRED;
INSERT INTO b(j) VALUES(2);
INSERT INTO a(i) VALUES(2);
COMMIT;
BEGIN
SET CONSTRAINTS
INSERT 0 1
ERROR: duplicate key value violates unique constraint "a_pkey" DETAIL: Key (i)=(2) already exists.
ROLLBACK;
ROLLBACK
DROP TABLE b,a;
CREATE TABLE a(i integer PRIMARY KEY);
CREATE TABLE b(
j integer
, CONSTRAINT fkey_ij FOREIGN KEY (j) REFERENCES a (i)
ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE -- !!!
);
DROP TABLE
CREATE TABLE
CREATE TABLE
-- now original works, too
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
INSERT INTO b(j) VALUES(2);
INSERT INTO a(i) VALUES(2);
COMMIT;
BEGIN
SET CONSTRAINTS
INSERT 0 1
INSERT 0 1
COMMIT
TABLE b;
| j |
|---|
| 2 |
SELECT 1