On 5/2/19, Manuel Rigger <rigger.man...@gmail.com> wrote:
> Hi everyone,
>
> It seems that upsert does not take into account "NOT NULL" constraints. In
> the example below, I get an error "NOT NULL constraint failed: test.c0":
>
> CREATE TABLE test (c0 NOT NULL);
> INSERT INTO test(c0) VALUES (NULL) ON CONFLICT DO NOTHING;
>
> I would have expected that the second statement has the same effect as the
> following statement, which would also be confirmed by the docs [1]:
>
> INSERT OR IGNORE INTO test(c0) VALUES (NULL);
>
> The example seems to work for the UNIQUE and PRIMARY KEY constraints.

UPSERT is not standard SQL - it is a PostgreSQL extension that we have
attempted to replicate.  PostgreSQL behaves the same way in this test
(a fact that I have just now verified on sqlfiddle.com).  The ON
CONFLICT clause is only triggered by uniqueness constraints, not NOT
NULL constraints.

The UPSERT documentation says "The conflict target specifies a
specific uniqueness constraint that will trigger the upsert."  So it
does not explicitly say that UPSERT does not work for NOT NULL
constraints, but that is the implication.


-- 
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to