Postgres custom type with constraints

datatypespostgresqlpostgresql-12

I was wondering if it were possible (and/or desirable) to create custom types with constraints on the values. Say I have a custom type for height/width/length:

CREATE TYPE dimensions AS (w float, h float, l float);

I'd like to ensure that none of my widths are greater than 10 (for example).

Is such a thing possible, or would I be better served creating check constraints on every table that uses this type? I'd like to be able to add/edit the constraint in a single place (ie, on the type itself).

Thanks!

Best Answer

You can create a domain based on your type.

create domain checked_dimensions 
   as dimensions
   constraint check_size check ( (value).w <= 10 );

Note the parentheses around the keyword value. They look redundant but are required in this case.

Then use the type checked_dimensions as your column type

Related Solutions

Postgresql – Fast hamming distance queries in postgres

Well, I spent a while looking at writing a custom postgres C extension, and wound up just writting a Cython database wrapper that maintains a BK-tree structure in memory.

Basically, it maintains a in-memory copy of the phash values from the database, and all updates to the database are replayed into the BK-tree.

It's all up on github here. It also has a LOT of unit-tests.

Querying across a dataset of 10 million hash values for items with a distance of 4 results in touching ~0.25%-0.5% of the values in the tree, and takes ~100 ms.

Postgresql – Table-based custom type does not verify check constraints

A record will only be null if all of its fields are null or if the record itself is null. If you want to check if any of the fields is null then check one by one:

create table final
(
    name character varying(20) not null,
    data source check (
        (data).id is not null and (data).weight is not null
    ),
    constraint final_pkey primary key (name)
);

insert into final (name, data) values ('a', null);
ERROR:  new row for relation "final" violates check constraint "final_data_check"
DETAIL:  Failing row contains (a, null).

insert into final (name, data) values ('a', (null, null));
ERROR:  new row for relation "final" violates check constraint "final_data_check"
DETAIL:  Failing row contains (a, (,)).

insert into final (name, data) values ('a', ('1', null));
ERROR:  new row for relation "final" violates check constraint "final_data_check"
DETAIL:  Failing row contains (a, (1,)).

insert into final (name, data) values ('a', ('1', 1));
INSERT 0 1

Best Answer

Related Solutions

Postgresql – Fast hamming distance queries in postgres

Postgresql – Table-based custom type does not verify check constraints

Related Question