fengzhi09 commented on PR #89: URL: https://github.com/apache/flink-connector-jdbc/pull/89#issuecomment-3664504004
## English Version **Title**: PostgresDialect fails to quote reserved keywords (e.g., `desc`) in column names, causing SQL syntax errors **Body**: I encountered an issue when using the Flink JDBC connector to write data into a PostgreSQL table that contains a column named `desc` — which is a reserved keyword in PostgreSQL. The operation fails with the following error: ``` ERROR: syntax error at or near "desc" Position: 49 ``` Upon investigation, I found that the generated SQL statement does **not** wrap the column name `desc` in double quotes (i.e., it should be `"desc"`). In PostgreSQL, reserved keywords must be quoted with double quotes to be used as identifiers. Looking at the source code, I noticed that `PostgresDialect` does **not** override the `quoteIdentifier` method like other dialects (e.g., MySQLDialect, OracleDialect). As a result, column names are inserted into SQL statements verbatim, leading to syntax errors when they conflict with reserved words. **Suggestion**: Please implement the `quoteIdentifier` method in `PostgresDialect` to properly quote all column names (or at least reserved keywords) using double quotes, ensuring compatibility with PostgreSQL’s identifier rules. Relevant code location: https://github.com/apache/flink/blob/master/flink-connectors/flink-connector-jdbc/src/main/java/org/apache/flink/connector/jdbc/dialect/PostgresDialect.java <img width="1850" height="844" alt="image" src="https://github.com/user-attachments/assets/c8907648-1f96-48fc-b30f-10686f6d7a56"; /> ## 中文版本 **标题**：PostgreSQL 方言未正确转义保留关键字字段（如 `desc`），导致 INSERT/UPDATE 语句语法错误 **正文**： 我在使用 Flink JDBC connector 写入 PostgreSQL 表时遇到了一个问题：当表中包含名为 `desc` 的列（PostgreSQL 保留关键字）时，写入操作失败，报错如下： ``` ERROR: syntax error at or near "desc" Position: 49 ``` 经排查，Flink 生成的 SQL 语句未对列名 `desc` 进行双引号转义（即应写作 `"desc"`），而 PostgreSQL 要求保留关键字必须用双引号引用才能作为标识符使用。 我查看了源码，发现 `PostgresDialect` 并未像其他数据库方言（如 MySQL、Oracle）那样重写 `quoteIdentifier` 方法来自动处理此类保留字。这导致在构建 INSERT 或 ON CONFLICT ... DO UPDATE 语句时，直接使用了原始列名，从而引发语法错误。 **建议**： 请为 `PostgresDialect` 实现 `quoteIdentifier` 方法，对所有列名统一使用双引号包裹（或至少对保留关键字进行转义），以确保与 PostgreSQL 的兼容性。 相关代码位置参考： https://github.com/apache/flink/blob/master/flink-connectors/flink-connector-jdbc/src/main/java/org/apache/flink/connector/jdbc/dialect/PostgresDialect.java <img width="1850" height="844" alt="image" src="https://github.com/user-attachments/assets/c8907648-1f96-48fc-b30f-10686f6d7a56"; /> --- -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
