Getting generate_series(date, date) does not exist

Question

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

Answer 1

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,

• Amazon Redshift API Version 2012-12-01
• Unsupported PostgreSQL Functions

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