How to Use Enumerated Data Types with PostgreSQL

Introduction

In the PostgreSQL database server, an enumerated (enum) type is a custom data type containing a list of permitted input values. You can map the PostgreSQL enum data types to a table using the CREATE TABLE command. Enum data types are suitable in scenarios where you want to move some validation logic to the database level and prevent users from inserting invalid data.

Also, only use the enum data types in a table column that requires a clear set of items (usually around ten) that rarely change (For instance, colors, continents, and product types). Otherwise, lookup tables with correctly linked foreign keys might be more appropriate.

This guide shows you how to use a managed PostgreSQL database cluster to define and use custom enum data types on Ubuntu 20.04.

Prerequisites

To test this guide:

• Set up an Ubuntu 20.04 server.

• Create a non-root sudo user.

• Deploy a managed PostgreSQL database cluster.

• Sign in to your Vultr account. Locate Products on the left side menu, then click Databases on the navigation menu. Click the name of your managed PostgreSQL database cluster under the Managed Database Name. Under the Overview tab, locate Connection Details. This guide uses the following sample connection details:

  • username: vultradmin

  • password: EXAMPLE_POSTGRESQL_PASSWORD

  • host: SAMPLE_POSTGRESQL_DB_HOST_STRING.vultrdb.com

  • port: 16751

1. Set Up a Sample Database

Before creating the enum data types, establish an SSH connection to your Linux server and execute the following steps to set up a sample database:

1. Install the postgresql-client package, a command-line client that allows you to establish a secure connection to your managed PostgreSQL database cluster.

    $ sudo apt update 
    $ sudo apt install -y postgresql-client

2. Use the PostgreSQL client (psql) to connect to the managed PostgreSQL cluster. Replace the values after -h, -p, -U with the correct host, port, and username from the managed database cluster.

    $ psql -h SAMPLE_POSTGRESQL_DB_HOST_STRING.vultrdb.com -p 16751 -U vultradmin defaultdb

   Output.

    Password for user vultradmin:

3. Enter the PostgreSQL database cluster's password and press Enter to proceed.

   Output.

    defaultdb=>

4. Run the following SQL command to set up a sample company_db database.

    defaultdb=> CREATE DATABASE company_db;

   Output.

    CREATE DATABASE

5. Connect to the new company_db.

    defaultdb=> \c company_db;

   Output.

    You are now connected to database "company_db" as user "vultradmin".

After setting up the database, proceed to the next step to create enum data types.

2. Create Sample Enum Data Types

In PostgreSQL, you should follow the syntax below to create an enum data type:

    => CREATE TYPE SAMPLE_ENUM_DATA_TYPE AS ENUM (SAMPLE_ENUM_VALUES);

The PostgreSQL enum command explained:

• SAMPLE_ENUM_DATA_TYPE: This is a unique enum data type name. The name must be descriptive enough to avoid confusion.

• SAMPLE_ENUM_VALUES: This is a comma-separated list containing quoted enum values (For instance, 'RED', 'BLUE', 'ORANGE').

After familiarizing yourself with the enum data type syntax, follow the steps below to create a few samples:

1. Create a product_type_enum data type to mark products as either PHYSICAL (tangible goods) or NON-PHYSICAL (services).

    company_db=> CREATE TYPE product_type_enum AS ENUM ('PHYSICAL', 'NON-PHYSICAL');

   Output.

    CREATE TYPE

2. Create an availability_enum data type to set products' availability. The ONLINE status shows that a product is only available from the company's website. The STOREFRONT status shows that the product is only available for pickups. The BOTH status shows that a product is available both on the website and in the company's physical store.

    company_db=> CREATE TYPE availability_enum AS ENUM ('ONLINE', 'STOREFRONT', 'BOTH');

   Output.

    ...
    CREATE TYPE

3. Ensure your product_type_enum list is in place.

    company_db=> SELECT UNNEST(enum_range(null::product_type_enum)) 
                 AS product_type_enum;

   Output.

     product_type_enum
    ----------------
     PHYSICAL
     NON-PHYSICAL
    (2 rows)

4. Run the following query to verify the availability_enum data type values.

    company_db=> SELECT UNNEST(enum_range(null::availability_enum)) 
                 AS availability_enum;

   Output.

      availability_enum
     --------------------
      ONLINE
      STOREFRONT
      BOTH
     (3 rows)

