enum data type for liquibase

Question

I'm currently working on a liquibase.xml file to create table table_a. One of my fields is <column name="state" type="ENUM('yes','no')"> I'm using postgresql as my DBMS. is there anything like enum data type? I've read in this like http://wiki.postgresql.org/wiki/Enum

that postgresql doesn't have such data type. CREATE TYPE function is used to create this data type. I still don't know how to make it in liquibase though.

Any suggestions?

Answer 1

Well of course PostgreSQL has an enum type (which is clearly documented in the link you have shown and the manual).

I don't think Liquibase "natively" supports enums for PostgreSQL, but you should be able to achieve it with a custom SQL:

<changeSet id="1" author="Arthur">
  <sql>CREATE TYPE my_state AS ENUM ('yes','no')</sql>
  <table name="foo">
    <column name="state" type="my_state"/>
  </table>
</changeSet>

For a simple yes/no column, I'd actually use the boolean type instead of an enum

Answer 2

An alternative to creating a new type would be a simple CHECK constraint on a varchar(3) column:

<changeSet id="1" author="X">
    <table name="t">
        <column name="c" type="varchar(3)"/>
    </table>
    <sql>ALTER TABLE t ADD CONSTRAINT check_yes_no CHECK (c = 'yes' OR c = 'no')</sql>
</changeSet>

That might play better with the client side, or not. I think boolean (as suggested by a_horse_with_no_name) would be a better call for this specific case: saying exactly what you mean usually works out better than the alternatives.