Search code examples
postgresqlpostgresql-11

Is posible to get better error message when a domain check fail on postgresql?

I create a domain to catch empty strings:

CREATE DOMAIN TEXTN AS TEXT
CONSTRAINT non_empty CHECK (length(VALUE) > 0);

Then I replace all text/varchars fields on the DB with TEXTN.

However, when I get a error, it not give much info:

DbError { severity: "ERROR", parsed_severity: Some(Error), 
code: SqlState("23514"), 
message: "value for domain textn violates check constraint \"non_empty\"", 
detail: None, hint: None, position: None, where_: None, 
schema: Some("libs"), 
table: None,
column: None, datatype: Some("textn"), 
constraint: Some("non_empty")}

It not even tell me in what table and field the check fail.

If is even possible to print the row to insert better, but at least table and field is possible?

Solution

  • PostgreSQL (I checked version 11) simply does not provide this information as part of the protocol. Consider these statements:

    => CREATE DOMAIN TEXTN AS TEXT CONSTRAINT non_empty CHECK (length(VALUE) > 0);
CREATE DOMAIN
=> CREATE TABLE test_table (test_column textn);
CREATE TABLE
=> INSERT INTO test_table VALUES ('');
ERROR:  value for domain textn violates check constraint "non_empty"

    The error message on the wire looks like this:

    S ERROR
V ERROR
C 23514
M value for domain textn violates check constraint "non_empty\
s public
d textn
n non_empty
F execExprInterp.c
L 3494
R ExecEvalConstraintCheck

    There is no trace of test_table or test_column.

    If you have some control over how your framework creates tables, it may be possible to use named table constraints instead of domain types, like this:

    CREATE TABLE test_table (
  test_column text
  CONSTRAINT test_column_check CHECK (length(test_column) > 0));

    If you make sure that the constraint name uniquely identifies the column, you can use that to recover the problematic column.

    Even for a CHECK constraint defined on the column, as in CREATE TABLE test_table (test_column text CHECK (length(test_column) > 0));, PostgreSQL does not report the column name. You only get the name of the constraint, which is autogenerated by PostgreSQL on table creation and usually starts with the column name, but this is not guaranteed.