Postgres 14

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);
INSERT INTO tbl VALUES (1);

CREATE TABLE

INSERT 0 1

CREATE TYPE vehicle_type AS enum ('car', 'truck', 'motorcycle');

-- add column with default
ALTER TABLE tbl ADD COLUMN v_type vehicle_type NOT NULL DEFAULT 'car';

CREATE TYPE

ALTER TABLE

INSERT INTO tbl VALUES (2, 'truck');
INSERT INTO tbl(id) VALUES (3);

TABLE tbl;

INSERT 0 1

INSERT 0 1

id
v_type

1
car

2
truck

3
car

SELECT 3

-- alternative: "char"
ALTER TABLE tbl
  ADD column v_type2 "char" NOT NULL DEFAULT 'c'
, ADD CONSTRAINT v_type_allowed CHECK (v_type2 IN ('c', 't', 'm'));

ALTER TABLE

INSERT INTO tbl VALUES (4, 'motorcycle', 'm');
INSERT INTO tbl(id) VALUES (5);

TABLE tbl;

INSERT 0 1

INSERT 0 1

id
v_type
v_type2

1
car
c

2
truck
c

3
car
c

4
motorcycle
m

5
car
c

SELECT 5