Enum Arrays In PostgreSQL Explained

Enumerated types, a.k.a enums, represent a set of predefined values. For instance, you could represent programming languages with an enum.

CREATE TYPE programming_language AS ENUM ('java', 'ruby', 'javascript', 'php');
CREATE TABLE employees (
    # ...
    language programming_language
);

Attempting to enter a value that is not defined in the Enum set will result in an error.

INSERT INTO employees VALUES ('Ellie', 'rust');
# ERROR: invalid input value for enum programming_language: "rust"

Storing enum arrays

Certain databases such as PostgreSQL support the storage of enum arrays. That is, instead of being restricted to storing a single enum value in a single cell, you may store multiple. To enable this feature, make the following changes in the CREATE TABLE query.

CREATE TABLE employees (
    # ...
    languages programming_language ARRAY NOT NULL
);

In the database, arrays are represented by {} curly braces.

INSERT INTO employees VALUES ('Jack', '{java,javascript}');
INSERT INTO employees VALUES ('Stan', '{ruby}');

Likewise, attempting to insert an invalid enum value into the array will be caught by the database constraint.

INSERT INTO employees VALUES ('Ellie', '{rust, javascript}');
# ERROR: invalid input value for enum programming_language: "rust"

Updating enum arrays

You can update your array with the following command:

UPDATE employees SET languages[2]='php' WHERE developer='Jack';

This will insert php in 2^nd spot and overwrite any pre-existing values.