How to create enum in Redshift?

Question

I want to create an enum type on redshift but not able to.

CREATE TYPE status AS ENUM ('Yes', 'No');

The error is :

An error occurred when executing the SQL command:
CREATE TYPE status AS ENUM ('Yes', 'No')

ERROR: syntax error at or near "ENUM"
  Position: 23

CREATE TYPE status AS ENUM ('Yes', 'No')
                  .    ^
1 statement failed

The version I am using is:

PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.1885

Not able to figure out whether the query is wrong or not supported by this version?

Answer 1

As explained in the Redshift documentation, Redshift does not support enumerated types.

Unfortunately, Redshift doesn't support the two work-arounds that come to my mind: check constraints and foreign key constraints (where the values go in a reference table).

My advice is to create a reference table for the type and to use a foreign key reference. Even though Redshift does not enforce the constraint, at least the intention will be clear.