Postgresql中DDL的双引号问题

org.jooq.exception.DataAccessException: SQL [insert into "public"."tb_purchase" ("id", "billId", "detid", "productid", "num", "price", "create_time", "update_time", "status") values (?, ?, ?, ?, ?, ?, cast(? as timestamp), cast(? as timestamp), ?)]; ERROR: column "billId" of relation "tb_purchase" does not exist
  Position: 57
        at org.jooq.impl.Tools.translate(Tools.java:1941)
        at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:659)
        at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:362)
        at org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:135)
        ... ...
        at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
        at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:802)
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1410)
        at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.lang.Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException: ERROR: column "billId" of relation "tb_purchase" does not exist
  Position: 57
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2310)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2023)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:217)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:421)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:166)
        at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:159)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute(NewProxyPreparedStatement.java:989)
        at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:194)
        at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:430)
        at org.jooq.impl.AbstractDMLQuery.execute(AbstractDMLQuery.java:334)
        at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:348)
        ... 101 more

查看数据库，这个表确实有这个字段，导出ddl，发现表名、字段名都有双引号，去掉双引号重新创建表，还是报错，最后发现是由于双引号里面的字符区分大小写。

PostgreSQL is following the SQL standard and in that case that means that identifiers (table names, column names, etc) are forced to lowercase, except when they are quoted. So when you create a table like this:

网站建设哪家好，找创新互联公司！专注于网页设计、网站建设、微信开发、小程序开发、集团企业网站建设等服务项目。为回馈新老客户创新互联还提供了大洼免费建站欢迎大家使用！

CREATE TABLE APP_USER ... 

you actually get a table app_user. You apparently did:

CREATE TABLE "APP_USER" ... 

and then you get a table "APP_USER".

In Spring, you specify a regular string for the table name, in capital letters, but that gets spliced into a query to the PostgreSQL server without quotes. You can check this by reading the PostgreSQL log files: it should show the query that Spring generated followed by the error at the top of your message.

Since you have very little control over how Spring constructs queries from entities, you are better off using SQL-standard lower-case identifiers

新闻标题：Postgresql中DDL的双引号问题
转载注明：http://cqcxhl.com/article/pdhsdd.html