How do I insert into a table values from two arrays in Postgres?

Question

Supposing, I have a table:

create table mytable (
    "x" text,
    "y" text
);

And I have arrays with values for x and for y called arr_x and arr_y:

["x1", "x2", "x3"]
["y1", "y2", "y3"]

I want to insert these values with one query. The desired result is:

x  | y
-------
x1 | y1
x2 | y2
x3 | y3

I tried to do something like that, but it failed:

insert into mytable ("x", "y")
    select unnest(arr_x::text), unnest(arr_y::text);

Any idea how can I insert the values? I am new in Postgres.

Did you mean to cast to ::text[]?

teppic
– teppic

2016-12-23 05:29:25 +00:00
Commented Dec 23, 2016 at 5:29 — teppic
– teppic, Commented Dec 23, 2016 at 5:29

teppic · Accepted Answer · 2016-12-23 06:54:24Z

3

Your cast is wrong, you can't unnest text.

Try

INSERT INTO mytable (x, y)
  SELECT
    unnest('{x1, x2, x3}' :: TEXT []),
    unnest('{y1, y2, y3}' :: TEXT []);

Note that this form of select behaves oddly if both arrays aren't the same length.

The unnest function in postgres 9.4+ allows you to expand multiple arrays, using one array per output column:

INSERT INTO mytable
  SELECT *
  FROM unnest('{x1, x2, x3}' :: TEXT [], '{y1, y2, y3, y4}' :: TEXT [])

edited Dec 23, 2016 at 6:54

answered Dec 23, 2016 at 5:31

teppic

7,3161 gold badge34 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:33:44Z

You can try this.You can insert into multiple rows by using UNNEST along with SELECT. Refer this link insert with unnest function - skips a number in serial column

postgres=# CREATE TABLE mytable (
postgres(# ID SERIAL PRIMARY KEY,
postgres(# column1 VARCHAR(50),
postgres(# column2 VARCHAR(50));
CREATE TABLE
postgres=# INSERT into mytable(column1,column2) 
postgres-# SELECT UNNEST(ARRAY['x1','x2','x3']), UNNEST(ARRAY['Y1','Y2','Y3']);
INSERT 0 3
postgres=# TABLE mytable;
 id | column1 | column2 
----+---------+---------
  1 | x1      | Y1
  2 | x2      | Y2
  3 | x3      | Y3
(3 rows)

postgres=# INSERT into mytable(column1,column2) 
SELECT UNNEST(ARRAY['x4','x5']), UNNEST(ARRAY['Y4','Y5']);
INSERT 0 2
postgres=# TABLE mytable;
 id | column1 | column2 
----+---------+---------
  1 | x1      | Y1
  2 | x2      | Y2
  3 | x3      | Y3
  4 | x4      | Y4
  5 | x5      | Y5
(5 rows)

Hope this helps !!

Collectives™ on Stack Overflow

How do I insert into a table values from two arrays in Postgres?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related