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 TYPE address_object AS (
    state char(2),
    zip char(10)
);
CREATE TYPE embedded_address AS (
    state char(2),
    zip char(10),
    preaddr address_object  
);
CREATE TYPE address_value AS (
    state char(2),
    zip char(10)
);
CREATE TYPE person AS (
    name char(20),
    age numeric,
    address address_object
);
CREATE TABLE customerval (
    custno numeric,
    addr address_value
);
CREATE TABLE extaddr OF address_object;
CREATE TABLE customerobj (
    custno numeric,
    addr address_object  
);
CREATE TABLE embextaddr OF embedded_address;
CREATE TABLE emp (
    emp_id numeric,
    emp_info person
);
CREATE TABLE empref (
    emp_id numeric,
    emp_info person  
);
CREATE TABLE extper OF person;

CREATE TYPE

CREATE TYPE

CREATE TYPE

CREATE TYPE

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

-- Insert data
INSERT INTO customerval VALUES (100, ROW('CA', '94065')::address_value) RETURNING *;

custno
addr

100
(CA,"94065     ")

INSERT 0 1

INSERT INTO extaddr VALUES ('CA', '94065'),
                           ('CA', '98765'),
                           ('CA', '95117')
RETURNING *;

state
zip

CA
94065

CA
98765

CA
95117

INSERT 0 3

INSERT INTO embextaddr VALUES ('CA','95117', NULL) RETURNING *;

state
zip
preaddr

CA
95117
null

INSERT 0 1

create table test(a int, b int); 
insert into test values (1) returning *;--too few values is fine and tolerated

CREATE TABLE

a
b

1
null

INSERT 0 1

insert into test values (1,2,3);--only too many values throws an error

ERROR:  INSERT has more expressions than target columns
LINE 1: insert into test values (1,2,3);--only too many values throw...
                                     ^

--unpack
INSERT INTO extaddr VALUES ((row('CA', '94065')).*),
                           ((row('CA', '98765')).*),
                           ((row('CA', '95117')).*)
RETURNING *;

state
zip

CA
94065

CA
98765

CA
95117

INSERT 0 3