Postgres Pro
Downloads
Home   >   mailing lists

Re: Returning Values from INSERT ON CONFLICT DO NOTHING - Mailing list pgsql-general

From Jov
Subject Re: Returning Values from INSERT ON CONFLICT DO NOTHING
Date
Msg-id CADyrUxMVOR-BjirpPptbFe4KeKuPuwoZaP7sigqe__CmLajkfg@mail.gmail.com
Whole thread Raw
In response to Re: Returning Values from INSERT ON CONFLICT DO NOTHING  (Igal Sapir <igal@lucee.org>)
Responses Re: Returning Values from INSERT ON CONFLICT DO NOTHING  (Igal Sapir <igal@lucee.org>)
List pgsql-general
Tree view
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@lucee.org>:
It seems that if I do a simple update it resolves my issue:

INSERT INTO users(email, name)
  VALUES('user@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 <igal@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('user@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


pgsql-general by date:

Previous
From: Igal Sapir
Date:
Subject: Re: Returning Values from INSERT ON CONFLICT DO NOTHING
Next
From: Durumdara
Date:
Subject: Select for update / deadlock possibility?