add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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