Postgres: why do I need to quote column name in max()?

Question

So I encountered the following behaviour which surprised me. I first thought DateTime might be a postgres data type but what about BidOpen? Then there is the funny thing with the case of the column name in the error message. I almost feel this has to do with unquoted names being case insensitive. Why do I need to surround the column name in quotes for the query to work?

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)

The schema looks like this:

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)

Try: `select max(symbol_id) from fx.candle `. What do you get ? — Oto Shavadze, May 29 '19 at 12:57
I think this has to do with postgres being case insensitive when not quoting the name, whereas the column's name is in case. — s5s, May 29 '19 at 12:58
Similar: https://stackoverflow.com/questions/21796446/postgres-case-sensitivity — s5s, May 29 '19 at 12:59
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS — , May 29 '19 at 13:03

Tim Biegeleisen · Accepted Answer · 2019-05-29T12:59:47.253

My understanding is that Postgres is not case sensitive regarding column and table names unless you actually create them using double quotes in the beginning. If that be the case, then you would need to forever refer to them using double quotes, to ensure that the proper case literal is being used.

So, to avoid your current situation, you should also avoid creating column/table names in a case sensitive manner.

Your create table should look something like this:

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

Yup, just clicked in my head that this must be it – s5s May 29 '19 at 12:58 — s5s, May 29 '19 at 12:58

Postgres: why do I need to quote column name in max()?

1 Answers1