From: | Igal Sapir <igal(at)lucee(dot)org> |
---|---|
To: | "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:43:40 |
Message-ID: | CA+zig088y41vDLOn+z1MGy0WgyD74SzAuyPPZvOq0ikXA5Cgjg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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;
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 | Jov | 2018-01-02 07:59:46 | Re: Returning Values from INSERT ON CONFLICT DO NOTHING |
Previous Message | Igal Sapir | 2018-01-02 07:07:46 | Returning Values from INSERT ON CONFLICT DO NOTHING |