CODEWITHSUNDEEP
postgresqldatabase-designconstraints
CCPony
CCPony

Reputation: 998

Table-wide constraint in Postgres

I'm rather new at Postgres. Is there any way that I can write a constraint for a table that checks ALL characters fields and tests to make sure that there are no leading or trailing characters IF there is any value in the field?

This way I don't have to itemize each and every character field when I write the constraint.

Thanks!

Upvotes: 0

Views: 137

Answers (1)

Patrick
Patrick

Reputation: 32264

No, you cannot write such a constraint insofar as I am aware.

What you could do is to create a DOMAIN that has the check function and then make all of your table columns of that domain type. Assuming that the characters you refer to are spaces:

CREATE DOMAIN varchar_no_spaces AS varchar
CHECK ( left(VALUE, 1) <> ' ' AND right(VALUE, 1) <> ' ') );

There are many variations on this CHECK expression, including regular expression and using different or multiple characters. See the string functions for more options.

Then:

CREATE TABLE mytable (
  f1     varchar_no_spaces,
  ...
);

Effectively you relay the constraint check to the level of the domain.

Upvotes: 1

Related Questions