Search code examples
postgresqlgreenplum

generate_series for Min and Max dates

Here's the very basic query that i want to accomplish in Greenplum Database (like postgresql 8.2.15).

The field create_date in table t is timestamp w/o time zone. Could anyone point me to right query to accomplish this? Thanks.

select * from generate_series ((select EXTRACT (YEAR FROM MIN(t1.create_date)) from t1),(select EXTRACT (YEAR FROM MAX(t1.create_date)) from t1))

Its throwing error 

ERROR:  function generate_series(double precision, double precision) does not exist

LINE 1: select * from generate_series ((select EXTRACT (YEAR FROM MI... ^ HINT: No function matches the given name and argument types. You may need to add explicit type casts.

Solution

  • You can explicitly cast arguments to integer:

    select *
from generate_series (
    (select EXTRACT (YEAR FROM MIN(t1.create_date)) from t1)::int,
    (select EXTRACT (YEAR FROM MAX(t1.create_date)) from t1)::int
)

    sql fiddle demo