3

I'm using PostgreSQL 9.5.3 and I'm trying to select all users that have an age of over twenty. I have constructed a SQLFiddle that shows the error:

http://sqlfiddle.com/#!15/5cd52/3

SNIPPET:

Schema:

CREATE TABLE users (
  name text,
  birthdate bigint
);

INSERT INTO users values('Chris', 774532800000);

SQL:

SELECT u.*, age(TO_TIMESTAMP(u.birthdate / 1000)) as age from users u
WHERE age > 20

ERROR:

ERROR: column "age" does not exist Position: 77

The reason that I'm deviding by 1000 is because my dates are all stored using milliseconds as that's the default by Firebase. This postgreSQL database is used to handle more complex queries that Firebase couldn't.

Alex
  • 16,739
  • 1
  • 28
  • 51
Hobbyist
  • 15,888
  • 9
  • 46
  • 98

2 Answers2

1

The age alias still does not exist when the where clause is executed, before the select clause. One solution is to check the age in an outer query:

select *
from (
    select u.*,
        extract(year from age(to_timestamp(u.birthdate / 1000))) as age
    from users u
) s
where age > 20
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
0

EDIT As suggested by @Clodoaldo Neto (I'm new, don't know how to tag), here it is:

SELECT *
FROM staging.users u 
WHERE age(to_timestamp(u.birthdate / 1000)) > '20 years';

Explanation:

  • You need to cast birthdate / 1000 to double because to_timestamp only accept double.
  • Then when filtering, you need to use '20 years' instead of 20 because the age function returns interval.
librata
  • 150
  • 9