As far as I know there are the following 4 ways to cast a string to date in postgres:

SELECT '2024-04-03'::date AS colon_casting
    , date('2024-04-03') AS function_casting
    , date '2024-04-03' AS type_hinting_casting
    , CAST('2024-04-03' AS date) AS sql_compliant_casting

The question is: Is there a difference between them or are they alias to the same way to convert a string into date?

  • Is there a difference between them Of course. type_hinting_casting specifies literal datatype and does not perform any function processing whereas another 3 variants explicitly calls type-convertion functions. I.e. 3rd variant creates date-type value on the parsing/planning step whereas all other variants performs the convertion from the string-type value to the date-type value on the execution step.
    – Akina
    Apr 3 at 13:00
  • 1
    @Akina You are right that the third variant is different because it only works with string literals, but all of there conversions would happen during query planning. Apr 3 at 20:42
  • @LaurenzAlbe Deterministic constant expression is evaluated in PostgreSQL during query execution planning unconditionally?
    – Akina
    Apr 4 at 4:46
  • 1
    @Akina Yes, if the functions involved are immutable. Apr 4 at 5:51


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.