What are Enums?

Enumerated types (Enums) are a special kind of data type that consist of a set of predefined values. It is common in many programming languages and it is supported in PostgreSQL as well. 

For example, let’s say we want to define the allowed type of credit cards for a user. We can create an enum data type where the allowed values will only be (visa, mastercard).

Enums are useful because 

• they provide data validation by restricting allowed values. 
• Allow customisation of the type system based on business rules.

Defining Enums in PostgreSQL

To use enums in PostgreSQL we just need to do 2 things. 

• Define the enum type.

CREATE TYPE card AS ENUM ('visa', 'mastercard', ‘amex’);

• Use the defined type in a table.

CREATE TABLE payment (
    name text,
    card_type card
);

Pretty easy right? Now when we insert data into the table, only the allowed card type can be stored. 

INSERT INTO payment VALUES ('grocery', 'visa');
INSERT INTO payment VALUES ('dinner', 'mastercard');
SELECT * FROM payment;
  name   | card_type  
---------+------------
 grocery | visa
 dinner  | mastercard
(2 rows)INSERT INTO payment VALUES ('dinner', 'maestro');
ERROR:  invalid input value for enum card: "maestro" 

We see that when we try to insert a user with a “maestro” card type, the query fails. Using enums in this way ensures data integrity. Internally, PostgreSQL creates a constraint check for the table column that we created an enum type for.

Enum column with multiple values.

So far, we have looked at creating and using enums on PostgreSQL columns that just allow a single value. 

If we have a business requirement that needs a user to be associated with more than one card type, e.g visa and amex, how can we pull that off using enums?

Introducing array

Array is another common data type in many programming languages. It is supported by PostgreSQL and has a lot of array functions that we can use to manage the array data type. 

Basically, an array is a list of zero or more values. By definition, array values are not unique or restricted to certain values. Usually, arrays are defined to hold primitive data types such as strings, and integers. However, they can also be used for custom data types as we will see with user defined enum types below.

Arrays and Enums

To be able to save multiple values on the card_type column, we need to change its definition. Let’s go ahead and just create a new table with an array of enum type as the card_type field.

CREATE TABLE payment_array (
    name text,
    card_type card ARRAY NOT NULL
);

Next, we can insert some values into the table rows. Now that the card_type column accepts an array, the insert query will be a  bit different from the one we have above.

INSERT INTO payment_array VALUES ('grocery', '{visa}');
INSERT INTO payment_array VALUES ('dinner', '{mastercard}');
SELECT * FROM payment_array;
  name   |  card_type   
---------+--------------
 grocery | {visa}
 dinner  | {mastercard}
(2 rows)

Notice that when inserting array types, the values are enclosed in curly braces {}. So far, the table still looks similar to our previous one right? Let’s try and add more card types and see what happens

UPDATE payment_array SET card_type[2]='amex' WHERE name='grocery';
SELECT * FROM payment_array;
  name   |  card_type   
---------+--------------
 grocery | {visa,amex}
 dinner  | {mastercard}
(2 rows)

We see that the table can now accept multiple entries. Pretty cool right? We were able to update the card_type for grocery payments to accept both visa and amex. 

Keep in mind that even though It allows an array type, the values that can be entered into that array is still restricted by the enum type. So if we try to insert a maestro card type. This is what happens

UPDATE payment_array SET card_type[2]='maestro' WHERE name='dinner';
ERROR:  invalid input value for enum card: "maestro"

So that is it. We have seen how to use enums in PostgreSQL for columns that require single values. Also we looked at using arrays with enums to provide data integrity for columns that require multiple inputs. I guess it is also fair to point out that sometimes, we can also achieve some of the functionality we have seen above by using check constraints in PostgreSQL. Check  out my article on how to use check constraints for more information.

Have any questions, want to share your thoughts or just say Hi? I’m always excited to connect! Follow me on Twitter or LinkedIn for more insights and discussions. If you’ve found this valuable, please consider sharing it on your social media. Your support through shares and follows means a lot to me! 

References:

PostgreSQL 12 documentation

• Enumerated Types
• Arrays