Re: [sqlite] UPSERT clause does not work with "NOT NULL" constraint

2019-05-02 Thread Manuel Rigger
Great, thanks!

Best,
Manuel

On Thu, May 2, 2019 at 6:25 PM Richard Hipp  wrote:

> Documentation has been updated in an attempt to clarify when UPSERT
> applies and when it does not.
>
> On 5/2/19, Manuel Rigger  wrote:
> > Okay, thanks for the clarification!
> >
> > I think that this part of the documentation is ambiguous. The part of the
> > documentation that you quoted mentions a "conflict target", but there is
> no
> > conflict target in the example that I provided.  The documentation
> > continues by stating that a conflict target is not necessary and that "A
> DO
> > NOTHING upsert without a conflict target works the same as an INSERT OR
> > IGNORE." Would it maybe be helpful to update the documentation to
> > explicitly state that UPSERT does not apply to NOT NULL constraints, and
> > that apart from this case DO NOTHING works in the same way as INSERT OR
> > IGNORE?
> >
> > Best,
> > Manuel
> >
> > On Thu, May 2, 2019 at 5:38 PM Richard Hipp  wrote:
> >
> >> On 5/2/19, Manuel Rigger  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
> >>
> >
>
>
> --
> 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


Re: [sqlite] UPSERT clause does not work with "NOT NULL" constraint

2019-05-02 Thread Richard Hipp
Documentation has been updated in an attempt to clarify when UPSERT
applies and when it does not.

On 5/2/19, Manuel Rigger  wrote:
> Okay, thanks for the clarification!
>
> I think that this part of the documentation is ambiguous. The part of the
> documentation that you quoted mentions a "conflict target", but there is no
> conflict target in the example that I provided.  The documentation
> continues by stating that a conflict target is not necessary and that "A DO
> NOTHING upsert without a conflict target works the same as an INSERT OR
> IGNORE." Would it maybe be helpful to update the documentation to
> explicitly state that UPSERT does not apply to NOT NULL constraints, and
> that apart from this case DO NOTHING works in the same way as INSERT OR
> IGNORE?
>
> Best,
> Manuel
>
> On Thu, May 2, 2019 at 5:38 PM Richard Hipp  wrote:
>
>> On 5/2/19, Manuel Rigger  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
>>
>


-- 
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


Re: [sqlite] UPSERT clause does not work with "NOT NULL" constraint

2019-05-02 Thread Manuel Rigger
Okay, thanks for the clarification!

I think that this part of the documentation is ambiguous. The part of the
documentation that you quoted mentions a "conflict target", but there is no
conflict target in the example that I provided.  The documentation
continues by stating that a conflict target is not necessary and that "A DO
NOTHING upsert without a conflict target works the same as an INSERT OR
IGNORE." Would it maybe be helpful to update the documentation to
explicitly state that UPSERT does not apply to NOT NULL constraints, and
that apart from this case DO NOTHING works in the same way as INSERT OR
IGNORE?

Best,
Manuel

On Thu, May 2, 2019 at 5:38 PM Richard Hipp  wrote:

> On 5/2/19, Manuel Rigger  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


Re: [sqlite] UPSERT clause does not work with "NOT NULL" constraint

2019-05-02 Thread Richard Hipp
On 5/2/19, Manuel Rigger  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