Learn how to use a MySQL ENUM Equivalent in PostgreSQL. Our MySQL Support team is here to help you with your questions and concerns.

MySQL ENUM Equivalent in PostgreSQL

Did you know that the ENUM data type lets us define a column to store one of a predefined set of values in MySQL?

Interestingly, PostgreSQL does not have a direct equivalent to the MySQL ENUM data type.

Fortunately, we can achieve the same result in PostgreSQL with one of these methods:

• Method 1: Using Check Constraints with VARCHAR or TEXT:

  We can use the VARCHAR or TEXT data types to store string values and then use constraints on the allowed values with check constraints.

  For example:
  CREATE TABLE example_table (
id serial PRIMARY KEY,
status VARCHAR(10) CHECK (status IN ('active', 'inactive'))
);

  Here, we create a VARCHAR column called status and use a check constraint to make sure that the values stored in the column are limited to ‘active’ or ‘inactive’. This gives us control over the allowed values. Additionally, it helps define custom sets of values.
• Method 2: Using a Separate Lookup Table:

  Alternatively, we can create a separate table with the allowed values and then set up a foreign key relationship between the main table and the lookup table. This is more flexible and extensible. In other words, it comes in handy while managing a dynamic set of values.

  CREATE TABLE status_lookup (
id serial PRIMARY KEY,
value VARCHAR(10) UNIQUE
);
INSERT INTO status_lookup (value) VALUES ('active'), ('inactive');
CREATE TABLE example_table (
id serial PRIMARY KEY,
status_id INT REFERENCES status_lookup(id)
);

  Here, we create a status_lookup table to hold the allowed values. The example_table has a foreign key reference to the status_lookup table, making sure that only valid values are stored in the status_id column.
• Method 3: Using ENUM in PostgreSQL:

  Although PostgreSQL does not have a native ENUM data type, it offers an ENUM type as an extension. However, it is not commonly used and is not as flexible as the other 2 methods.

  In order to use it, we have to install the enum extension and create an ENUM type:

  CREATE EXTENSION IF NOT EXISTS "enum";
CREATE TYPE status_enum AS ENUM ('active', 'inactive');
CREATE TABLE example_table (
id serial PRIMARY KEY,
status status_enum
);

  However, this PostgreSQL ENUM type may limit how much we can alter or extend the set of allowed values in the future.

Let us know in the comments which one of the above methods worked in your favor.

[Need assistance with a different issue? Our team is available 24/7.]

Conclusion

In brief, our Support Experts demonstrated 3 different ways to use MySQL ENUM Equivalent in PostgreSQL.