Postgresql insert parser error

[slieer]

Describe the bug A clear and concise description of what the bug is.

To Reproduce Steps to reproduce the behavior:

1. 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=?

2. Parsing this SQL using JSqlParser with this statements

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

[WenSteven]

It also appears in v4.1

[manticore-projects]

Greetings!

I do not think that the PostgreSQL specific ON CONFLICT ... DO ... clause is supported. You would need to amend the INSERT production and send a PR please.

[manticore-projects]

Technically this is an UPSERT, which is actually supported by JSQLParser: http://217.160.215.75:8080/jsqlformatter/_static/railroad_diagram.xhtml#Upsert

However, PostgreSQL does not seem to support UPSERT explicitly, but having these INSERT ... ON CONFLICT ... syntax instead, which is not supported by JSQLParser. Please implement and send a PR.

[manticore-projects]

INSERT ... ON DUPLICATE ... is supported as well: http://217.160.215.75:8080/jsqlformatter/_static/railroad_diagram.xhtml#Insert

So it all boils down to ammending the INSERT production and to allow the ON CONFLICT instead.

[WenSteven]

INSERT ... ON DUPLICATE ... is supported as well: http://217.160.215.75:8080/jsqlformatter/_static/railroad_diagram.xhtml#Insert

So it all boils down to ammending the INSERT production and to allow the ON CONFLICT instead.

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