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?

2 Answers 2


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');
postgres=# select regexp_replace(mac_address_temp, '[^a-fA-F0-9]', '', 'g')
postgres-# from mactable;
(2 rows)


So the update would be:

UPDATE mactest SET mac_address = CAST(regexp_replace(mac_address_temp, '[^a-fA-F0-9]', '', 'g') as macaddr);
  • Awesome, that brings me to second question, ':' could be other characters as well like '-' or '.'. I tried this: UPDATE mactable SET mac_address = CAST(regexp_replace(mac_address_temp,'[.:-]','') as macaddr);, but it only replaces the first occurance: ERROR: invalid input syntax for type macaddr: "AAB54f27:e299". I tried adding {2} in there, but no good, would you be able to please help me with that or guide me right direction?
    – Ken D
    Sep 21, 2012 at 14:02
  • @KenD try regexp_replace(mac_address_temp,'[.:-]','', 'g') instead
    – dezso
    Sep 21, 2012 at 14:06
  • @KenD you need to use regexp_replace(mac_address_temp,'[.:-]','', 'g'). The g tells it to replace "globally".
    – user1822
    Sep 21, 2012 at 14:07

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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.