Repository navigation
Postgresql insert parser error #955
Description
Describe the bug
A clear and concise description of what the bug is.
To Reproduce
Steps to reproduce the behavior:
-
Example SQL
INSERT INTO tableName (id,xxx0,xxx1,xxx2,is_deleted,create_time,update_time) VALUES (?, ?, ?, ?, ?, ?, ?) on conflict(xxx0, xxx1) do update set xxx1=?, update_time=? -
Parsing this SQL using JSqlParser with this statements
-
Exception
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "conflict" <S_IDENTIFIER>
at line 3, column 12.
Was expecting:
"DUPLICATE"
at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:22439)
at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:22286)
at net.sf.jsqlparser.parser.CCJSqlParser.Insert(CCJSqlParser.java:1792)
at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:137)
at net.sf.jsqlparser.parser.CCJSqlParser.Statements(CCJSqlParser.java:479)
at DqmTest.parseStatements(DqmTest.java:51)
... 40 more
Caused by:
net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "conflict" <S_IDENTIFIER>
at line 3, column 12.
Was expecting:
"DUPLICATE"
at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:22439)
at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:22286)
at net.sf.jsqlparser.parser.CCJSqlParser.Insert(CCJSqlParser.java:1792)
at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:137)
at net.sf.jsqlparser.parser.CCJSqlParser.Statements(CCJSqlParser.java:479)
at DqmTest.parseStatements(DqmTest.java:51)
Expected behavior
A clear and concise description of what you expected to happen.
System
- Database you are using
- Java Version
java 11 - JSqlParser version
3.1
Activity
WenSteven commentedon Jul 21, 2021
It also appears in v4.1
manticore-projects commentedon Jul 21, 2021
Greetings!
I do not think that the PostgreSQL specific
ON CONFLICT ... DO ...clause is supported.You would need to amend the
INSERTproduction and send a PR please.manticore-projects commentedon Jul 21, 2021
Technically this is an
UPSERT, which is actually supported by JSQLParser: http://217.160.215.75:8080/jsqlformatter/_static/railroad_diagram.xhtml#UpsertHowever, PostgreSQL does not seem to support
UPSERTexplicitly, but having theseINSERT ... ON CONFLICT ...syntax instead, which is not supported by JSQLParser. Please implement and send a PR.manticore-projects commentedon Jul 21, 2021
INSERT ... ON DUPLICATE ...is supported as well: http://217.160.215.75:8080/jsqlformatter/_static/railroad_diagram.xhtml#InsertSo it all boils down to ammending the
INSERTproduction and to allow theON CONFLICTinstead.WenSteven commentedon Jul 21, 2021
Yeap,but the code not designed with design-patterns,I think parse sql with different db dialect should be rather than knead the code together
feat: `ConflictTarget` allows multiple `IndexColumnNames`
Refactor Parenthesed SelectBody and FromItem (#1754)