PostgreSQL CHECK constraints

When violating a constraint on a specific field, the error shows the field’s name as seen in these examples:

-- With CHECK (birth_date > '1900-01-01':

psql:115 CHECK constraint.sql:44: ERROR:  new row for relation "check_constraint"
violates check constraint "check_constraint_birth_date_check"
DETAIL:  Failing row contains (2, fn2, ln2, 1900-01-01, 1925-07-17, -4000).

-- Changed that, now negative salary failure:

psql:115 CHECK constraint.sql:44: ERROR:  new row for relation "check_constraint"
violates check constraint "check_constraint_salary_check"
DETAIL:  Failing row contains (2, fn2, ln2, 1900-01-01, 1925-07-17, -4000).

However, when a CHECK compares two fields, then the message is more a general table check violation, as when joined_date is not > birth_date:

psql:115 CHECK constraint.sql:45: ERROR:  new row for relation "check_constraint"
violates check constraint "check_constraint_check"
DETAIL:  Failing row contains (3, fn2, ln2, 1925-07-17, 1925-07-17, -4000).

PostgreSQL doesn’t have an opinionated stance on which field is wrong, just that something is wrong between them.

Specify a constraint name

If one wants such a constraint to have a more intuitive name, add it after table creation:

ALTER TABLE public.check_constraint
  ADD CONSTRAINT check_constraint_joined_after_birth
  CHECK (joined_date > birth_date)
  ;

INSERT INTO public.check_constraint (
  fname,
  lname,
  birth_date,
  joined_date,
  salary
  ) VALUES
  ('fn2', 'ln2', '1925-07-17', '1925-07-17', -4000)
  RETURNING *
  ;

psql:116 CHECK constraint - add rename drop.sql:47: ERROR:  new row for relation "check_constraint" violates check constraint "check_constraint_joined_after_birth"
DETAIL:  Failing row contains (4, fn2, ln2, 1925-07-17, 1925-07-17, -4000).

And now the constraint violation is more clearly specified (“check_constraint_joined_after_birth“).