Postgres 16

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 t3 (
    id bigserial primary key,
    val1 text,
    val2 text
);

CREATE TABLE


create unique index concurrently uidx
  on t3 (val1 
         /*collate unicode 
         text_ops 
         desc nulls last
         with operator(pg_catalog.=)*/) 
  include(val2)
  nulls not distinct 
  with(fillfactor=100)
  tablespace pg_default
  where id%2=1;

CREATE INDEX


alter table t3 
  add constraint ucns 
  unique using index uidx 
  deferrable initially deferred;

ERROR:  "uidx" is a partial index
LINE 2:   add constraint ucns 
              ^
DETAIL:  Cannot create a primary key or unique constraint using such an index.


insert into t3 values (1,'',''), (3,null,null), (5,null,null);

ERROR:  duplicate key value violates unique constraint "uidx"
DETAIL:  Key (val1)=(null) already exists.