Postgres identifiers with double quotes not supported

[raipc]

trafficstars

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:

1. 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;

2. Parsing this SQL using JSqlParser with this statements
3. 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

[manticore-projects]

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]

Or as always PRs are welcome or do you want to support in other ways?

[sstock]

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.