Re: Missing constraint when duplicated unique index ?

Em qui., 13 de mar. de 2025 às 09:17, Álvaro Herrera <
alvherre(at)alvh(dot)no-ip(dot)org> escreveu:

> I tried this example all the way back to pg 9.5, and they all end up
> with a single constraint and a single index -- namely, the test_pk
> constraint and corresponding index. There's no second index and no
> second constraint. test_uq goes ignored.
>

PostgreSQL 17.0 (Ubuntu 17.0-1.pgdg22.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
test=# set client_min_messages = debug; -- only to get toast table name

test=# CREATE TABLE table_test (
foo text NOT NULL,
CONSTRAINT test_pk PRIMARY KEY (foo),
CONSTRAINT test_uq UNIQUE (foo)
);
building index "pg_toast_29368336_index" on table "pg_toast_29368336"
serially
CREATE TABLE / PRIMARY KEY will create implicit index "test_pk" for table
"table_test"
building index "test_pk" on table "table_test" serially

test=# select relname, relkind from pg_class where relname ~
'pg_toast_29368336|pg_toast_29368336_index|test_pk|table_test';
relname | relkind
-------------------------+---------
pg_toast_29368336 | t
pg_toast_29368336_index | i
table_test | r
test_pk | i
(4 rows)

test=# select conname, contype, conrelid::regclass from pg_constraint where
conrelid::regclass::text ~ 'table_test|pg_toast_29368336';
conname | contype | conrelid
---------+---------+------------
test_pk | p | table_test
(1 row)

test=# select indexrelid::regclass, indrelid::regclass, indisunique from
pg_index
where indrelid::regclass::text ~ 'table_test|pg_toast_29368336'
indexrelid | indrelid | indisunique
----------------------------------+----------------------------+-------------
pg_toast.pg_toast_29368336_index | pg_toast.pg_toast_29368336 | t
test_pk | table_test | t
(2 rows)

I know Table and Toast are separate relations but there are two indexes, so
the question is,
will there be any additional check when insert/update because there exists
a second unique index ?
Is it intentional to have this unique index without a related constraint ?

regards
Marcos