Postgresql check constraint on all individual elements in array using function

Question

If I have a table

create table foo ( bar text[] not null check ..... );

and a function

create function baz(text) returns boolean as $$ .....

How do I add a check constraint to the foo table such that every element in the bar field validates the baz function?

I'm thinking that I need to create a function

create function array_baz(arg text[]) returns boolean as $$ 
    with x as ( select baz(unnest(arg)) as s_arg )
    select not exists (select 1 from x where s_arg = false)
$$ language sql strict immutable;

 create table foo (bar text[] not null check ( array_baz(bar) = true ) );

However, I'm sure that I'm reinventing the wheel here and there's a cuter way of doing this. What psql trick am I missing? A map function would be nice

create table foo (bar text[] not null check (true = all(map('baz', bar)));

but so far my search efforts are fruitless.

You can simplify the function to select every(baz(u)) from unnest(arg) u, but I can't think of any way to get rid of it entirely... — Nick Barnes
– Nick Barnes, Commented Jul 29, 2017 at 4:44
Thanks @NickBarnes . Was fairly sure there would be a nicer way of writing that CTE. — Christopher Causer
– Christopher Causer, Commented Jul 31, 2017 at 14:08

joanolo · Accepted Answer · 2017-07-28 15:08:53Z

You can do what you want in more than one way. If you want to use the ALL(...) quantifiers, you need a suitable operator. For that, you first need a function to perform what you want:

Imagine you want to check that your texts don't have any uppercase letter in them. You'd define a function like:

CREATE FUNCTION doesnt_have_uppercase(b boolean, t text)
/* Compares b to the result of `t` not having any non-lowercase character */
    RETURNS boolean
    IMMUTABLE
    STRICT
    LANGUAGE SQL
AS
$$
    SELECT (t = lower(t)) = b
$$ ;

Based on it, create an operator:

CREATE OPERATOR =%= (
    PROCEDURE = doesnt_have_uppercase,
    LEFTARG = boolean,
    RIGHTARG = text
) ;

You need this operator because the ANY and ALL quantifiers need the following structure:

expression operator ALL(array)

At this point, you can define:

create table foo 
( 
    bar text[] not null,
    CONSTRAINT bar_texts_cant_have_uppercase CHECK(true =%= ALL(bar))
);

Which will lead you to the following behaviour:

INSERT INTO foo
    (bar)
VALUES
    (ARRAY['this will pass', 'this too']) ;

1 rows affected

INSERT INTO foo
    (bar)
VALUES
    (ARRAY['that would pass', 'BUT THIS WILL PREVENT IT']) ;

ERROR:  new row for relation "foo" violates check constraint "bar_texts_cant_have_uppercase"
DETAIL:  Failing row contains ({"that would pass","BUT THIS WILL PREVENT IT"}).

Check it all at dbfiddle here

I would most probably seek a less tortuous route, however:

CREATE FUNCTION doesnt_have_uppercase(t text[])
/* Returns true if all elements of t don't have any uppercase letter */
    RETURNS boolean
    IMMUTABLE
    STRICT
    LANGUAGE SQL
AS
$$
    SELECT (NOT EXISTS (SELECT 1 FROM unnest(t) q WHERE q <> lower(q)))
$$ ;

create table foo 
( 
    bar text[] not null,
    CONSTRAINT bar_texts_cant_have_uppercase CHECK(doesnt_have_uppercase(bar))
);

This behaves exactly like the previous example (except if some of the elements of the array are NULL).

dbfiddle here

Thanks. Your last solution looks pretty much the same as what I had in the question so I'm assuming you don't know a simple built-in like a map function and instead I have to write either an operator or another function? I'll accept your answer in two weeks if nobody else answers.

eradman · Accepted Answer · 2021-10-01 14:21:44Z

4

Check constraints can be applied to individual items in an array by defining a domain type:

CREATE DOMAIN lower_text AS text
CHECK(
  VALUE = lower(VALUE)
);

CREATE TABLE test (
  name lower_text[] NOT NULL
);

answered Oct 1, 2021 at 14:21

eradman

2,1352 gold badges20 silver badges24 bronze badges

Collectives™ on Stack Overflow

Postgresql check constraint on all individual elements in array using function

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related