PostgreSQL - Constraint on each custom type element in an array

Question

Suppose I have this custom type:

CREATE TYPE post AS (
  title varchar(100),
  content varchar(10000)
);

And this table:

CREATE TABLE blogs (
  -- Constraint on one custom type element:
  one_post post NOT NULL
    CHECK ((one_post).title IS NOT NULL AND (one_post).content IS NOT NULL),

  -- Constraint on array elements:
  posts post[] NOT NULL CHECK (???)
);

As commented in the code, I want to check that the title and content of each element of posts are NOT NULL, but I can't find a way to express that. What is the right way to do this?

(P.S.: I know the right way is to normalize my schema, I'm just curious if what I asked can be done.)

Abelisto · Accepted Answer · 2017-06-11 13:19:34Z

For complex check constraints usually used stored functions.

There is my attempt to make it in convenient way:

First of all:

create type post as (
  title varchar(100),
  content varchar(10000)
);

create function is_filled(post) returns bool immutable language sql as $$
  select $1.title is not null and $1.content is not null
$$;

with t(x) as (values(('a','b')::post), (('a',null)::post), ((null,null)::post))
select *, is_filled(x), (x).is_filled from t;

╔═══════╤═══════════╤═══════════╗
║   x   │ is_filled │ is_filled ║
╠═══════╪═══════════╪═══════════╣
║ (a,b) │ t         │ t         ║
║ (a,)  │ f         │ f         ║
║ (,)   │ f         │ f         ║
╚═══════╧═══════════╧═══════════╝

Here you can see two syntaxes of function calling (personally I like the second one - it is more OOP-like)

create function is_filled(post[]) returns bool immutable language sql as $$
  select bool_and((x).is_filled) and $1 is not null from unnest($1) as x
$$;

with t(x) as (values(('a','b')::post), (('a',null)::post), ((null,null)::post))
select (array_agg(x)).is_filled from t;

╔═══════════╗
║ is_filled ║
╠═══════════╣
║ f         ║
╚═══════════╝

Note that we are able to use functions with same name but with different parameters - it is Ok in PostgreSQL.

Finally:

create table blogs (
  one_post post check ((one_post).is_filled),
  posts post[] check ((posts).is_filled)
);

insert into blogs values(('a','b'), array[('a','b'),('c','d')]::post[]); -- Works
insert into blogs values(('a','b'), array[('a','b'),(null,'d')]::post[]); -- Fail
insert into blogs values(('a',null), array[('a','b')]::post[]); -- Fail

PS: script to clear our experiment:

/*
drop table if exists blogs;
drop function if exists is_filled(post);
drop function if exists is_filled(post[]);
drop type if exists post;
*/

Collectives™ on Stack Overflow

PostgreSQL - Constraint on each custom type element in an array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related