CODEWITHSUNDEEP
sqlpostgresql
user3291025
user3291025

Reputation: 1117

How to upsert in postgresql

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

Upvotes: 0

Views: 61

Answers (1)

Mike Organek
Mike Organek

Reputation: 12494

You have syntax problems

  1. The email address has to be enclosed in quotes
  2. The ON CONFLICT should specify the conflict column or the name of the constraint
  3. There should be no WHERE clause
  4. 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;

Upvotes: 1

Related Questions