Postgres 10

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).