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

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

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

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

Hint

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

Practice

1. What is the correct way to declare an integer array column named scores in a PostgreSQL table?

easy

A. scores integer[]

B. scores array(integer)

C. scores [integer]

D. scores int array

Solution

1. Step 1: Understand PostgreSQL array syntax

   PostgreSQL uses type[] to declare an array column, for example integer[].

2. Step 2: Match the syntax to the options

   Only scores integer[] uses the correct syntax scores integer[].

3. Final Answer:

   scores integer[] -> Option A

4. Quick Check:

   Array declaration = type[] [OK]

Hint: Use type[] to declare arrays in PostgreSQL [OK]

Common Mistakes:

• Using parentheses instead of square brackets
• Writing 'array(integer)' which is invalid syntax
• Trying to put brackets before the type

2. Which of the following is the correct syntax to insert an array of integers {10, 20, 30} into a column numbers in PostgreSQL?

easy

A. INSERT INTO table_name (numbers) VALUES ('{10,20,30}');

B. INSERT INTO table_name (numbers) VALUES (ARRAY(10, 20, 30));

C. INSERT INTO table_name (numbers) VALUES ([10, 20, 30]);

D. INSERT INTO table_name (numbers) VALUES (10, 20, 30);

Solution

1. Step 1: Know how to insert arrays in PostgreSQL

   Arrays are inserted as string literals with curly braces, like '{10,20,30}'.

2. Step 2: Check each option

   INSERT INTO table_name (numbers) VALUES ('{10,20,30}'); uses the correct string literal format. INSERT INTO table_name (numbers) VALUES ([10, 20, 30]); uses square brackets which is invalid. INSERT INTO table_name (numbers) VALUES (ARRAY(10, 20, 30)); uses wrong ARRAY syntax (should be ARRAY[10,20,30]). INSERT INTO table_name (numbers) VALUES (10, 20, 30); inserts separate values, not an array.

3. Final Answer:

   INSERT INTO table_name (numbers) VALUES ('{10,20,30}'); -> Option A

4. Quick Check:

   Array insert = string with curly braces [OK]

Hint: Insert arrays as strings with curly braces '{...}' [OK]

Common Mistakes:

• Using square brackets instead of curly braces
• Using ARRAY() with parentheses instead of ARRAY[]
• Inserting multiple values without array syntax

3. Given a table students with a column grades integer[], what will the query SELECT grades[2] FROM students WHERE id = 1; return if the row with id = 1 has grades = {85, 90, 78}?

medium

A. 85

B. NULL

C. 90

D. 78

Solution

1. Step 1: Understand PostgreSQL array indexing

   PostgreSQL arrays start at index 1, so grades[2] accesses the second element.

2. Step 2: Identify the second element in the array

   The array is {85, 90, 78}, so the second element is 90.

3. Final Answer:

   90 -> Option C

4. Quick Check:

   Array index 2 = 90 [OK]

Hint: PostgreSQL arrays start at 1, not 0 [OK]

Common Mistakes:

• Assuming array index starts at 0
• Confusing element positions
• Expecting entire array instead of single element

4. Consider the query: SELECT numbers[0] FROM data_table; where numbers is an integer array column. What is the likely result?

medium

A. Returns the first element of the array

B. Returns NULL because PostgreSQL arrays start at index 1

C. Syntax error due to invalid index

D. Returns the entire array

Solution

1. Step 1: Recall PostgreSQL array indexing rules

   PostgreSQL arrays start at index 1, so index 0 is invalid but does not cause syntax error.

2. Step 2: Understand behavior of accessing index 0

   Accessing index 0 returns NULL, not an error or the first element.

3. Final Answer:

   Returns NULL because PostgreSQL arrays start at index 1 -> Option B

4. Quick Check:

   Index 0 access = NULL [OK]

Hint: Index 0 in PostgreSQL arrays returns NULL, not error [OK]

Common Mistakes:

• Expecting index 0 to return first element
• Thinking it causes syntax error
• Assuming it returns entire array

5. You have a table orders with a column item_ids integer[]. How would you write a query to find all orders that include the item ID 42 anywhere in the array?

hard

A. SELECT * FROM orders WHERE item_ids[42] IS NOT NULL;

B. SELECT * FROM orders WHERE item_ids[1] = 42;

C. SELECT * FROM orders WHERE ARRAY[42] @> item_ids;

D. SELECT * FROM orders WHERE 42 = ANY(item_ids);

Solution

1. Step 1: Understand how to check if an array contains a value

   PostgreSQL provides the ANY() operator to check if a value exists in an array.

2. Step 2: Analyze each option

   SELECT * FROM orders WHERE 42 = ANY(item_ids); correctly uses 42 = ANY(item_ids). SELECT * FROM orders WHERE item_ids[42] IS NOT NULL; incorrectly tries to access index 42. SELECT * FROM orders WHERE ARRAY[42] @> item_ids; uses @> which checks if array contains another array, but syntax is reversed (should be item_ids @> ARRAY[42]). SELECT * FROM orders WHERE item_ids[1] = 42; only checks first element.

3. Final Answer:

   SELECT * FROM orders WHERE 42 = ANY(item_ids); -> Option D

4. Quick Check:

   Use ANY() to check value in array [OK]

Hint: Use value = ANY(array) to check membership [OK]

Common Mistakes:

• Using wrong operator like @> with reversed operands
• Checking only first element instead of whole array
• Trying to access array at index equal to value