Generate series of dates – using date type as input

by

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:

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.

More Related Contents:

  1. Generating time series between two dates in PostgreSQL
  2. Difference between timestamps with/without time zone in PostgreSQL
  3. now() default values are all showing same timestamp
  4. How to exit from PostgreSQL command line utility: psql
  5. Convert hex in text representation to decimal number
  6. How to convert primary key from integer to serial?
  7. Making sense of Postgres row sizes
  8. I forgot the password I entered during postgres installation
  9. How to set value of composite variable field using dynamic SQL
  10. Is there a way to disable function overloading in Postgres
  11. Test for null in function with varying parameters
  12. Postgres query optimization (forcing an index scan)
  13. Dynamically generate columns for crosstab in PostgreSQL
  14. I forgot the password I entered during PostgreSQL installation
  15. PostgreSQL ERROR: canceling statement due to conflict with recovery
  16. Change type of varchar field to integer: “cannot be cast automatically to type integer”
  17. Update timestamp when row is updated in PostgreSQL
  18. Postgresql insert trigger to set value
  19. How to add column if not exists on PostgreSQL?
  20. How to perform a select query in a DO block?
  21. Join a count query on generate_series() and retrieve Null values as ‘0’
  22. Importing .csv with timestamp column (dd.mm.yyyy hh.mm.ss) using psql \copy
  23. Convert escaped Unicode character back to actual character in PostgreSQL
  24. Use multiple conflict_target in ON CONFLICT clause
  25. PostgreSQL -must appear in the GROUP BY clause or be used in an aggregate function
  26. Postgres error on insert – ERROR: invalid byte sequence for encoding “UTF8”: 0x00
  27. How do you find the disk size of a Postgres / PostgreSQL table and its indexes
  28. Hashing a String to a Numeric Value in PostgreSQL
  29. Fetch records that are non zero after the decimal point in PostgreSQL
  30. Format specifier for integer variables in format() for EXECUTE?

Leave a Comment