Re: Returning Values from INSERT ON CONFLICT DO NOTHING

On Mon, Jan 1, 2018 at 11:59 PM, Jov <amutu(at)amutu(dot)com> 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(at)lucee(dot)org>:
>
>> It seems that if I do a simple update it resolves my issue:
>>
>> INSERT INTO users(email, name)
>> VALUES('user(at)domain(dot)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;
>>
>
> ​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