Re: Unique constraint and unique index

On Thu, Aug 22, 2013 at 2:46 AM, Ivan Radovanovic <radovanovic@gmail.com> wrote:
> Just to verify:
> - when unique index is created row is added only to pg_index table but not
> to pg_constraint table (although in fact that index is behaving like
> constraint on table)
Yep.
postgres=# create table foo (a int);
CREATE TABLE
postgres=# select count(*) from pg_index;
 count
-------
   112
(1 row)
postgres=# select count(*) from pg_constraint;
 count
-------
     2
(1 row)
postgres=# create unique index aai on foo(a); -- unique index
CREATE INDEX
postgres=# select count(*) from pg_index;
 count
-------
   113
(1 row)
postgres=# select count(*) from pg_constraint;
 count
-------
     2
(1 row)
> - when unique constraint is created using appropriate syntax rows are added
> to tables pg_constraint and pg_index (pg_constraint with type 'u' and
> referring to index with indisunique set to true)
Yep. Following last example:
postgres=# alter table foo add unique using index aai;
ALTER TABLE
postgres=# select count(*) from pg_index;
 count
-------
   113
(1 row)
postgres=# select count(*) from pg_constraint;
 count
-------
     3
(1 row)
A unique constraint refers to a unique index using conindid of pg_constraint.

> Is that correct?
Yep.
--
Michael