PostgreSQL: default constraint names

Question

When creating a table in PostgreSQL, default constraint names will assigned if not provided:

CREATE TABLE example (     a integer,     b integer,     UNIQUE (a, b) ); 

But using ALTER TABLE to add a constraint it seems a name is mandatory:

ALTER TABLE example ADD CONSTRAINT my_explicit_constraint_name UNIQUE (a, b); 

This has caused some naming inconsistencies on projects I've worked on, and prompts the following questions:

1. Is there a simple way to add a constraint to an extant table with the name it would have received if added during table creation?

2. If not, should default names be avoided altogether to prevent inconsistencies?

Answer 1

The standard names for indexes in PostgreSQL are:

{tablename}_{columnname(s)}_{suffix}

where the suffix is one of the following:

• pkey for a Primary Key constraint
• key for a Unique constraint
• excl for an Exclusion constraint
• idx for any other kind of index
• fkey for a Foreign key
• check for a Check constraint

Standard suffix for sequences is

• seq for all sequences

Proof of your UNIQUE-constraint:

NOTICE: CREATE TABLE / UNIQUE will create implicit index "example_a_b_key" for table "example"