Mar 1, 2024 by Robert Gravelle

Arrays and Enums

PostgreSQL, renowned for its extensibility and versatility, offers several data types beyond the conventional integer and string. Among these are the array and enum, which empower developers with advanced data modeling capabilities. In this blog article, we'll be delving into these sophisticated data types, demonstrating their usage and benefits within the context of the free dvdrental sample database.

The Array Type

Arrays in PostgreSQL enable the storage of multiple values within a single database field. This capability proves invaluable in scenarios where dealing with lists or sets of data is essential. Let's consider a practical example. Suppose we want to store films along with the actors who appeared each film. We can utilize the array data type to achieve this efficiently. First, here are the statements to create and populate the new "films_with_actors" table:

CREATE TABLE films_with_actors (
    film_id SERIAL PRIMARY KEY,
    title VARCHAR(255),
    actors TEXT[]
);

INSERT INTO films_with_actors (title, actors) VALUES
('Inception', ARRAY['Leonardo DiCaprio', 'Joseph Gordon-Levitt']),
('The Shawshank Redemption', ARRAY['Tim Robbins', 'Morgan Freeman']);
    

In Navicat, we can create our table using the Table Designer:

Note that if we append square brackets "[]" to the text type Navicat will recognize it an Array type and add a "1" to the Dimension field upon saving the table, indicating that it is a one-dimensional array.

After creating the table, we will be able to add data to it. Be sure to enclose the Array values in curly braces "{}" to tell Navicat which values to include within each array:

In queries, we can refer to a specific Array element by appending the desired index within square brackets. Hence, "actors[1]" would fetch the first Array value:

The Enum Type

Short for "Enumerated", the Enum type allows developers to define a fixed set of possible values for a column. This enhances data integrity and clarity within the database schema. Let's illustrate this by adding a "rating" column to the "films_with_actors" table. We can define an enumerated type for movie ratings using the following DDL statement:

CREATE TYPE rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');
ALTER TABLE films_with_actors ADD COLUMN rating rating;
    

In Navicat, we can append the new column in the Table Designer by clicking the "Add Field" button above the column list. After we've created the rating Enum using the CREATE TYPE statement above, we can choose it by selecting the "(Type)" item from the Type drop-down and then choosing the rating item from the Object Type list:

The table rating column will now include a drop-down with our defined Enum values:

Conclusion

PostgreSQL's array and enum data types provide developers with powerful tools to model complex data structures efficiently. By leveraging these advanced features, developers can enhance data integrity, streamline queries, and build more robust database schemas. In next week's blog, we'll conclude our exploration of PostgreSQL's advanced data types with a look at the Range type. Offering a concise way to represent a range of values within a single database field, the Range type is highly useful in various domains, from temporal data to numeric intervals.

Looking for an easy-to-use graphical tool for PostgreSQL database development? Navicat 16 For PostgreSQL has got you covered. Click here to download the fully functioning application for a free 14 day trial!