UPSERT available in pre-release

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

UPSERT available in pre-release

Richard Hipp-3
The latest pre-release snapshot [1] contains support for UPSERT
following the PostgreSQL syntax.  The documentation is still pending.
Nevertheless, early feedback is welcomed.  You can respond either to
this mailing list, or directly to me.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPSERT available in pre-release

Petite Abeille-2


> On Apr 19, 2018, at 12:29 PM, Richard Hipp <[hidden email]> wrote:
>
> The latest pre-release snapshot [1]

Link missing?

> contains support for UPSERT
> following the PostgreSQL syntax.
>  The documentation is still pending.
> Nevertheless, early feedback is welcomed.  You can respond either to
> this mailing list, or directly to me.

Postgres UPSERT?!?

Wouldn’t a standard ANSI MERGE be more appropriate?

https://en.wikipedia.org/wiki/Merge_(SQL)

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPSERT available in pre-release

Shevek
In reply to this post by Richard Hipp-3
Opinion: Of all the DBMS's UPSERT/MERGE semantics, postgresql's is the
least useful because it's very limited: It can only do a check against a
constraint, and the cost of evaluating that constraint has to be carried
by all other statements which mutate the table. Oracle/Teradata MERGE is
a far more useful semantics because it's defined more like a self-join,
where the constraint is specified in the statement, not the DBMS.

On 04/19/2018 11:29 AM, Richard Hipp wrote:
> The latest pre-release snapshot [1] contains support for UPSERT
> following the PostgreSQL syntax.  The documentation is still pending.
> Nevertheless, early feedback is welcomed.  You can respond either to
> this mailing list, or directly to me.
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPSERT available in pre-release

Richard Hipp-3
In reply to this post by Petite Abeille-2
On 4/19/18, Petite Abeille <[hidden email]> wrote:
>
>
>> On Apr 19, 2018, at 12:29 PM, Richard Hipp <[hidden email]> wrote:
>>
>> The latest pre-release snapshot [1]
>
> Link missing?

[1] https://sqlite.org/download.html

>
>> contains support for UPSERT
>> following the PostgreSQL syntax.
>>  The documentation is still pending.
>> Nevertheless, early feedback is welcomed.  You can respond either to
>> this mailing list, or directly to me.
>
> Postgres UPSERT?!?
>
> Wouldn’t a standard ANSI MERGE be more appropriate?
>

We are open to adding MERGE INTO at some point in the future.  But the
UPSERT syntax is both easier to understand and easier to implement,
and we prefer to follow PostgreSQL syntax whenever possible.  See
https://wiki.postgresql.org/wiki/UPSERT#SQL_MERGE_syntax for
PostgreSQL's rationale for rejecting MERGE.

MySQL also has UPSERT but no MERGE.  The MySQL UPSERT syntax is
similar, but omits the constraint-target clause following the ON
CONFLICT.  So if there are multiple uniqueness constraints, you never
know which one will receive the UPSERT in MySQL.  That seemed
problematic, so we dropped support for the MySQL syntax during
development.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPSERT available in pre-release

Petite Abeille-2


> On Apr 19, 2018, at 1:06 PM, Richard Hipp <[hidden email]> wrote:
>
> We are open to adding MERGE INTO at some point in the future.

Excellent!

>  But the UPSERT syntax is both easier to understand

Debatable.

> and easier to implement,

Possibly.

> and we prefer to follow PostgreSQL syntax whenever possible.  See
> https://wiki.postgresql.org/wiki/UPSERT#SQL_MERGE_syntax for
> PostgreSQL's rationale for rejecting MERGE.

Let’s agree to disagree on that long running opinion piece.

MERGE, as per SQL:2003 & SQL:2008 & co. is the way to go.

The situation remind me of the introduction of recursive common table expression (CTE) in SQLite, which at first you wanted to implement solely in terms of Oracle’s 'CONNECT BY’ syntax, but ultimately saw the benefit of embracing the full-fledged CTE syntax instead.

And I’m personally very grateful for that thought process which gifted SQLite a kickass CTE implementation. Thanks for that!

Hopefully, the UPSERT vs. MERGE conversation will move that way as well: MERGE FTW! :)


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPSERT available in pre-release

Quan Yong Zhai
In reply to this post by Richard Hipp-3
Dear Richard,

I modified the wordcount.c in SQLite/test directory, to use the new upsert command:

   INSERT INTO wordcount(word,cnt) VALUES(?1,1) ON CONFLICT(word) DO UPDATE SET cnt=cnt+1

Before:

   wordcount --all :memory: sqlite3.c

  2.406 wordcount --insert

  2.296 wordcount --insert --without-rowid

After:

wordcount --all :memory: sqlite3.c

  1.701 wordcount --insert

  3.547 wordcount --insert --without-rowid



As you can see, it’s very strangely ,in the table with rowid, the upsert improved a lot, but in the table without rowidd, it’s slower than the origin sql.





Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10



________________________________
From: sqlite-users <[hidden email]> on behalf of Richard Hipp <[hidden email]>
Sent: Thursday, April 19, 2018 6:29:55 PM
To: General Discussion of SQLite Database
Subject: [sqlite] UPSERT available in pre-release

The latest pre-release snapshot [1] contains support for UPSERT
following the PostgreSQL syntax.  The documentation is still pending.
Nevertheless, early feedback is welcomed.  You can respond either to
this mailing list, or directly to me.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPSERT available in pre-release

