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 tbl (
id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
, foo text DEFAULT 'bar'
, CONSTRAINT foo_bar CHECK (foo IN ('bar', 'bar1'))
);
INSERT INTO tbl(foo) VALUES (DEFAULT), ('bar1');
CREATE TABLE
INSERT 0 2
TABLE tbl;
| id | foo |
|---|---|
| 1 | bar |
| 2 | bar1 |
SELECT 2
CREATE TEMP TABLE t_tbl (LIKE tbl INCLUDING ALL);
INSERT INTO t_tbl TABLE tbl;
SELECT setval(pg_get_serial_sequence('t_tbl', 'id'), max(id)) FROM tbl;
CREATE TABLE
INSERT 0 2
| setval |
|---|
| 2 |
SELECT 1
INSERT INTO t_tbl DEFAULT VALUES;
TABLE t_tbl;
INSERT 0 1
| id | foo |
|---|---|
| 1 | bar |
| 2 | bar1 |
| 3 | bar |
SELECT 3
INSERT INTO t_tbl (foo) VALUES ('bar2'); -- would violate the copied (!) CHECK constraint
ERROR: new row for relation "t_tbl" violates check constraint "foo_bar" DETAIL: Failing row contains (4, bar2).