With the two sample custom enum data types in place, proceed to the next step to create a table.

3. Create Enum Type Columns and Query Data

This step shows you how to create a table using your custom enum data types, inserting data, and querying the table to confirm changes. In PostgreSQL, creating a table that uses some custom enum data types is not complicated. Use the following syntax.

    => CREATE TABLE SAMPLE_TABLE_NAME (             
           SAMPLE_COLUMN_NAME SAMPLE_ENUM_DATA_TYPE          
       );

1. Create a sample products table. Ensure the product_type and availability columns use the product_type_enum and availability_enum data types.

    company_db=> CREATE TABLE products (
                     product_id SERIAL PRIMARY KEY,
                     product_name VARCHAR(50),
                     sku VARCHAR(50),
                     product_type product_type_enum,
                     availability availability_enum,
                     retail_price FLOAT
                 );

   Output.

    CREATE TABLE

2. Insert sample data into the products table.

    company_db=> INSERT INTO products (product_name, sku, product_type, availability, retail_price) VALUES ('ORANGE JUICE', 'FNT123C', 'PHYSICAL', 'ONLINE', '23.55');
                 INSERT INTO products (product_name, sku, product_type, availability, retail_price) VALUES ('BLUE PEN', 'BP429D', 'PHYSICAL', 'BOTH', 0.85);
                 INSERT INTO products (product_name, sku, product_type, availability, retail_price) VALUES ('COMPUTER ANTIVIRUS', 'CMP12A', 'NON-PHYSICAL', 'ONLINE', 24.99);
                 INSERT INTO products (product_name, sku, product_type, availability, retail_price) VALUES ('FRESH KALES', 'FR127K', 'NON-PHYSICAL', 'STOREFRONT', 3.45);

   Output

    ...
    INSERT 0 1

3. Query the products table to ensure everything is in place.

    company_db=# SELECT
                     product_id,
                     product_name,
                     sku,
                     product_type, 
                     availability,
                     retail_price
                  FROM products; 

   Output.

     product_id |    product_name    |   sku   | product_type | availability | retail_price
    ------------+--------------------+---------+--------------+--------------+--------------
              1 | ORANGE JUICE       | FNT123C | PHYSICAL     | ONLINE       |        23.55
              2 | BLUE PEN           | BP429D  | PHYSICAL     | BOTH         |         0.85
              3 | COMPUTER ANTIVIRUS | CMP12A  | NON-PHYSICAL | ONLINE       |        24.99
              4 | FRESH KALES        | FR127K  | NON-PHYSICAL | STOREFRONT   |         3.45
    (4 rows)

