UPSERT on constraint name instead of conflict target

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

UPSERT on constraint name instead of conflict target

Jordan Owens
Hi,

In reviewing the documentation for UPSERT, it does not appear that it fully
supports the PostgreSQL syntax. PostgreSQL provides an option to use the
name of a constraint instead of a list of columns or index expressions.

For example:

CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT, d INT);
CREATE UNIQUE INDEX t1b ON t1(b);
INSERT INTO t1(a,b,c,d) VALUES(1,2,3,4);
SELECT * FROM t1;
INSERT INTO t1(a,b,c,d) VALUES(1,2,33,44)
ON CONFLICT ON CONSTRAINT t1b DO UPDATE SET c=excluded.c;

The last query results in a syntax error. Is it possible for SQLite to
support this option as well?

Thanks!
_______________________________________________
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 on constraint name instead of conflict target

Richard Hipp-3
The PostgreSQL documentation recommends against using the
constraint-name syntax.  I decided to encourage their recommendation
by not supporting that feature.

On 8/14/18, Jordan Owens <[hidden email]> wrote:

> Hi,
>
> In reviewing the documentation for UPSERT, it does not appear that it fully
> supports the PostgreSQL syntax. PostgreSQL provides an option to use the
> name of a constraint instead of a list of columns or index expressions.
>
> For example:
>
> CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT, d INT);
> CREATE UNIQUE INDEX t1b ON t1(b);
> INSERT INTO t1(a,b,c,d) VALUES(1,2,3,4);
> SELECT * FROM t1;
> INSERT INTO t1(a,b,c,d) VALUES(1,2,33,44)
> ON CONFLICT ON CONSTRAINT t1b DO UPDATE SET c=excluded.c;
>
> The last query results in a syntax error. Is it possible for SQLite to
> support this option as well?
>
> Thanks!
> _______________________________________________
> 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