Postgresql, bulk insert if row with some value not exists

Question

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

your example should work (with a couple modifications) -- could you share the error message that you see, in addition to the actual query that you're trying to do it with? — richyen
– richyen, Commented Oct 11, 2022 at 7:09

Ramin Faracov · Accepted Answer · 2022-10-11 07:42:01Z

1

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)

answered Oct 11, 2022 at 7:42

Ramin Faracov

3,3331 gold badge6 silver badges10 bronze badges

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

Collectives™ on Stack Overflow

Postgresql, bulk insert if row with some value not exists

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related