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 sales (
id bigint PRIMARY KEY
, tahun int NOT NULL
);
INSERT INTO sales VALUES
(1, 1)
, (2, 2)
, (7, 55)
;
CREATE TABLE
INSERT 0 3
-- raises exception!
INSERT INTO sales(tahun) VALUES (66)
RETURNING *;
ERROR: null value in column "id" of relation "sales" violates not-null constraint DETAIL: Failing row contains (null, 66).
-- convert id to IDENTITY column
DO
$do$
BEGIN
EXECUTE format('ALTER TABLE sales ALTER id ADD GENERATED ALWAYS AS IDENTITY (RESTART %s)'
, (SELECT max(id) + 1 FROM sales));
END
$do$;
DO
-- now it works
INSERT INTO sales(tahun) VALUES (66)
RETURNING *;
| id | tahun |
|---|---|
| 8 | 66 |
INSERT 0 1