1

When I run commands like this,

SELECT * FROM generate_series('YESTERDAY'::date, 'TODAY'::date);
SELECT * FROM generate_series('YESTERDAY'::timestamp without time zone, 'TODAY'::timestamp without time zone);
SELECT * FROM generate_series('YESTERDAY'::timestamp with time zone, 'TODAY'::timestamp with time zone);

I am getting errors like this,

ERROR:  function generate_series(date, date) does not exist
ERROR:  function generate_series(timestamp without time zone, timestamp without time zone) does not exist
ERROR:  function generate_series(timestamp with time zone, timestamp with time zone) does not exist

I also get this same error when I try to generate a date series with timestamp without time zone

2

PostgreSQL 8.4+

This is because generate_series currently requires a step when the inputs are not int, bigint or numeric from the docs

returns: timestamp or timestamp with time zone
generate_series(start, stop, step interval)

As compared to

returns: int, bigint or numeric, step defaults to 1.
generate_series(start, stop, step DEFAULT 1)

To remedy, simply add a day,

SELECT d
FROM generate_series(
  'YESTERDAY'::date,
  'TODAY'::date,
  '1 day' -- REQUIRED STEP
) AS gs(d);

PostgreSQL pre-8.3

Note: you may also get this error if you're using PostgreSQL 8.3 or lower in which case the generate_series doesn't support non-numeric types

Amazon Redshift

generate_series may or may not be currently supported in Amazon Redshift, which isn't really PostgreSQL anyway. If it still isn't supported you're likely to get an error with or without the step,

ERROR:  function generate_series(timestamp without time zone, timestamp without time zone, "unknown") does not exist

See also,

Many people report this to work, but the docs have not been updated to reflect that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.