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