patternsqlModerate
Constraint on Composite Type
Viewed 0 times
typecompositeconstraint
Problem
How can I create a constraint on a sub-field of composite type?
Pseudocode
Is this possible in PostgreSQL 9.2? It seems to be not possible in 9.1 as mentioned here.
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."
This INSERT statement should succeed.
But this one should fail.
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: 23514Code 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: 23514Context
StackExchange Database Administrators Q#46758, answer score: 12
Revisions (0)
No revisions yet.