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;
PostgreSQL NULLIF function syntax The NULLIF function returns a null value if argument_1 equals to argument_2 , otherwise it returns argument_1 .
nullif also used with the coalesce function to handle the null values. PostgreSQL nullif function returns a null value if provided expressions are equal. If two expressions provided are equal, then it provides a null value; as a result, otherwise, it will return the first expression as a result.
PostgreSQL does not support nvl functions, but it supports coalesce functions. The usage is the same with that in Oracle. You can utilize coalesce to convert nvl and coalesce functions of Oracle. The arguments have to be of the same type, or can be automatically converted to the same type.
The nullif() function returns a null value, if a the value of the field/column defined by the first parameter equals that of the second. Otherwise, it will return the original value.
This can be done by trapping an exception in a plpgsql function.
create or replace function my_to_timestamp(arg text)
returns timestamp language plpgsql
as $$
begin
begin
return arg::timestamp;
exception when others then
return null;
end;
end $$;
select id, c1, my_to_timestamp(c1) as c2 from a;
Trying to define a generic function.
Assume that you defined a function set_null_on_error(anyelement)
. Calling
select set_null_on_error('foo'::timestamp);
raises error before the function is executed.
You can try something like this:
create or replace function set_null_on_error(kind text, args anyarray)
returns anyelement language plpgsql
as $$
begin
begin
if kind = 'timestamp' then
return args[1]::timestamp;
elseif kind = 'number' then
return to_number(args[1], args[2]);
end if;
exception when others then
return null;
end;
end; $$;
select set_null_on_error('timestamp', array['2014-01-01']);
select set_null_on_error('number', array['1.22444', '9999D99']);
In my opinion such a solution is too complicated, quite inconvenient to use and generally might turn out to generate problems hard to debug.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With