How to save an interval of years in postgresql

I have to save an interval of years, like:

id intervalYears
1  2014/2015
2  2015/2016
3  2016/2017

And so on. Those years are like accademic years, so the first year must be lesser then the second, but greater or equal then the actual year.

So, how to save this datatype? Should i separate the dates and add some costraints?

If possible, just store the first value

It doesn't look like you need to store two numbers at all.

If these are like tax years, i.e. "2014/2015 financial year", you only need to consistently store one part.

If you store 2014, your app knows it's the academic year beginning in 2014. So it can display "2014/2015 Academic Year" with no ambiguity.

If you really need a range

If you actually need ranges and your sample data doesn't express that, e.g. you might need to store 2012/2015, then two int2 columns would be a good choice. You'd use CHECK constraints to limit the range to a reasonable value for an academic year and to enforce that the lower bound is less than the higher bound, as @Neil has already showed.

Alternately you could use the new-ish and PostgreSQL-specific int4range type:

SELECT int4range(2014, 2015);

per the documentation on range types. This offers you lots more operators at the cost of being fiddlier to work with for basic queries and from client apps.

You can even make a DOMAIN over it to confine its range.

CREATE DOMAIN academicyearrange AS int4range 
  CONSTRAINT must_be_sane_year 
  CHECK ( lower(VALUE) BETWEEN 1900 AND 2100 
      AND upper(VALUE) BETWEEN 1900 AND 2100 );

e.g.

regress=> SELECT '[1421,2247)'::academicyearrange;
ERROR:  value for domain academicyearrange violates check constraint "must_be_sane_year"

regress=> SELECT int4range(1421,2247)::academicyearrange;
ERROR:  value for domain academicyearrange violates check constraint "must_be_sane_year"

(Before range types I would've suggested a domain over a composite type, but int4range is better in every way than a composite type).

Most likely all this is unnecessary - if you don't need variable length ranges, i.e. it's always someyear/thenextyear, you should just store the first year.

StackExchange Database Administrators Q#78063, answer score: 5