PostgreSQL UNNEST Function: A Detailed Guide
The UNNEST function in PostgreSQL is used to expand an array into a set of rows. This is particularly useful when dealing with arrays in a table column or when you want to normalize array data into tabular form for querying.
Syntax of UNNEST
UNNEST(array_expression)
Examples of Using UNNEST
Example 1: Basic UNNEST Usage
Code:
-- Expanding an array into rows
SELECT UNNEST(ARRAY['Apple', 'Banana', 'Cherry']);
Result:
unnest Apple Banana Cherry
Example 2: Using UNNEST with a Table Column
Code:
-- Creating a table with an array column
CREATE TABLE products (
id SERIAL PRIMARY KEY,
tags TEXT[]
);
-- Inserting data into the table
INSERT INTO products (tags) VALUES (ARRAY['Electronics', 'Sale']);
-- Expanding the array column into rows
SELECT id, UNNEST(tags) AS tag FROM products;
Result:
id tag 1 Electronics 1 Sale
Example 3: Using UNNEST with Multiple Arrays
Code:
-- Expanding two arrays into rows simultaneously
SELECT *
FROM UNNEST(ARRAY[1, 2, 3], ARRAY['A', 'B', 'C']) AS t(num, letter);
Result:
num letter 1 A 2 B 3 C
Explanation of code Examples:
1. Expanding Arrays: The UNNEST function breaks an array into individual rows, making it easier to process each element.
2. Array Columns in Tables: When working with array columns, UNNEST allows querying each array element as a separate row.
3. Multiple Arrays: When passing multiple arrays, PostgreSQL pairs elements from the arrays positionally.
Combining UNNEST with Other Functions
Filtering Unnested Rows:
Code:
-- Filtering rows from an array
SELECT UNNEST(ARRAY['Red', 'Green', 'Blue']) AS color
WHERE color = 'Green';
Result:
color Green
Using UNNEST in a Join
Code:
-- Joining an array with a table
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name TEXT
);
INSERT INTO categories (name) VALUES ('A'), ('B'), ('C');
-- Joining with an array
SELECT c.name, t.num
FROM categories c
JOIN UNNEST(ARRAY[10, 20, 30]) AS t(num) ON c.id = t.num / 10;
Result:
name Num A 10 B 20 C 30
Best Practices and Considerations
All PostgreSQL Questions, Answers, and Code Snippets Collection.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4