MacLochlainns Weblog

The Postgres 11 database documentation says that it supports naming constraints. While you can create a table with named constraints inside the CREATE TABLE statement, the names are not assigned to the not null check constraint.

Here’s a quick test case in three steps:

1. Drop a demo table if it exists:

   DROP TABLE IF EXISTS demo;

   DROP TABLE IF EXISTS demo;

2. Drop a demo table if it exists:

   CREATE TABLE demo ( demo_id SERIAL , demo_text VARCHAR(20) CONSTRAINT nn_demo_1 NOT NULL );

   CREATE TABLE demo ( demo_id SERIAL , demo_text VARCHAR(20) CONSTRAINT nn_demo_1 NOT NULL );

3. Create a demo table if it exists:

   SELECT substr(check_clause,1,strpos(check_clause,' ')-1) AS check_column , constraint_name FROM information_schema.check_constraints WHERE check_clause LIKE 'demo_text%';

   SELECT substr(check_clause,1,strpos(check_clause,' ')-1) AS check_column , constraint_name FROM information_schema.check_constraints WHERE check_clause LIKE 'demo_text%';

   You should see the following output with a parsed check_column name and the system generated check constraint name rather than the nn_demo_1 constraint name:

   check_column | constraint_name --------------+----------------------- demo_text | 2200_18896_2_not_null (1 row)

   check_column | constraint_name --------------+----------------------- demo_text | 2200_18896_2_not_null (1 row)

On the bright side, you can name primary key and foreign key constraints.