>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 <i...@lucee.org>: > It seems that if I do a simple update it resolves my issue: > > INSERT INTO users(email, name) > VALUES('u...@domain.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.​ > > 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 <i...@lucee.org> 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('u...@domain.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 >> > >