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 fruits(
  id serial PRIMARY KEY
, name varchar NOT NULL UNIQUE -- !
);

CREATE TABLE

INSERT INTO fruits(name)    VALUES('Orange');
INSERT INTO fruits(id,name) VALUES(DEFAULT,'Apple');

TABLE fruits;

INSERT 0 1

INSERT 0 1

id
name

1
Orange

2
Apple

SELECT 2

-- wrong
DELETE FROM fruits;
INSERT INTO fruits(name) VALUES('Kiwi');

TABLE fruits;

DELETE 2

INSERT 0 1

id
name

3
Kiwi

SELECT 1

-- simple 1
DELETE FROM fruits;
SELECT setval('fruits_id_seq', 1, false);  -- !
INSERT INTO fruits(name) VALUES('Kiwi');

TABLE fruits;

DELETE 1

setval

1

SELECT 1

INSERT 0 1

id
name

1
Kiwi

SELECT 1

-- simple 2
DELETE FROM fruits;
ALTER SEQUENCE fruits_id_seq RESTART WITH 1; -- !
INSERT INTO fruits(name) VALUES('Kiwi');

TABLE fruits;

DELETE 1

ALTER SEQUENCE

INSERT 0 1

id
name

1
Kiwi

SELECT 1

-- safe
DELETE FROM fruits;
SELECT setval(pg_get_serial_sequence('fruits', 'id')
            , COALESCE(max(id) + 1, 1)
            , false)
FROM   fruits;
INSERT INTO fruits(name) VALUES('Kiwi');

TABLE fruits;

DELETE 1

setval

1

SELECT 1

INSERT 0 1

id
name

1
Kiwi

SELECT 1