SQL Question

Age limit in postgresql

I want to implement the following restriction in PostgreSQL:


People can not have birth dates where at 01/01/2016 have more than 100 years.


So, I've implement the restriction as:

ALTER TABLE person ADD CONSTRAINT CHK_biggerThan100 CHECK(
(
(extract(year FROM current_date) - extract(year FROM birth)) * 365 +
(extract(month FROM current_date) - extract(month FROM birth))* 30 +
(extract(day FROM current_date) - extract(day FROM birth))
) < 36500 --100 years
);


Is it possible to implement this restriction in a more elegant way? And how can I compare the birthday with the date 1/1/2016?

My table person has only two attributes: person_id (int) as the primary key and birth (date).

Answer

Use functions date_part() and age()

For details, see for documentation of date functions https://www.postgresql.org/docs/current/static/functions-datetime.html

ALTER TABLE person ADD CONSTRAINT CHK_biggerThan100 
CHECK (date_part('year',age(timestamp '2016-01-01',birth))<100)
Comments