Getting generate_series(date, date) does not exist 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
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.