Search Postgresql Archives

Re: Partial update on an postgres upsert violates constraint

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 <tgl@xxxxxxxxxxxxx
<mailto:tgl@xxxxxxxxxxxxx>> wrote:

    Adrian Klaver <adrian.klaver@xxxxxxxxxxx
    <mailto:adrian.klaver@xxxxxxxxxxx>> 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.klaver@xxxxxxxxxxx


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux