5

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.

  • What are you storing in angle? the angle to what? – Evan Carroll Jun 2 '18 at 21:22
  • @EvanCarroll Well, it could be anything. Some example might be angle of normalized rotation (from 0 to 360) or pitch/roll/yaw angle (-180 to +180). – Vineet Menon Jun 4 '18 at 4:30
  • but I wouldn't store pitch/roll/yaw like that. I would store the major/minor as a PostGIS point on a table that had columns dedicated to x/y/z, time, and pitch/roll/yaw. – Evan Carroll Jun 4 '18 at 12:50
  • @EvanCarroll Well, the point is that, the three columns are related, and IMHO that's the point of grouping distinct data into "classes" or user defined data structure. – Vineet Menon Jun 5 '18 at 4:32
  • They're not really related -- (X/Y are, because neither one of them stands by itself). Pitch/yaw/roll are not related: they all stand individually. So I (and most others) would store this in a table that was CREATE TABLE ( geog geography(pointz 4326), pitch float, yaw float, roll float ). With this method of storage you could use a PostGIS aggregate to get a linesegment that represented the flight path (and the like) – Evan Carroll Jun 5 '18 at 5:33
10

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
  • @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
1

I would store them separately and not as a composite type. There is a geospatial component here. I would store that as a PostGIS Geography type which is in fact composite: the x getter is ST_X, and y getter is ST_Y

CREATE TABLE plane_path (
  id    int  PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  geog  geography(pointz, 4326),
  ts    timestamp with time zone,
  pitch float,
  yaw   float,
  roll  float
);

Or if you want, you could represent your angle as a DOMAIN and put it on the table too.

CREATE DOMAIN angle AS float CHECK (value between -90 and 90);

CREATE TABLE plane_path (
  id    int  PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  geog  geography(pointz, 4326),
  ts    timestamp with time zone,
  pitch angle,
  yaw   angle,
  roll  angle
);

With this schema and PostGIS, you could easily aggregate the points into a linepath representing the trajectory with ST_MakeLine

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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