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
