Returning Values from INSERT ON CONFLICT DO NOTHING

From: Igal Sapir <igal(at)lucee(dot)org>
To: "Psql_General (E-mail)" <pgsql-general(at)postgresql(dot)org>
Subject: Returning Values from INSERT ON CONFLICT DO NOTHING
Date: 2018-01-02 07:07:46
Message-ID: CA+zig0_Joi5+toujDMF0K80x6kLaXJuHDk0kmDRNkiK5oW-jpQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)domain(dot)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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Igal Sapir 2018-01-02 07:43:40 Re: Returning Values from INSERT ON CONFLICT DO NOTHING
Previous Message Paul Jungwirth 2018-01-01 18:54:45 Re: Use of Port 5433 with Postgresql 9.6