Postgres 15

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 test(
  id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
  NAME text not null
);
insert into test(name)values('a')returning*;

CREATE TABLE

id
name

1
a

INSERT 0 1

DELETE FROM test;
select setval(pg_get_serial_sequence('test', 'id'), 1);
insert into test(name)values('a')returning*;

DELETE 1

setval

1

SELECT 1

id
name

2
a

INSERT 0 1

DELETE FROM test;
select setval(pg_get_serial_sequence('test', 'id'), 1,false);
insert into test(name)values('a')returning*;

DELETE 1

setval

1

SELECT 1

id
name

1
a

INSERT 0 1

DELETE FROM test;
ALTER TABLE test ALTER COLUMN id RESTART;
insert into test(name)values('a')returning*;

DELETE 1

ALTER TABLE

id
name

1
a

INSERT 0 1

truncate test restart identity;
insert into test(name)values('a')returning*;

TRUNCATE TABLE

id
name

1
a

INSERT 0 1

create sequence sq2 
  start 22
  owned by test.id;
create sequence sq3 
  start 33
  owned by test.id;
alter table test 
  alter column id 
  drop identity;
select pg_get_serial_sequence('test', 'id');

CREATE SEQUENCE

CREATE SEQUENCE

ALTER TABLE

pg_get_serial_sequence

public.sq2

SELECT 1

alter table test 
  alter column id 
  drop identity;

ERROR:  column "id" of relation "test" is not an identity column

drop sequence sq2;
select pg_get_serial_sequence('test', 'id');

DROP SEQUENCE

pg_get_serial_sequence

public.sq3

SELECT 1

alter table test 
  alter column id 
  add generated always as identity (start 11);
select pg_get_serial_sequence('test', 'id');

ALTER TABLE

pg_get_serial_sequence

public.sq3

SELECT 1

truncate test restart identity;
insert into test(name)values('a')returning*;

TRUNCATE TABLE

id
name

11
a

INSERT 0 1