1

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:

ytu
  • 1,822
  • 3
  • 19
  • 42

1 Answers1

0

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
$$BEGIN
EXECUTE format('CREATE SEQUENCE years MAXVALUE %s INCREMENT -12', EXTRACT(YEAR FROM now()));
END$$;

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228