Sample Header Ad - 728x90

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

14 votes
3 answers
4371 views
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
)
Asked by Link0 (243 rep)
Apr 3, 2021, 11:40 AM
Last activity: Oct 8, 2024, 03:50 AM