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 demo (
id bigint generated always as identity primary key,
value int not null,
constraint unique_value exclude using hash(value with =)
);
CREATE TABLE
insert into demo (value)
select unnest(array_agg(i))
from generate_series(1,(2^16)::int4) i
group by hashint4(i)
having count(*) > 1
limit 2;
INSERT 0 2
select
opc.opcname as operator_class_name,
amproc.amproc as access_method_procedure
from
pg_class t
join pg_index ix on t.oid = ix.indrelid
join pg_class i on i.oid = ix.indexrelid
join pg_attribute a on a.attrelid = t.oid and a.attnum = any(ix.indkey)
join pg_opclass opc on opc.oid = ix.indclass[0]
join pg_amproc amproc on amproc.amprocfamily = opc.opcfamily
where
i.relname = 'unique_value' and amproc.amprocnum = 1; -- HASHSTANDARD_PROC = 1
| operator_class_name | access_method_procedure |
|---|---|
| int4_ops | hashint8 |
| int4_ops | hashint2 |
| int4_ops | hashint4 |
SELECT 3
select *, hashint4(value), hashint8(value) from demo;
| id | value | hashint4 | hashint8 |
|---|---|---|---|
| 1 | 13362 | -178094052 | -178094052 |
| 2 | 41950 | -178094052 | -178094052 |
SELECT 2
insert into demo (value)
select unnest(array_agg(i))
from generate_series(1,(2^16)::int4) i
group by hashint4(i)
having count(*) > 1
limit 1;
ERROR: conflicting key value violates exclusion constraint "unique_value" DETAIL: Key (value)=(13362) conflicts with existing key (value)=(13362).
insert into demo (value)
select unnest(array_agg(i))
from generate_series(1,(2^16)::int4) i
group by hashint4(i)
having count(*) > 1
limit 1
on conflict (value) do nothing;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
insert into demo (value)
select unnest(array_agg(i))
from generate_series(1,(2^16)::int4) i
group by hashint4(i)
having count(*) > 1
limit 1
on conflict on constraint unique_value do nothing;
INSERT 0 0