in Postgres, use array of values for INSERT

3
votes

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.

1
sqlpostgresql

1 Answers

2
votes

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