Postgres returns null on function error / cast failure

Question

I am trying to convert text values ​​to timestamp values. For the following table: 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 trying to execute select with cast as follows:

select id, c1, c1::timestamp as c2 from a;

This works correctly if there were only the first 5 lines, but for the 6th line, where c1 is 24-04-89 2:33 am , it produces the following error:

ERROR: date / time field value out of range: "24-04-89 2:33 AM"
TIP. Perhaps you need a different "datestyle" setting.

I want null for those values ​​that cannot be selected for the timestamp, and not for the entire command. 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 general way to implement this? i.e.: (based on klin answer ) the plpgsql wrapper function that sets to null if the function it wraps causes an error. For example, for the set_null_on_error function, which can be used as follows:

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;

Answer 1

This can be done by catching an exception in the 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; 

---

Attempt to define a common function.

Suppose you define a set_null_on_error(anyelement) function. Call

 select set_null_on_error('foo'::timestamp); 

causes an 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, rather inconvenient to use and, as a rule, can cause problems that are difficult to debug.