in Postgres, use array of values for INSERT

Question

have an array of values. I wish to INSERT each of them as records if it does not already exist. can this be done in one statement in SQL?

traditional javascript approach:

[4,5,8]forEach( x => queryParams(
   insert into t1( c1 ) values( $1 )
   where not exists( select 1 from t1 where c1 = $1`,
   [x]
);

what would be ideal is something like

queryParams(
 `some fancy SQL here`,
 [ `{${join[4,5,8]}}` ]
);

many reasons for this, including limiting the cost of server round trips and transactions.

Jason W · Accepted Answer · 2015-07-25 03:42:15Z

2

You can use a correlated sub-query to find the values that don't exist matching a condition:

INSERT INTO Records (X)
    SELECT X
    FROM unnest(ARRAY[4,5,8]) T (X)
    WHERE NOT EXISTS (SELECT * FROM Records WHERE X = T.X);

SQL Fiddle: http://sqlfiddle.com/#!15/e0334/29/0

Edited above to use unnest

edited Jul 25, 2015 at 3:42

answered Jul 25, 2015 at 2:52

Jason W

13.2k3 gold badges35 silver badges62 bronze badges

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

2 Comments

cc young Over a year ago

that works, but not very elegant, esp if the array is very long. would be nice to have one SQL statement where the array is an argument, rather than creating the statement anew with each array.

Jason W Over a year ago

You can also unnest an array like updated answer above.

Collectives™ on Stack Overflow

in Postgres, use array of values for INSERT

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related