zvonimir-dd opened a new issue, #2625:
URL: https://github.com/apache/datafusion-sqlparser-rs/issues/2625
### Summary
In PostgreSQL, an `ON CONFLICT` conflict target given as a column list may
end with `WHERE index_predicate`, which lets a partial unique index be inferred
as the arbiter. sqlparser rejects the predicate, in both the PostgreSQL and the
generic dialect.
This is the conflict *target's* `WHERE`. The action's `DO UPDATE SET ...
WHERE condition` is already supported (#733, #735).
### Reproduction (sqlparser 0.63.0)
```rust
use sqlparser::{dialect::PostgreSqlDialect, parser::Parser};
let sql = "INSERT INTO t (a, b) VALUES (1, 2) ON CONFLICT (a) WHERE b > 0 DO
NOTHING";
Parser::parse_sql(&PostgreSqlDialect {}, sql).unwrap();
```
```
sql parser error: Expected: DO, found: WHERE at Line: 1, Column: 52
```
`... ON CONFLICT (a) WHERE b > 0 DO UPDATE SET b = EXCLUDED.b` fails the
same way. Both run on PostgreSQL (checked on 16.15):
```sql
CREATE TABLE t (a int, b int);
CREATE UNIQUE INDEX ON t (a) WHERE b > 0;
INSERT INTO t (a, b) VALUES (1, 2) ON CONFLICT (a) WHERE b > 0 DO NOTHING;
INSERT INTO t (a, b) VALUES (1, 2) ON CONFLICT (a) WHERE b > 0 DO UPDATE SET
b = EXCLUDED.b;
```
### Grammar
From https://www.postgresql.org/docs/current/sql-insert.html:
```
ON CONFLICT [ conflict_target ] conflict_action
where conflict_target can be one of:
( { index_column_name | ( index_expression ) } [ COLLATE collation ] [
opclass ] [, ...] ) [ WHERE index_predicate ]
ON CONSTRAINT constraint_name
```
### Proposed change
An optional predicate expression on the column-list conflict target, parsed
after the parenthesized list and before `DO`, and printed in the same place.
Two possible shapes:
- a new field on `OnConflict`, e.g. `pub conflict_target_predicate:
Option<Expr>`; or
- `ConflictTarget::Columns` as a struct variant, `Columns { columns:
Vec<Ident>, predicate: Option<Expr> }`.
The struct variant would also leave room for the `( index_expression )` form
requested in #1977. This issue does not cover that form, nor `COLLATE` /
opclass. I'm happy to follow the maintainers' preference and send a PR.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
