Generate series of dates - using date type as input

Question

Documentation for generate_series says that argument can be int or bigint for generate_series(start, stop) and generate_series(start, stop, step) cases and timestamp or timestamp with time zone for generate_series(start, stop, step interval).

What is the reason that generate_series works also with date type as input and returns timestamp with timezone?

pg=# select generate_series('2014-01-01'::date,'2014-01-02'::date,'1 day');
    generate_series     
------------------------
 2014-01-01 00:00:00+01
 2014-01-02 00:00:00+01
(2 rows)

Answer 1

Thanks to function type resolution we can also pass date values to generate_series() because there is an implicit cast from date to timestamp as well as from date to timestamptz. Would be ambiguous, but timestamptz is "preferred" among "Date/time types". Detailed explanation:

• Generating time series between two dates in PostgreSQL

For a bare date the local time 00:00 is assumed in the cast. Be aware that the current time zone setting directly affects the result if you use date as input since, obviously, '2014-01-10 00:00' represents a different point in time in Tokio than it does in New York.

How does Postgres decide what types are acceptable?

Postgres basically distinguishes between three types of casts:

Explicit casts .. when using CAST or :: syntax.
Assignment cast .. implicit cast when a value is assigned to a target column.
Implicit cast .. implicit casts in all other expressions.

There has to be an implicit cast registered in the system from the input type to the expected type to make a function silently accept (and convert) an input value.

To see which casts are defined to timestamptz, you can query the catalog table pg_cast:

SELECT castsource::regtype, casttarget::regtype, castcontext
FROM   pg_cast
WHERE  casttarget = 'timestamptz'::regtype;

         castsource          |        casttarget        | castcontext
-----------------------------+--------------------------+-------------
 abstime                     | timestamp with time zone | i
 date                        | timestamp with time zone | i
 timestamp without time zone | timestamp with time zone | i
 timestamp with time zone    | timestamp with time zone | i

All of these casts are implicit. The manual on castcontext:

Indicates what contexts the cast can be invoked in. e means only as an explicit cast (using CAST or :: syntax). a means implicitly in assignment to a target column, as well as explicitly. i means implicitly in expressions, as well as the other cases.

Bold emphasis mine.