postgreSQL update set only where cast is possible and ignore error?

Question

I have 2 columns in a PostgreSQL table. The mac_address_temp column is for migration from character type to MAC-address type:

mac_address      | macaddr               |
mac_address_temp | character varying(17) |

I want to migrate data from mac_address_temp to mac_address, but there is some data that can't be cast as macaddr type.

mac_address | mac_address_temp
-------------+------------------
            | AAB5:4f27:e299
            | AAB54f27e299

UPDATE mactable SET mac_address = CAST(mac_address_temp as macaddr);
ERROR:  invalid input syntax for type macaddr: "AAB5:4f27:e299"

Is there a way to ignore where CAST doesn't work and still update rest of the table?

Answer 1

Just remove the ':' characters and the update will work:

UPDATE mactable SET mac_address = CAST(replace(mac_address_temp,':','') as macaddr);

To get rid of everything that's not a hex digit:

postgres=# insert into mactable (mac_address_temp) values ('AAB5:-4f-27:e2-99');
INSERT 0 1
postgres=# select regexp_replace(mac_address_temp, '[^a-fA-F0-9]', '', 'g')
postgres-# from mactable;
 regexp_replace
----------------
 AAB54f27e299
 AAB54f27e299
(2 rows)

postgres=#

So the update would be:

UPDATE mactest SET mac_address = CAST(regexp_replace(mac_address_temp, '[^a-fA-F0-9]', '', 'g') as macaddr);

Answer 2

In answer to "Is there a way to ignore where cast doesn't work and still update rest of the table?":

Not directly. There's no "ignore error rows" flag you can set, and there's nothing like an "is_cast_valid(type,value)" function you could use.

A typical approach is to to use a PL/PgSQL function that loops over the input query with FOR record_var IN query. For each input row, it enters a BEGIN ... EXCEPTION block where it tries the INSERT. On failure control jumps to the EXCEPTION WHERE block, where it is typical to RAISE NOTICE about the bad row or insert it into a separate table for invalid rows. Then execution continues for the next row.

This works, but it's slow and clumsy. If at all possible it is always best to filter the data first, or write queries that detect and exclude the bad data. For that reason phil's approach is by far the better one.