HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

Constraint on Composite Type

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
typecompositeconstraint

Problem

How can I create a constraint on a sub-field of composite type?

Pseudocode

create type axis(
    major_axis float,
    minor_axis float,
    angle float constraint angle_constraint check(angle between -90 and 90)
);

create table sample(
    axis1 axis,
    axis2 axis
);


Is this possible in PostgreSQL 9.2? It seems to be not possible in 9.1 as mentioned here.

Solution

Use CREATE DOMAIN with a CHECK constraint. This works in PostgreSQL 9.1. It's documented to work in at least 8.0+. "A partial workaround is to use domain types as members of composite types."

create domain angle as float check (value between -90 and 90);

create type axis as (
    major_axis float,
    minor_axis float,
    angle angle
);

create table sample(
    axis1 axis,
    axis2 axis
);


This INSERT statement should succeed.

insert into sample values
(row(0, 0, 35), row(0, 0, 35));


But this one should fail.

insert into sample values
(row(0, 0, 93), row(0, 0, 35));
ERROR: value for domain angle violates check constraint "angle_check"
SQL state: 23514

Code Snippets

create domain angle as float check (value between -90 and 90);

create type axis as (
    major_axis float,
    minor_axis float,
    angle angle
);

create table sample(
    axis1 axis,
    axis2 axis
);
insert into sample values
(row(0, 0, 35), row(0, 0, 35));
insert into sample values
(row(0, 0, 93), row(0, 0, 35));
ERROR: value for domain angle violates check constraint "angle_check"
SQL state: 23514

Context

StackExchange Database Administrators Q#46758, answer score: 12

Revisions (0)

No revisions yet.