What is the fastest way to unwrap array into rows in PostgreSQL? For instance,
We have:
a
-
{1,2}
{2,3,4}
And we need:
b
-
1
2
2
3
4
I'm using:
select explode_array(a) as a from a_table;
where explode_array is:
create or replace function explode_array(in_array anyarray) returns setof anyelement as
$$
select ($1)[s] from generate_series(1,array_upper($1, 1)) as s;
$$
Is there any better way?
Use unnest. For example:
CREATE OR REPLACE FUNCTION test( p_test text[] )
RETURNS void AS
$BODY$
BEGIN
SELECT id FROM unnest( p_test ) AS id;
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE
COST 1;
unnest --> expand an array to a set of rows
unnest(ARRAY[1,2])
1
2
select unnest( '{1,2}'::int[] ) or select unnest( '{a,b}'::text[] ) will return the row columns with proper type ... more stuff here: postgresql.org/docs/9.4/static/functions-array.htmlIf you have a table users_to_articles like:
| user_id | articles |
|---|---|
| 1 | {1,2} |
| 2 | {6,2,7} |
And need to explode the articles array so to obtain:
| user_id | article_id |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 2 | 6 |
| 2 | 2 |
| 2 | 7 |
You could run something like that:
CREATE TABLE "users_to_articles" (
"user_id" SERIAL PRIMARY KEY,
"articles" INT[]
);
INSERT INTO "users_to_articles" ("articles")
VALUES ('{1,2}'), ('{6,2,7}');
SELECT
"user_id"
, "article_id"
FROM "users_to_articles", unnest("articles") AS "article_id";
Here is a working sqlfiddle:
http://www.sqlfiddle.com/#!17/c26742/1
You can use unnest() to unwrap an array into a set of rows.
For example, you can unwrap the array of INT[] type into a set of rows as shown below:
postgres=# SELECT unnest(ARRAY[1,2,3]::INT[]);
unnest
--------
1
2
3
(3 rows)
*Memos:
The type of unwrapped values are INT(INTEGER).
You can omit ::INT[], then the type of unwrapped values are still INT(INTEGER).
And, you can unwrap the array of VARCHAR[] type into a set of rows as shown below:
postgres=# SELECT unnest(ARRAY['John','David','Robert']::VARCHAR[]);
unnest
--------
John
David
Robert
(3 rows)
*Memos:
The type of unwrapped values are VARCHAR(CHARACTER VARYING).
You can omit ::VARCHAR[], then the type of unwrapped values are TEXT.
And, you can unwrap the array of RECORD[] type into a set of rows as shown below:
postgres=# SELECT unnest(ARRAY[ROW('John','Smith'),ROW('David','Miller')]);
unnest
----------------
(John,Smith)
(David,Miller)
(2 rows)
*Memos:
The type of unwrapped values are RECORD.
You must use ROW() instead of ::RECORD[]to create the array of RECORD[] type otherwise there is the error.
