Re: to_timestamp alternatives

On Thu, Dec 31, 2015 at 2:34 PM, gkhan <drjohnpayne(at)gmail(dot)com> wrote:

> 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
>

​Um, both fail for the same reason.​ You added ", 'DD.MM.YYYY HH24:MI:ss'"
to the parenthesized expression which turns it into an adhoc record type
instead of simply performing grouping. Basically you wrote: ROW(literal,
literal)::timestamp.

You cannot pass arguments here, which is what your format expression is.
But the casting mechanism understands most comment formats are will try
them until it fails, runs out of possibilities, or succeeds.

David J.