Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t (
  id bigserial NOT NULL
, date date
, PRIMARY KEY(id, date)
) PARTITION BY RANGE (date);

CREATE TABLE t_2020 PARTITION OF t FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');
CREATE TABLE t_2021 PARTITION OF t FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');

CREATE TABLE

CREATE TABLE

CREATE TABLE

-- normal operation
INSERT INTO t(date) VALUES ('2020-01-02') RETURNING *;
INSERT INTO t(date) VALUES ('2021-01-02') RETURNING *;

id
date

1
2020-01-02

INSERT 0 1

id
date

2
2021-01-02

INSERT 0 1

-- but no guaaratees! manual insert overrides:
INSERT INTO t(id, date) VALUES (1, '2021-01-02');

-- now we have mulitple rows with id = 1:
TABLE t;

INSERT 0 1

id
date

1
2020-01-02

2
2021-01-02

1
2021-01-02

SELECT 3