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

Question

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?

Any insert in any table (all have textn instead of text) where the textn field is '' — mamcx, Dec 13 '19 at 22:39
possibly related: https://dba.stackexchange.com/questions/96743/how-to-get-exception-context-for-a-manually-raised-exception-in-pl-pgsql — Bergi, Dec 13 '19 at 22:59
It's hard to answer your question because it doesn't include a [MRE]. We can't tell what crates (and their versions), types, traits, fields, etc. are present in the code. It would make it easier for us to help you if you try to reproduce your error on the [Rust Playground](https://play.rust-lang.org) if possible, otherwise in a brand new Cargo project, then [edit] your question to include the additional info. There are [Rust-specific MRE tips](//stackoverflow.com/tags/rust/info) you can use to reduce your original code for posting here. Thanks! — Shepmaster, Dec 14 '19 at 04:13
We do not even know what crate you are using to connect to Postgres, so it's impossible to answer this. — Shepmaster, Dec 14 '19 at 04:13
Instead of a domain, what about using a check constraint with a custom error message? https://stackoverflow.com/a/53448944/330315 — , Dec 14 '19 at 10:50
@Shepmaster The use of rust here is incidental. The problem is at the postgresql side. — mamcx, Dec 14 '19 at 18:01

score 3 · Accepted Answer · answered Dec 14 '19 at 10:41

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.

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

1 Answers1