Postgresql, bulk insert if row with some value not exists-CodePudding

I want to do bulk insert only if there is no matching email

I tried this, but I don't know how to add a where clause.

INSERT INTO user (
   'pk',
   'email'
)
SELECT UNNEST(ARRAY[10, 11, 12, 13, 14, 15, 16]),
      UNNEST(ARRAY['[email protected]', '[email protected]', .........])
WHERE something.......

CodePudding user response：

Sample 1:

with tbl(a,b) as (
select
    UNNEST(ARRAY[10, 11, 12]),
    UNNEST(ARRAY['[email protected]', '[email protected]', '[email protected]'])
)
insert into user (pk, email)
select a, b from tbl 
left join user on tbl.b = user.email 
where user.email is null

Sample 2:

with tbl(a,b) as (
select
    UNNEST(ARRAY[10, 11, 12]),
    UNNEST(ARRAY['[email protected]', '[email protected]', '[email protected]'])
)
insert into user (pk, email)
select a, b from tbl 
where not exists (select 1 from user where user.email = tbl.b)