current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I want to convert a MySQL database to PostgreSQL.

In MySQL, ENUM data type allows by default empty fields, which seems not to work with PostgreSQL. Therefore, I can't cast from VARCHAR to ENUM with PostgreSQL because I have a lot of empty fields in my database that PgSQL does not allow.

What may I do ? Allow NULL and set empty fields to NULL ? Add an empty value in PostgreSQL's ENUM when creating the type (something like ENUM('A','B','C','')) ? Stop using this ugly and incongruous ENUM data type (and use a constraint on varchar instead or another table and a foreign key) ?

Thank you :)

user1527491

share|improve this question

1 Answer 1

up vote 3 down vote accepted

If you actually want to have empty string values in the enum, you should add them to the enum when creating the type, yes. Any database that accepts the empty string into an enum that does not explicitly allow an empty string is buggy, and if your application relies on it, that's buggy too :)

If the idea is that it means "unknown", then using NULL is also a good choice - from a pure model perspective, probably the cleaner one.

It comes down to the classic problem that some databases and systems consider null and the empty string to be the same thing, when they are clearly distinct values.

share|improve this answer
    
Thank you :) . Seems to be clear to me now. –  user1527491 Aug 17 '13 at 16:32

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.