current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.3 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 1 down vote favorite
1

I have a table with field "mac" of type MACADDR. Now I would like to treat a situation (probably with trigger?) when somebody inserts empty string instead of mac address. I would like to turn this empty string to NULL, so postgresql will not complain: invalid input syntax for type macaddr: ""

What I have now in trigger function is this:

IF CHAR_LENGTH(NEW.mac) = 0 THEN
  NEW.mac := NULL;
END IF;

But it does not seem to work. What would you do, if you want to treat this on DB level?

Thank you very much. -Jan

PS: I am a postgresql newbie. But a fascinated one :)

share|improve this question
up vote 4 down vote accepted

You can't do what you want with a trigger. Your incoming empty string will be parsed and converted to a macaddr (or at least the parsing will be attempted) before the trigger is executed. However, you could write a simple function to convert empty strings to NULL and use that in your INSERT:

create function macaddr_ify(m varchar) returns macaddr as $$
begin
    if m is null or length(m) = 0 then
        return null;
    end if;
    return cast(m as macaddr);
end;
$$ language plpgsql;

And then:

insert into t (addr) values (macaddr_ify(str));

I'd recommend that your client application properly convert empty MAC address strings to NULLs itself though.

share|improve this answer
    
Cool, thank you! That is a beautiful function. I am trying to put maximum logic to DB, as there will be a lot of applications and dumb scripts accessing it. – Petr Cezar May 28 '11 at 15:51
    
I've received one more possible solution to this. Can help sbd mabye... One good guy says to use a "fake" table with varchars and a trigger to put it into the final table. Well... that's dirty. I love it! :D – Petr Cezar May 28 '11 at 19:46
    
@Petr: A fake table to hold all possible MAC addresses? Or insert into a "fake" table and then do an INSERT ... SELECT thing? – mu is too short May 29 '11 at 4:10
    
The latter one: insert into a fake table and then trigger that will move it into the real one. Or one more way I got told yesterday: "mac_text" varchar field in the same table and a trigger that will convert/move it into "mac" macaddr field. – Petr Cezar May 29 '11 at 6:54
    
@Petr: Sounds like a lot of pointless busy-work to me. Sure, you could do all those things but you'd still have people inserting directly into the table with the real macaddr column. Fixing the client applications to behave themselves still seems like the best solution to me. – mu is too short May 29 '11 at 17:48

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.