Search code examples
sqlpostgresqlselectsequencegenerate-series

Select values in given sequential numbers

I'd like to write a query to retrieve users' id whose birthyear is in current year or all the years which have increment of -12 from Health_User table. Take this year for example, it would be users who were born in 2018, 2006, 1994, 1982... I want the query to be general enough to be used in any year without resetting the current year.

My approach is written below:

CREATE SEQUENCE years MAXVALUE EXTRACT (YEAR FROM current_date) INCREMENT -12;
SELECT id, EXTRACT (YEAR FROM birthyear)
FROM Health_User
WHERE EXTRACT (YEAR FROM birthyear) IN (years);

birthyear in Health_User is a column in timestamp date format (thanks to Erwin Brandstetter's reminder) which is always January 1st of the year in which the user was born.

However, it returns an error reads: "syntax error at or near 'EXTRACT'" in the first line. Would someone suggest how I can revise my query?

Have read:

Solution

  • You wouldn't create a SEQUENCE for the purpose of a query. The syntax is also invalid since you cannot pass an expression to the utility command. (The reason for the error you see.) It would work with dynamic SQL in a DO statement like this:

    DO
$do$
BEGIN
   EXECUTE format('CREATE SEQUENCE years MAXVALUE %s INCREMENT -12', EXTRACT(YEAR FROM now()));
END
$do$;

    But not for this purpose. Use generate_series() instead:

    SELECT u.*  -- or just the columns you need
FROM   generate_series(0, 12) g  -- nobody's older than 144 years ...
JOIN   Health_User u ON u.birthyear
                     = (date_trunc('year', now()) - interval '12 years' * g)::date;

    Works dynamically any time.

    This query can use a plain index on (birthyear) - while your original cannot, since the predicate is not "sargable".

    Aside:

    birthyear in Health_User is a column in timestamp format which is always January 1st of the year in which the user was born.

    Should be a date or a plain integer, not a timestamp.

    Either way, remember that date_trunc() and the cast to date also depend on the current time zone setting.

    You can also generate timestamps with generate_series() directly. Consider: