PostgreSQL: default constraint names

Question

When creating a table in PostgreSQL, default constraint names are assigned if they are not specified:

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

But using ALTER TABLE to add a constraint, it seems that the name is required:

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

This caused some inconsistencies in the names of the projects that I worked on, and offers the following questions:

• Is there an easy way to add a constraint to an existing table with the name that it would get if it were added when the table was created?

• If not, should the default names be excluded at all to prevent inconsistencies?

Answer 1

the manual is pretty clear about this ("tableconstraint: this form adds a new constraint to the table using the same syntax as CREATE TABLE.")

So you can just run:

 ALTER TABLE example ADD UNIQUE (a, b);

Answer 2

The standard index names in PostgreSQL are:

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

where the suffix is ​​one of the following:

• pkey to restrict the primary key
• key for unique constraint
• excl to limit exclusion
• idx for any other type of index
• fkey for foreign key
• check to limit validation

Standard sequence suffix

• seq for all sequences

Proof of your only limitation:

NOTICE: CREATE A TABLE / UNIQUE create an implicit index "example_a_b_key" for the table "example"