Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

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);
share|improve this answer
    
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 '12 at 14:02
    
@KenD try regexp_replace(mac_address_temp,'[.:-]','', 'g') instead –  dezso Sep 21 '12 at 14:06
    
@KenD you need to use regexp_replace(mac_address_temp,'[.:-]','', 'g'). The g tells it to replace "globally". –  a_horse_with_no_name Sep 21 '12 at 14:07
    
Answer edited.. –  FreshPhilOfSO Sep 21 '12 at 14:09
    
Thank you Phil, dezso and a_horse_with_no_name, really appreciated! –  Ken D Sep 21 '12 at 14:13
show 1 more comment

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.

share|improve this answer
    
Thanks, this is good to know for future reference. Phil's approach is obviously a better answer than the question :). –  Ken D Sep 23 '12 at 17:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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