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 ?
