PostgreSQL update set only where cast is possible and ignore error

postgresqlupdate

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?

Best Answer

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);
Related Question