13

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.

Maciej Ziarko
  • 11,494
  • 13
  • 48
  • 69

1 Answers1

19

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.

A.H.
  • 63,967
  • 15
  • 92
  • 126
  • 1
    I suppose I need to study Postgres docs more thoroughly. :-) Thanks! – Maciej Ziarko Dec 29 '11 at 23:34
  • Does this work for `EXCLUDE` constraints? I can't figure out how to name those explicitly as part of the `CREATE TABLE` statement. – bjmc Feb 26 '20 at 11:40
  • @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. – A.H. Feb 28 '20 at 19:57