Re: to_timestamp alternatives - Mailing list pgsql-general

gkhan schrieb am 31.12.2015 um 22:34:
> Thanks very much for both of your replies.  I had tried something similar and
> gotten an error, so I am probably making a stupid mistake.  If I try this,
> it works:
>
>     SELECT ('09.03.2014'||' '||lpad('3:00:00',8,'0'),'DD.MM.YYYY
> HH24:MI:SS')::timestamp
>
> but if I use column names instead of the text, like this, it fails:
>     SELECT (gmt_date||' '||lpad(gmt_time),'DD.MM.YYYY HH24:MI:SS')::timestamp
> ...
>
> Both the gmt_date and gmt_time columns are "text" data type and formatted
> exactly as in the original example, but I get this error:
>     ERROR:  cannot cast type record to timestamp without time zone

This expression:

     (gmt_date||' '||lpad(gmt_time),'DD.MM.YYYY HH24:MI:SS')


creates an anonymous object type with two columns, that's why you get the "cannot cast type record" error.

You only need to provide the text value to be casted (no format mask).
Use only a single expression without those unnecessary parentheses:

     SELECT gmt_date||' '||lpad(gmt_time,8,'0')::timestamp

You don't need the lpad() either:

     SELECT gmt_date||' '||gmt_time::timestamp