18

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?

Ali Taha Ali Mahboub
  • 3,271
  • 6
  • 26
  • 25

2 Answers2

29

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

  • 3
    This seems to suggest otherwise: https://gist.github.com/wilmoore/812253#file-modify-column-xml – 1in9ui5t Jun 26 '15 at 17:14
  • 1
    Also it's good practice to provide a rollback command when doing custom sql in liquibase. http://www.liquibase.org/documentation/rollback.html – zudduz Nov 13 '17 at 18:42
  • 1
    @1in9ui5t it doesn't work!!! There's no "enum" type on Liquibase docs (https://docs.liquibase.com/change-types/community/add-column.html#Informa). – rios0rios0 Jun 11 '21 at 14:13
5

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.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • I agree: a check constraint is probably even better than an ENUM –  Feb 27 '11 at 19:02
  • 1
    You can use "checkConstraint" according to Liquibase docs: https://stackoverflow.com/questions/38315020/postgresql-check-constraint-in-liquibase – rios0rios0 Jun 11 '21 at 14:14