Postgres Pro
Downloads
Home   >   mailing lists

Re: Returning Values from INSERT ON CONFLICT DO NOTHING - Mailing list pgsql-general

From Igal Sapir
Subject Re: Returning Values from INSERT ON CONFLICT DO NOTHING
Date
Msg-id CA+zig088y41vDLOn+z1MGy0WgyD74SzAuyPPZvOq0ikXA5Cgjg@mail.gmail.com
Whole thread Raw
In response to Returning Values from INSERT ON CONFLICT DO NOTHING  (Igal Sapir <igal@lucee.org>)
Responses Re: Returning Values from INSERT ON CONFLICT DO NOTHING  (Jov <amutu@amutu.com>)
List pgsql-general
Tree view
It seems that if I do a simple update it resolves my issue:

INSERT INTO users(email, name)
  VALUES('user@domain.tld', 'User')
  ON CONFLICT (email) DO UPDATE 
    SET email = excluded.email  -- users.email works too, not sure if makes a difference
  RETURNING user_id, (xmax::text::int > 0) as existed;

But if anyone has a better solution then I'd love to hear it.

Thanks,


Igal

On Mon, Jan 1, 2018 at 11:07 PM, Igal Sapir <igal@lucee.org> wrote:
Hello,

I want to use the "UPSERT" syntax for returning an ID if it exists, or inserting a record and returning the new ID if it does not exist.

INSERT INTO users(email, name)
  VALUES('user@domain.tld', 'User')
  ON CONFLICT (email) DO NOTHING
  RETURNING user_id, (xmax::text::int > 0) as existed;

When an email address does not exist then it works fine and I get the new user_id, but when it does exist, I get no results at all.

How can I get the results in case of a CONFLICT?

Thanks,


Igal

pgsql-general by date:

Previous
From: Igal Sapir
Date:
Subject: Returning Values from INSERT ON CONFLICT DO NOTHING
Next
From: Jov
Date:
Subject: Re: Returning Values from INSERT ON CONFLICT DO NOTHING