Richard Hipp-3
On 4/19/18, Quan Yong Zhai <[hidden email]> wrote:

> I modified the wordcount.c in SQLite/test directory, to use the new upsert
>
> Before:
>    wordcount --all :memory: sqlite3.c
>   2.406 wordcount --insert
>   2.296 wordcount --insert --without-rowid
>
> After:
> wordcount --all :memory: sqlite3.c
>   1.701 wordcount --insert
>   3.547 wordcount --insert --without-rowid
>
> As you can see, it’s very strangely ,in the table with rowid, the upsert
> improved a lot, but in the table without rowidd, it’s slower than the origin
> sql.

That's a good testing idea.  Thank you.  I will make a similar change
and investigate the cause of the slowdown, and hopefully fix the
problem.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPSERT available in pre-release

Richard Hipp-3
In reply to this post by Quan Yong Zhai
Please try again with the latest pre-release snapshot.

On 4/19/18, Quan Yong Zhai <[hidden email]> wrote:

> Dear Richard,
>
> I modified the wordcount.c in SQLite/test directory, to use the new upsert
> command:
>
>    INSERT INTO wordcount(word,cnt) VALUES(?1,1) ON CONFLICT(word) DO UPDATE
> SET cnt=cnt+1
>
> Before:
>
>    wordcount --all :memory: sqlite3.c
>
>   2.406 wordcount --insert
>
>   2.296 wordcount --insert --without-rowid
>
> After:
>
> wordcount --all :memory: sqlite3.c
>
>   1.701 wordcount --insert
>
>   3.547 wordcount --insert --without-rowid
>
>
>
> As you can see, it’s very strangely ,in the table with rowid, the upsert
> improved a lot, but in the table without rowidd, it’s slower than the origin
> sql.
>
>
>
>
>
> Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows
> 10
>
>
>
> ________________________________
> From: sqlite-users <[hidden email]> on behalf
> of Richard Hipp <[hidden email]>
> Sent: Thursday, April 19, 2018 6:29:55 PM
> To: General Discussion of SQLite Database
> Subject: [sqlite] UPSERT available in pre-release
>
> The latest pre-release snapshot [1] contains support for UPSERT
> following the PostgreSQL syntax.  The documentation is still pending.
> Nevertheless, early feedback is welcomed.  You can respond either to
> this mailing list, or directly to me.
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPSERT available in pre-release

Sylvain Pointeau
I full agree with Petite Abeille.
This upsert is quite limited, we can only insert one row on conflict update
one row.
Even so it is a big improvement versus the insert or replace, this is far
from the merge from the SQL standard where we can insert or update multiple
rows in one query.

I am already super mega happy to think about using this new upsert for
sure, but could you implement the merge from the SQL standard?

Best regards,
Sylvain
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPSERT available in pre-release

Quan Yong Zhai
In reply to this post by Richard Hipp-3
Perfect, That’s a big step of SQLite.

I think one of our projects will benefit of the new upsert.

Thanks a lot.



wordcount --all :memory: sqlite3.c

  2.422 wordcount --insert

  2.341 wordcount --insert --without-rowid

  3.610 wordcount --replace

  1.766 wordcount --replace --without-rowid

  1.594 wordcount --upsert

  1.625 wordcount --upsert --without-rowid

  2.171 wordcount --select

  2.281 wordcount --select --without-rowid

  2.423 wordcount --update

  2.391 wordcount --update --without-rowid

  0.375 wordcount --delete

  0.328 wordcount --delete --without-rowid

  0.372 wordcount --query

  0.328 wordcount --query --without-rowid

24.027 wordcount --all



Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10



________________________________
From: sqlite-users <[hidden email]> on behalf of Richard Hipp <[hidden email]>
Sent: Saturday, April 21, 2018 3:49:08 AM
To: SQLite mailing list
Subject: Re: [sqlite] UPSERT available in pre-release

Please try again with the latest pre-release snapshot.

On 4/19/18, Quan Yong Zhai <[hidden email]> wrote:

> Dear Richard,
>
> I modified the wordcount.c in SQLite/test directory, to use the new upsert
> command:
>
>    INSERT INTO wordcount(word,cnt) VALUES(?1,1) ON CONFLICT(word) DO UPDATE
> SET cnt=cnt+1
>
> Before:
>
>    wordcount --all :memory: sqlite3.c
>
>   2.406 wordcount --insert
>
>   2.296 wordcount --insert --without-rowid
>
> After:
>
> wordcount --all :memory: sqlite3.c
>
>   1.701 wordcount --insert
>
>   3.547 wordcount --insert --without-rowid
>
>
>
> As you can see, it’s very strangely ,in the table with rowid, the upsert
> improved a lot, but in the table without rowidd, it’s slower than the origin
> sql.
>
>
>
>
>
> Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows
> 10
>
>
>
> ________________________________
> From: sqlite-users <[hidden email]> on behalf
> of Richard Hipp <[hidden email]>
> Sent: Thursday, April 19, 2018 6:29:55 PM
> To: General Discussion of SQLite Database
> Subject: [sqlite] UPSERT available in pre-release
>
> The latest pre-release snapshot [1] contains support for UPSERT
> following the PostgreSQL syntax.  The documentation is still pending.
> Nevertheless, early feedback is welcomed.  You can respond either to
> this mailing list, or directly to me.
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users