Repository navigation
Postgres identifiers with double quotes not supported #1335
Description
Describe the bug
Postgres supports double-quotes in table in column name identifiers with CSV-escape syntax, but they are unable to parse using JSqlParser
To Reproduce
Steps to reproduce the behavior:
- Example SQL
-- CREATE TABLE "table""with""quotes" (
-- "column""with""quotes" varchar(64)
-- );
INSERT INTO "table""with""quotes" ("column""with""quotes")
VALUES ('1'), ('2'), ('3');
UPDATE "table""with""quotes" SET "column""with""quotes" = '1.0'
WHERE "column""with""quotes" = '1';
SELECT "column""with""quotes" FROM "table""with""quotes"
WHERE "column""with""quotes" IS NOT NULL;- Parsing this SQL using JSqlParser with this statements
- Exception
net.sf.jsqlparser.JSQLParserException: Encountered unexpected token: "\"quotes\"" <S_QUOTED_IDENTIFIER>
at line 1, column 26.
Was expecting one of:
"("
"SET"
"VALUES"
"WITH"
<K_SELECT>
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatements(CCJSqlParserUtil.java:215)
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatements(CCJSqlParserUtil.java:203)
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "\"quotes\"" <S_QUOTED_IDENTIFIER>
at line 1, column 26.
Was expecting one of:
"("
"SET"
"VALUES"
"WITH"
<K_SELECT>
at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:26917)
at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:26753)
at net.sf.jsqlparser.parser.CCJSqlParser.Insert(CCJSqlParser.java:2501)
at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:159)
at net.sf.jsqlparser.parser.CCJSqlParser.Statements(CCJSqlParser.java:547)
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatements(CCJSqlParserUtil.java:213)
... 26 more
Expected behavior
I expect JSqlParser to parse these statements and be able to process identifiers as unescaped ones.
System
- Database you are using: Postgres 9.6.2
- Java Version: 8.0.292-librca
- JSqlParser version: 4.1
Activity
manticore-projects commentedon Sep 9, 2021
Hi Anton,
unfortunately escaping/quoting is not very flexible in JSQLParser right now and this is unlikely to change soonest due to the nature of JavaCC and its way to define tokens.
Your best bet at the moment was to mangle your SQL with a Pre-processor, replacing your
""quotes with the JSQLParser compliant quotes and parse your statement.wumpz commentedon Sep 30, 2021
Or as always PRs are welcome or do you want to support in other ways?
sstock commentedon Feb 23, 2022
The PostgreSQL double quote behavior follows SQL 1992 section 5 Lexical elements (presumably newer versions are the same). I do not know if the CSV usage predates that of SQL or vice versa.
feat: Quoted Identifiers can contain double-quotes (PostgreSQL)
Assorted Fixes #8 (#1807)
Backslash Quotation of Single-Quote `'\\''` (#1813)