Re: Returning Values from INSERT ON CONFLICT DO NOTHING

[Igal Sapir <igal@xxxxxxxxx>]

On Mon, Jan 1, 2018 at 11:59 PM, Jov <amutu@xxxxxxxxx> wrote:

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.

That explains it, thank you.

 

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.​

Makes sense, thanks again.

Igal