Re: Returning Values from INSERT ON CONFLICT DO NOTHING

[Jov <amutu@xxxxxxxxx>]

From https://www.postgresql.org/docs/devel/static/sql-insert.html:

The optional RETURNING clause causes INSERT to compute and return value(s) based on each row actually inserted (or updated, if an ON CONFLICT DO UPDATE clause was used). This is primarily useful for obtaining values that were supplied by defaults, such as a serial sequence number. However, any _expression_ using the table's columns is allowed. The syntax of the RETURNING list is identical to that of the output list of SELECT. Only rows that were successfully inserted or updated will be returned. For example, if a row was locked but not updated because an ON CONFLICT DO UPDATE ... WHERE clause condition was not satisfied, the row will not be returned.​

do update will return values while do nothing will not.

2018-01-02 15:43 GMT+08:00 Igal Sapir <igal@xxxxxxxxx>:

It seems that if I do a simple update it resolves my issue:

INSERT INTO users(email, name)

  VALUES('user@xxxxxxxxxx', '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;

​Do not update email column because there is index on this column. It is better to update other non-index column for HOT update.​

 

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@xxxxxxxxx> 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@xxxxxxxxxx', '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