11

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
  • 2,751
  • 6
  • 23
  • 25

2 Answers2

22

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

a_horse_with_no_name
  • 440,273
  • 77
  • 685
  • 758
  • 3
    This seems to suggest otherwise: https://gist.github.com/wilmoore/812253#file-modify-column-xml – 1in9ui5t Jun 26 '15 at 17:14
  • 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

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
  • 396,305
  • 64
  • 779
  • 743
  • I agree: a check constraint is probably even better than an ENUM – a_horse_with_no_name Feb 27 '11 at 19:02
  • 1
    @a_horse_with_no_name: But a `boolean` is probably better than the `CHECK` provided that the client side can easily understand a `boolean` in the database. For the record: I love CHECK constraints almost as much as I love FKs; a highly constrained database is your friend. – mu is too short Feb 27 '11 at 20:21