Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

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
share|improve this answer
    
@Vineet: A DOMAIN would only be ruled out if you'd want to check on a combination of columns in the composite type. But that does not seem to be the case, so this should be the way to go. –  Erwin Brandstetter Jul 22 '13 at 20:08
add comment

The docs for 9.2 and 9.3 don't differ from 9.1 with regard to CHECK contraints for composite types. It's safe to assume that this is still not possible.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.