clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36237 in the last week).

SELECT oid::regprocedure AS function_signature , prorettype::regtype AS return_type FROM pg_proc where proname = 'generate_series';
function_signature return_type
generate_series(integer,integer,integer) integer
generate_series(integer,integer) integer
generate_series(bigint,bigint,bigint) bigint
generate_series(bigint,bigint) bigint
generate_series(numeric,numeric,numeric) numeric
generate_series(numeric,numeric) numeric
generate_series(timestamp without time zone,timestamp without time zone,interval) timestamp without time zone
generate_series(timestamp with time zone,timestamp with time zone,interval) timestamp with time zone
 hidden batch(es)


-- note the additional cast to timestamptz! EXPLAIN (ANALYZE, VERBOSE) SELECT t.day::date FROM generate_series(date '2004-03-07' , date '2004-08-16' , interval '1 day') t(day);
QUERY PLAN
Function Scan on pg_catalog.generate_series t (cost=0.01..12.51 rows=1000 width=4) (actual time=0.129..0.161 rows=163 loops=1)
Output: (day)::date
Function Call: generate_series(('2004-03-07'::date)::timestamp with time zone, ('2004-08-16'::date)::timestamp with time zone, '1 day'::interval)
Planning Time: 0.017 ms
Execution Time: 0.195 ms
 hidden batch(es)


EXPLAIN (ANALYZE, VERBOSE) SELECT day::date FROM generate_series(timestamp '2004-03-07' , timestamp '2004-08-16' , interval '1 day') t(day);
QUERY PLAN
Function Scan on pg_catalog.generate_series t (cost=0.00..12.50 rows=1000 width=4) (actual time=0.026..0.044 rows=163 loops=1)
Output: (day)::date
Function Call: generate_series('2004-03-07 00:00:00'::timestamp without time zone, '2004-08-16 00:00:00'::timestamp without time zone, '1 day'::interval)
Planning Time: 0.013 ms
Execution Time: 0.063 ms
 hidden batch(es)


-- equivalent EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT day::date FROM generate_series(timestamp '2004-03-07', '2004-08-16', '1 day') day;
QUERY PLAN
Function Scan on generate_series day (actual rows=163 loops=1)
Planning Time: 0.012 ms
Execution Time: 0.048 ms
 hidden batch(es)


-- faster equivalent EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT generate_series(timestamp '2004-03-07', '2004-08-16', '1 day')::date AS day;
QUERY PLAN
Result (actual rows=163 loops=1)
-> ProjectSet (actual rows=163 loops=1)
-> Result (actual rows=1 loops=1)
Planning Time: 0.013 ms
Execution Time: 0.049 ms
 hidden batch(es)