Postgres：为什么我需要在max（）中引用列名？[重复]

Question

所以我遇到了以下让我惊讶的行为。我最初认为DateTime可能是postgres数据类型，但BidOpen呢？还有一个有趣的事情是错误消息中列名的情况。我几乎觉得这与未引用的名称不区分大小写有关。为什么我需要用引号包围列名才能查询工作？

mydatabase=# select max("DateTime") from fx.candle;
         max
---------------------
 2019-04-26 20:59:00
(1 row)

mydatabase=# select max(DateTime) from fx.candle;
ERROR:  column "datetime" does not exist
LINE 1: select max(DateTime) from fx.candle;
                   ^
HINT:  Perhaps you meant to reference the column "candle.DateTime".
mydatabase=# select max(BidOpen) from fx.candle;
ERROR:  column "bidopen" does not exist
LINE 1: select max(BidOpen) from fx.candle;
                   ^
HINT:  Perhaps you meant to reference the column "candle.BidOpen".
mydatabase=# select max("BidOpen") from fx.candle;
   max
---------
 125.816
(1 row)

模式如下所示：

mydatabase=# \d fx.candle;
                                        Table "fx.candle"
  Column   |            Type             |                            Modifiers
-----------+-----------------------------+-----------------------------------------------------------------
 id        | integer                     | not null default nextval('fx.candle_id_seq'::regclass)
 DateTime  | timestamp without time zone |
 BidOpen   | double precision            | not null
 BidHigh   | double precision            | not null
 BidLow    | double precision            | not null
 BidClose  | double precision            | not null
 AskOpen   | double precision            | not null
 AskHigh   | double precision            | not null
 AskLow    | double precision            | not null
 AskClose  | double precision            | not null
 symbol_id | integer                     |
Indexes:
    "candle_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "candle_symbol_id_fkey" FOREIGN KEY (symbol_id) REFERENCES fx.symbol(id)

Answer 1

我的理解是，Postgres对于列和表名不区分大小写，除非您在开始时使用双引号创建它们。如果是这样的话，那么您需要永远使用双引号引用它们，以确保使用正确的大小写文字。

因此，为了避免当前的情况，还应该避免以区分大小写的方式创建列/表名。

您的创建表应该如下所示：

create table fx.candle (
    id integer not null default nextval('fx.candle_id_seq'::regclass),
    ...
    datetime timestamp without time zone   -- NO quotes here; important!
    ...
)