On 11/21/2016 02:32 AM, Andreas Terrius wrote:
Is there any way to check whether the row already exists before checking
constraints ? I still want it to fail if it turns out to be a new row
(which would violate the not null constraint), but updates the row if it
already exists.
Since if that is not possible, I would need to do a query to determine
whether the row exists in the database which kinda eliminates the use of
upsert. (in this case, partial upsert).
Before UPSERT appeared in 9.5, folks came up of with alternate methods
of doing this. I would suggest searching on:
postgres upsert cte
You might be able to modify the examples to get what you want.
On Sun, Nov 20, 2016 at 3:57 AM, Tom Lane <t...@sss.pgh.pa.us
<mailto:t...@sss.pgh.pa.us>> wrote:
Adrian Klaver <adrian.kla...@aklaver.com
<mailto:adrian.kla...@aklaver.com>> writes:
> ... So looks like constraints are checked before you get to the ON
CONFLICT section.
Right. ON CONFLICT is a means for dealing with duplicate-key errors in
the specified (or inferred) unique index. It is *not* an all-purpose
error catcher. In the case at hand, the given INSERT request fails due
to not-null constraints that are unrelated to what the ON CONFLICT
clause
tests for.
regards, tom lane
--
Adrian Klaver
adrian.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general