4. Review the validation policy of the enum data types by trying to enter a record with an invalid product_type value.

    company_db=# INSERT INTO products (product_name, sku, product_type, availability, retail_price) VALUES ('MINERAL WATER', 'MN857P', 'WATER', 'STOREFRONT', 8.65);

   Output.

    ERROR:  invalid input value for enum product_type_enum: "WATER"
    LINE 1: ... retail_price) VALUES ('MINERAL WATER', 'MN857P', 'WATER', '... 

The table structure and records are now in place. Learn to sort and compare data records using the enum data type columns in the next step.

4. Sort and Compare PostgreSQL Enum Data

When sorting values by an enum type column, PostgreSQL considers the original order declared when you first defined the enum type values.

1. Query the products table and use the ...ORDER BY availability clause to see the behavior in action.

    company_db=# SELECT
                     product_id,
                     product_name,
                     sku,
                     product_type, 
                     availability,
                     retail_price
                 FROM products 
                 ORDER BY availability;

2. Verify the output below. As you can see, PostgreSQL sorts the availability column using the order that you defined when creating the availability_enum data type. That is, PostgreSQL doesn't order the records alphabetically.

     product_id |    product_name    |   sku   | product_type | availability | retail_price
    ------------+--------------------+---------+--------------+--------------+--------------
              1 | ORANGE JUICE       | FNT123C | PHYSICAL     | ONLINE       |        23.55
              3 | COMPUTER ANTIVIRUS | CMP12A  | NON-PHYSICAL | ONLINE       |        24.99
              4 | FRESH KALES        | FR127K  | NON-PHYSICAL | STOREFRONT   |         3.45
              2 | BLUE PEN           | BP429D  | PHYSICAL     | BOTH         |         0.85
    (4 rows)

3. Override the sort order behavior by casting the enum column to text when running the ORDER BY clause.

    company_db=# SELECT
                     product_id,
                     product_name,
                     sku,
                     product_type, 
                     availability,
                     retail_price
                 FROM products 
                 ORDER BY availability::text;

   Output.

     product_id |    product_name    |   sku   | product_type | availability | retail_price
    ------------+--------------------+---------+--------------+--------------+--------------
              2 | BLUE PEN           | BP429D  | PHYSICAL     | BOTH         |         0.85
              1 | ORANGE JUICE       | FNT123C | PHYSICAL     | ONLINE       |        23.55
              3 | COMPUTER ANTIVIRUS | CMP12A  | NON-PHYSICAL | ONLINE       |        24.99
              4 | FRESH KALES        | FR127K  | NON-PHYSICAL | STOREFRONT   |         3.45
    (4 rows)

4. Run the following query that uses a comparison operator (>) to list all products having a product_type enum type index that appears after the PHYSICAL value in the original product_type enum list.

    company_db=# SELECT
                     product_id,
                     product_name,
                     sku,
                     product_type, 
                     availability,
                     retail_price
                 FROM products 
                 WHERE product_type > 'PHYSICAL';

   Output.

     product_id |    product_name    |  sku   | product_type | availability | retail_price
    ------------+--------------------+--------+--------------+--------------+--------------
              3 | COMPUTER ANTIVIRUS | CMP12A | NON-PHYSICAL | ONLINE       |        24.99
              4 | FRESH KALES        | FR127K | NON-PHYSICAL | STOREFRONT   |         3.45
    (2 rows)

After sorting data by enum columns, proceed next to learn how to manage existing enum lists.

5. Managing Enum Types and Values

The PostgreSQL server provides several commands for managing the enum data types and linked values. Test the commands in the following sections.

5.1. Change An Enum Type Name

In the PostgreSQL server, you can change the name of the enum type in case a need arises by following the steps below:

1. Find the enum data types applied to your products table by running a describe (\d) table command.

    company_db=> \d products;

2. Note the enum type names under the Type column in the following output.

        Column    |         Type          | Collation | Nullable |                   Default
    --------------+-----------------------+-----------+----------+----------------------------------------------
     product_id   | integer               |           | not null | nextval('products_product_id_seq'::regclass)
     product_name | character varying(50) |           |          |
     sku          | character varying(50) |           |          |
     product_type | product_type_enum     |           |          |
     availability | availability_enum     |           |          |
     retail_price | double precision      |           |          |
    Indexes:
        "products_pkey" PRIMARY KEY, btree (product_id)

3. Run the following command to change the enum type name from product_type_enum to type_of_product_enum.

    company_db=> ALTER TYPE product_type_enum RENAME TO type_of_product_enum;

   Output.

    ALTER TYPE

4. Review the structure of the products table again to confirm the changes.

    company_db=> \d products;

   You've successfully changed the name of the product_type_enum to type_of_product_enum.

        Column    |         Type          | Collation | Nullable |                   Default
    --------------+-----------------------+-----------+----------+----------------------------------------------
     product_id   | integer               |           | not null | nextval('products_product_id_seq'::regclass)
     product_name | character varying(50) |           |          |
     sku          | character varying(50) |           |          |
     product_type | type_of_product_enum  |           |          |
     availability | availability_enum     |           |          |
     retail_price | double precision      |           |          |
    Indexes:
        "products_pkey" PRIMARY KEY, btree (product_id)

5.2. Add a New Value to An Existing Enum Data Type

To add a new value to an existing enum data type, use the syntax below.

    => ALTER TYPE SAMPLE_TYPE ADD VALUE 'SAMPLE_ENUM_VALUE';

1. Add a new DOWNLOAD value to the type_of_product_enum.

    company_db=# ALTER TYPE type_of_product_enum ADD VALUE 'DOWNLOAD';

   Output.

    ALTER TYPE

2. List product_type_enum values to verify the change.

    company_db=> SELECT UNNEST(enum_range(null::type_of_product_enum)) 
                 AS type_of_product_enum;

   Output.

     type_of_product_enum
    ----------------
     PHYSICAL
     NON-PHYSICAL
     DOWNLOAD
    (3 rows)

3. Specify the sort position when adding new enum type values in an existing list using the BEFORE or AFTER keywords as shown below.

    company_db=> ALTER TYPE type_of_product_enum ADD VALUE 'CONSUMABLE' AFTER 'PHYSICAL';
                 ALTER TYPE type_of_product_enum ADD VALUE 'EXPLOSIVES' BEFORE 'DOWNLOAD';

   Output.

    ...
    ALTER TYPE

4. Verify the changes.

    company_db=> SELECT UNNEST(enum_range(null::type_of_product_enum)) 
                 AS type_of_product_enum;

   Output.

     type_of_product_enum
    -------------------
     PHYSICAL
     CONSUMABLE
     NON-PHYSICAL
     EXPLOSIVES
     DOWNLOAD
    (5 rows)

5.3. Rename Enum Type Value

To rename an existing enum data type value, use the following syntax.

    => ALTER TYPE SAMPLE_TYPE ADD VALUE 'SAMPLE_ENUM_VALUE';

1. To change the 'DOWNLOAD' value to 'SOFTWARE DOWNLOAD' in the type_of_product_enum data type, run the following command.

    company_db=> ALTER TYPE type_of_product_enum RENAME VALUE 'DOWNLOAD' TO 'SOFTWARE DOWNLOAD';

   Output.

    ALTER TYPE

2. Verify the changes.

    company_db=> SELECT UNNEST(enum_range(null::type_of_product_enum)) 
                 AS type_of_product_enum;

   Output.

     type_of_product_enum
    -------------------
     PHYSICAL
     CONSUMABLE
     NON-PHYSICAL
     EXPLOSIVES
     SOFTWARE DOWNLOAD
    (5 rows)

5.4. Change Enum Type Sort Order and Delete Values

PostgreSQL doesn't allow you to change the sort order or delete enum type values from an existing list. The only workaround is dropping and creating the enum type. For instance, to rearrange the sort order of the availability_enum data type from ONLINE, STOREFRONT, BOTH to BOTH, ONLINE, STOREFRONT, follow the steps below:

1. Verify the active sort order from the availability_enum data type.

    company_db=# SELECT UNNEST(enum_range(null::availability_enum)) 
                 AS availability_enum;

   Output.

     availability_enum
    -------------------
     ONLINE
     STOREFRONT
     BOTH
    (3 rows)

2. Rename the active enum data type to a new name. You can use the _old suffix at the end of the enum type name to avoid confusion.

    company_db=# ALTER TYPE availability_enum RENAME TO availability_enum_old;

   Output.

    ALTER TYPE

3. Recreate a new availability_enum data type with your new order.

    company_db=# CREATE TYPE availability_enum AS ENUM ('BOTH', 'ONLINE', 'STOREFRONT');

   Output.

    CREATE TYPE

4. Map the new enum data type to the products table by casting the availability column text values to the new availability_enum data type.

    company_db=# ALTER TABLE products 
                 ALTER COLUMN availability TYPE availability_enum 
                 USING availability::text::availability_enum;

   Output.

    ALTER TABLE

5. Delete the old (availability_enum_old) enum data type.

    company_db=> DROP TYPE availability_enum_old;

   Output.

    DROP TYPE

6. Query the availability_enum data type to ensure the new sort order reflects your desired output.

    company_db=# SELECT UNNEST(enum_range(null::availability_enum)) 
                 AS availability_enum;

   Output.

     availability_enum
    -------------------
     BOTH
     ONLINE
     STOREFRONT
    (3 rows)

Conclusion

This guide shows you how to use a managed PostgreSQL database server enumerated data types on Ubuntu 20.04. In this guide, you have created a sample database, declared two custom enum data types, and applied them to a products table. You've also run some commands to insert data into the enum columns. Towards the end, you've learned how to add and rearrange the enum data type values.

Check out the following links to learn more about Vultr's managed databases:

• How to Use Vultr Managed Databases for PostgreSQL with NodeJS.

• How to Implement PostgreSQL Database Transactions with Python on Ubuntu 20.04.