Can I create a DOMAIN on a range of years

Question

I have to create a custom domain that is an interval between two years, but i actually need both years. For example:

'2013/2014'
'2015/2016'

Is there a way to pack both years inside a single domain, or have i to separate them in two attributes of the relation?

score 2 · Accepted Answer · answered Jun 02 '18 at 23:20

A domain is a constraint. It doesn't hold any values whatsoever. It can only restrict a type to exclude values. If you need to store a range, use the range types.

As a side note, you can in fact, create a domain on a range type. In the event you want all of your ranges to start on a Wednesday

CREATE DOMAIN foo AS daterange
  CHECK ( EXTRACT(dow FROM lower(VALUE)) = 4 );

SELECT daterange('2018-01-02'::date,'2018-02-05'::date);

SELECT daterange('2018-01-02'::date,'2018-02-05'::date)::foo;
ERROR:  value for domain foo violates check constraint "foo_check"

Moreover, you can also ensure the range is always one year:

CREATE DOMAIN bar AS daterange
  CHECK (
    (lower(VALUE)::timestamp without time zone - upper(VALUE)::timestamp without time zone) = '-365 days'
  );

But if you're going to do that -- make it such that your range is always one year and a fixed size, I suggest you just store the year that you start as ::smallint and be done with it.

Can I create a DOMAIN on a range of years

1 Answers1