3

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
Nemo
  • 1,111
  • 6
  • 28
  • 45

1 Answers1

5

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

Roman Pekar
  • 107,110
  • 28
  • 195
  • 197