How to specify not null contraints for the fields of composite types in postgres

All we need is an easy explanation of the problem, so here it is.

Let’s say I would like to have a composite type for address, like:

create type address as (
  city text,
  address_line text,
  zip_code int
);

And to make data integrity better, I don’t want to allow NULLs to be members of city, address_line, or zip_code. So I would like to have a not null constraint for those fields.

Creating domain checks isn’t working for me. So this code produces error:

create domain address_domain as address 
check (
  value.city is not null and 
  value.address_line is not null and
  value.zip_code is not null
);

You might say: "Well, why won’t you store address as three columns, and add contraints to the fields?". And I will answer with that I would like to have ability to make address itself nullable, but if address is present, all of it’s fields should be present as well. Something like this:

create table companies (
  id serial primary key,
  name text not null,
  headquaters address -- this one can be null tho
)

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The correct syntax for a check constraint for a composite type would look like this:

create domain address_domain as address 
check (
  (value).city is not null and 
  (value).address_line is not null and
  (value).zip_code is not null
);

Let’s check:

melkij=> create table test_address_domain (a address_domain);
CREATE TABLE
melkij=> insert into test_address_domain values (('foo', 'bar', 11));
INSERT 0 1
melkij=> insert into test_address_domain values (('foo', 'bar', null)); -- fails
ERROR:  value for domain address_domain violates check constraint "address_domain_check"
melkij=> insert into test_address_domain values (('foo', null, 11)); -- fails
ERROR:  value for domain address_domain violates check constraint "address_domain_check"
melkij=> insert into test_address_domain values ((null, 'bar', 11)); -- fails
ERROR:  value for domain address_domain violates check constraint "address_domain_check"
melkij=> insert into test_address_domain values (null); -- fails
ERROR:  value for domain address_domain violates check constraint "address_domain_check"

But pay attention to the last line. It seems that this is not what you want. Try allow explicitly:

melkij=*> create domain address_domain2 as address 
check (
  value is null or (
  (value).city is not null and 
  (value).address_line is not null and
  (value).zip_code is not null
));
CREATE DOMAIN
melkij=*> create table test_address_domain2 (a address_domain2);
CREATE TABLE
melkij=*> insert into test_address_domain2 values (null); -- works now
INSERT 0 1

Method 2

I don’t know if you can create such domain constraints for composite types, but if you use regular types like below, you can add a constraint like:

create table companies 
( company_id serial primary key
, company_name text not null
, headquaters_city text
, headquaters_address_line text
, headquaters_zipcode text
,    constraint all_or_nothing_address 
         check ((case when headquaters_city is not null then 1 else 0 end +
                 case when headquaters_address_line is not null then 1 else 0 end +
                 case when headquaters_zipcode is not null then 1 else 0 end) in (0,3))
);

for more complicated scenarios you can add a unique weight to each attribute and check for certain combinations:

check ((case when headquaters_city is not null then 1 else 0 end +
                 case when headquaters_address_line is not null then 2 else 0 end +
                 case when headquaters_zipcode is not null then 4 else 0 end) in (1,2,7))

That said, you may want to normalize your design to something like:

create table companies 
( company_id serial primary key
, company_name text not null
);

create table company_adresses
( company_id int not null primary key
      references companies (company_id)
, headquaters_city text not null
, headquaters_address_line text not null
, ...
);

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: , ,

Leave a Reply