PostgreSQL: is it possible to provide custom name for PRIMARY KEY or UNIQUE?

Question

When I write:

 CREATE TABLE accounts (

     username varchar(64) PRIMARY KEY,

I get primary key named:

accounts_pkey

Is it possible to assign my own custom name, for instance "accounts_primary_key"?

Same story about UNIQUE.

I couldn't find it in PostgreSQL documentation.

Thanks in advance.

Cf

Answer 1

The trick is the CONSTRAINT part in the column_constraint section of CREATE TABLE. Example:

> create table x(xx text constraint xxxx primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "xxxx" for table "x"
CREATE TABLE

This works for all kind of constraints, including PRIMARY KEY and UNIQUE.

See the docs of CREATE TABLE for details.

Operant

I suppose I need to study Postgres docs more thoroughly. :-) Thanks! – Cf

Does this work for EXCLUDE constraints? I can't figure out how to name those explicitly as part of the CREATE TABLE statement. – Carmon

@bjmc: Reading the actual docs EXCLUDE is a table-constraint not a column constraint. So the syntax is create table x(xx text, constraint xxxx exclude ...); -- note the , between the column definition and the table-constraint. – Operant