Array data type in PostgreSQL - Mini Project: Build & Apply

Working with Array Data Type in PostgreSQL

📖 Scenario: You are managing a small library database. Each book can have multiple authors. You want to store the authors' names in a single column using PostgreSQL's array data type.

🎯 Goal: Create a table with an array column to store multiple authors for each book. Then, insert data, configure a query to filter books by author count, and finally select books with more than one author.

📋 What You'll Learn

Create a table named books with columns id (integer) and authors (text array).

Insert three books with exact authors arrays: one with one author, one with two authors, and one with three authors.

Create a variable or use a query condition to filter books with more than one author.

Write a SELECT query to retrieve id and authors of books having more than one author.

💡 Why This Matters

🌍 Real World

Storing multiple values like authors, tags, or categories in a single column is common in real-world databases. Arrays help keep related data together without creating extra tables.

💼 Career

Understanding array data types and how to query them is useful for database developers and analysts working with PostgreSQL or similar systems that support arrays.

1

Create the books table with an array column

Write a SQL statement to create a table called books with two columns: id as an integer primary key, and authors as a text array.

PostgreSQL

-- Create the books table with id and authors array columns
-- Your code here

Need a hint?

Use TEXT[] to define an array of text in PostgreSQL.

2

Insert books with exact authors arrays

Insert three rows into books with these exact values: (1, ARRAY['Alice']), (2, ARRAY['Bob', 'Carol']), and (3, ARRAY['Dave', 'Eve', 'Frank']).

PostgreSQL

-- Insert three books with authors arrays
-- Your code here

Need a hint?

Use ARRAY[...] syntax to insert arrays in PostgreSQL.

3

Set a condition to filter books with more than one author

Write a SQL WHERE condition using array_length(authors, 1) > 1 to filter books having more than one author.

PostgreSQL

-- Define a WHERE condition to filter books with more than one author
-- Your code here

Need a hint?

Use array_length(column, 1) to get the size of the first dimension of the array.

4

Select books with more than one author

Write a SELECT query to retrieve id and authors from books where the number of authors is more than one using array_length(authors, 1) > 1.

PostgreSQL

-- Select id and authors from books where authors count is more than one
-- Your code here

Need a hint?

Use a SELECT statement with a WHERE clause filtering by array length.