CODEWITHSUNDEEP
sqlpostgresql
Fomalhaut
Fomalhaut

Reputation: 9745

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

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.

Upvotes: 2

Views: 4446

Answers (2)

teppic
teppic

Reputation: 7286

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 [])

Upvotes: 3

Sai
Sai

Reputation: 46

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 !!

Upvotes: 3

Related Questions