From: | Jov <amutu(at)amutu(dot)com> |
---|---|
To: | Igal Sapir <igal(at)lucee(dot)org> |
Cc: | "Psql_General (E-mail)" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Returning Values from INSERT ON CONFLICT DO NOTHING |
Date: | 2018-01-02 07:59:46 |
Message-ID: | CADyrUxMVOR-BjirpPptbFe4KeKuPuwoZaP7sigqe__CmLajkfg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(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.​
>
> 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(at)lucee(dot)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(at)domain(dot)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
>>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Durumdara | 2018-01-02 08:22:57 | Select for update / deadlock possibility? |
Previous Message | Igal Sapir | 2018-01-02 07:43:40 | Re: Returning Values from INSERT ON CONFLICT DO NOTHING |