GeezerVison is showing github.com — Postgres identifiers with double quotes not supported · Issue #1335 · JSQLParser/JSqlParser
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 commented on 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 commented on Sep 30, 2021
Or as always PRs are welcome or do you want to support in other ways?
sstock commented on 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.
added a commit that references this issue
on May 18, 2023This issue will close once commit 73c55fd is merged into the 'master' branch.73c55fd added a commit that references this issue
on Jun 15, 2023This issue will close once commit a815f5f is merged into the 'master' branch.a815f5f added a commit that references this issue
on Jun 27, 2023This issue will close once commit c79dfff is merged into the 'master' branch.c79dfff
Metadata
Metadata
Development
No branches or pull requestsIssue actions