I am attempting to convert text
values to timestamp
values.
For the following table called a
:
id | c1
----+--------------------
1 | 03-03-2000
2 | 01-01-2000
3 | 12/4/1990
4 | 12 Sept 2011
5 | 12-1-1999 12:33:12
6 | 24-04-89 2:33 am
I am attempting to perform a select
with a cast as follows:
select id, c1,
c1::timestamp
as c2 from a;
This works correctly if there were only the first 5 rows, but for the 6th row where c1
is 24-04-89 2:33 am
it throws the following error:
ERROR: date/time field value out of range: "24-04-89 2:33 am"
HINT: Perhaps you need a different "datestyle" setting.
What I want is null
for those values which cannot not be casted to timestamp instead of the command failing altogether. Like this:
id | c1 | c2
----+--------------------+---------------------
1 | 03-03-2000 | 2000-03-03 00:00:00
2 | 01-01-2000 | 2000-01-01 00:00:00
3 | 12/4/1990 | 1990-12-04 00:00:00
4 | 12 Sept 2011 | 2011-09-12 00:00:00
5 | 12-1-1999 12:33:12 | 1999-12-01 12:33:12
6 | 24-04-89 2:33 am | (null)
(6 rows)
EDIT:
Also, is there a generic way to implement this? i.e.: (based on klin's answer) a plpgsql wrapper function that sets the value to null
if the function it is wrapped around throws an error.
For e.g.: a function set_null_on_error
that can be used like this:
select id, c1,
set_null_on_error(c1::timestamp)
as c2 from a;
or
select id, c1,
set_null_on_error(to_number(c1, '99'))
as c2 from a;
This can be done by trapping an exception in a plpgsql function.
Trying to define a generic function.
Assume that you defined a function
set_null_on_error(anyelement)
. Callingraises error before the function is executed.
You can try something like this:
In my opinion such a solution is too complicated, quite inconvenient to use and generally might turn out to generate problems hard to debug.