How to upsert in postgresql

Question

I have three columns in my table: id, email, and OAuthEmail. When a user, ([email protected]) signs up with an email I'd like to create a new record if the email is not in the table. If the user previously signed up with OAuthEmail (if OAuthEmail = [email protected]), I'd like to add [email protected] to the email column. I have tried the following -

INSERT INTO users ("email") 
  VALUES ([email protected]) 
ON CONFLICT (user."OAuthEmail" = [email protected]) 
  DO UPDATE SET (users.email = [email protected])
WHERE users."OAuthEmail" = [email protected];

I am getting a syntax error at "=" on the line - ON CONFLICT (users.email = user."OAuthEmail").

So which column should contain unique values: email or OAuthEmail? A few records of sample data and desired results would help clarifying your question. — GMB
– GMB, Commented Aug 18, 2020 at 21:14
Email and Oauth email can be the same values. If a user signs up with an email, then signs up again with a value that is the same OAuthEmail, then the OauthEmail should be added to the column instead of creating a new record. — user3291025
– user3291025, Commented Aug 18, 2020 at 21:54

Mike Organek · Accepted Answer · 2020-08-18 22:32:31Z

1

You have syntax problems

The email address has to be enclosed in quotes
The ON CONFLICT should specify the conflict column or the name of the constraint
There should be no WHERE clause
If you are going to set the email to the value that it already has, then change it to DO NOTHING

I hope you do have a UNIQUE constraint on the email column.

Also, You should not use mixed-case identifiers. That will give you headaches down the road.

with do_update as (
  update users
     set email = "OAuthEmail"
    from invars
   where "OAuthEmail" = '[email protected]'
     and email is null
)
insert into users (email)
select new_email 
  from invars
on conflict (email) do nothing;

edited Aug 18, 2020 at 22:32

answered Aug 18, 2020 at 21:21

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

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

3 Comments

user3291025 Over a year ago

If OAuthEmail is found, I'd like to add the email to the email field.

Mike Organek Over a year ago

@user3291025 I have modified the query to do what you ask. I am sorry that I did not read it through completely. This approach requires a UNIQUE constraint on the email column.

Mike Organek Over a year ago

@GMB When caffeine is gone, switch to bourbon. :-)

Collectives™ on Stack Overflow

How to upsert in postgresql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related