PostgreSQL Calculate Age From Birthdate

There is AGE() function that performs two calculations depending of provided params:

• It substracts date2 from date1 if two params are provided:

  --calculation: date1 - date2
  age(date1, date2)

• It substracts date from the current date if only one param is provided:

  --calculation: current_date - date
  age(date)    
  

---

Calculate Age From Birthdate

All of the following SELECT's will give the same result:

SELECT EXTRACT(YEAR from AGE(birth)) as "age", * FROM customers;

or

SELECT EXTRACT(YEAR from AGE(NOW(), birth)) as "age", * FROM customers;

or

SELECT EXTRACT(YEAR from AGE(CURRENT_DATE, birth)) as "age", * 
FROM customers;

or

SELECT EXTRACT(YEAR from AGE(CURRENT_TIMESTAMP, birth)) as "age", * 
FROM customers;

or

SELECT EXTRACT(YEAR from AGE(CURRENT_TIMESTAMP, birth::timestamp)) as "age", * 
FROM customers;

or

SELECT date_part('year', AGE(birth)) as "age", * FROM customers;

result:

age | user_id | username | first_name | last_name |sex |   birth    | active
----+---------+----------+------------+-----------+----+------------+-------
 27 |       1 | Hommy    | Hommer     | Simpson   | m  | 1987-04-19 | 1
 27 |       2 | Marge    | Marge      | Simpson   | f  | 1987-04-19 | 1

---

Add Age CHECK Constraint based on Birthdate

Add adults age verification:

my_db=# ALTER TABLE customers
my_db-# ADD CONSTRAINT CHK_AGE CHECK (date_part('year', AGE(birth)) >=18);

Let's try to violate it:

my_db=# INSERT INTO customers (user_id, first_name, last_name, sex, birth)
my_db-# VALUES (3, 'Maggie', 'Simpson', 'f', '2010-04-19');
ERROR:  new row for relation "customers" violates check constraint "chk_age"   

---

see Also

• PostgreSQL Date & Time