14

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
)
Improve this question
1
  • Why not use a proper relational design with an optional foreign key to an address table? Commented Apr 3, 2021 at 12:08

3 Answers 3

Reset to default
12

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
Improve this answer
2
  • 2
    Is it possible to create a domain and composite type in a single expression? It seems right now you have to first CREATE TYPE then do CREATE DOMAIN? Commented Dec 12, 2022 at 9:32
  • This answer is incorrect. Your first attempt prevents NULL as you saw; however your second attempt now allows rows where each field is NULL: INSERT INTO test_address_domain2 VALUES ((NULL, NULL, NULL)); succeeds. Commented Oct 7, 2024 at 20:02
1

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
, ...
);
Improve this answer
0

The correct answer to this was provided by Erwin Brandstetter.

Essentially you need to define a DOMAIN that prevents NULL for each of the composite type's field's types. Then define the composite type such that its fields have the appropriate DOMAIN.

In your case, you'd define two DOMAINs: one for text that prevents NULL and another for int that prevents NULL. Then define your composite type appropriately.

BEGIN;
    CREATE DOMAIN text_notnull AS text NOT NULL;
    CREATE DOMAIN int_notnull AS int NOT NULL;
    CREATE TYPE address AS (city text_notnull, address_line text_notnull, zip_code int_notnull);
    CREATE TABLE companies(id serial NOT NULL PRIMARY KEY, name text NOT NULL, headquarters address);
    INSERT INTO companies VALUES (DEFAULT, '', NULL), (DEFAULT, '', ('', '', 0)); --succeeds
    INSERT INTO companies VALUES (DEFAULT, '', (NULL, NULL, NULL)); --fails
    INSERT INTO companies VALUES (DEFAULT, '', ('', NULL, NULL)); --fails
    INSERT INTO companies VALUES (DEFAULT, '', (NULL, '', NULL)); --fails
    INSERT INTO companies VALUES (DEFAULT, '', (NULL, NULL, 0)); --fails
    INSERT INTO companies VALUES (DEFAULT, '', (NULL, '', 0)); --fails
    INSERT INTO companies VALUES (DEFAULT, '', ('', NULL, 0)); --fails
    INSERT INTO companies VALUES (DEFAULT, '', ('', '', NULL)); --fails
ROLLBACK;
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.