Search Postgresql Archives

Re: Returning Values from INSERT ON CONFLICT DO NOTHING

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Mon, Jan 1, 2018 at 11:59 PM, Jov <amutu@xxxxxxxxx> wrote:
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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux