In PostgreSQL, always use lowercase for object names (table names, column names)

Reason

  • In the database, uppercase and lowercase are distinguished when registering object names.
  • In SQL, when enclosed in double quotes (" “), the exact string is used, but if not enclosed, the name is converted to lowercase (apparently).
  • As a result, mismatches may occur.
testdb=> \d iris
                Table "public.iris"
    Column     |         Type          | Modifiers 
---------------+-----------------------+-----------
 Id            | integer               | not null
 SepalLengthCm | numeric(15,2)         | 
 SepalWidthCm  | numeric(15,2)         | 
 PetalLengthCm | numeric(15,2)         | 
 PetalWidthCm  | numeric(15,2)         | 
 Species       | character varying(20) | 
Indexes:
    "iris_pkey" PRIMARY KEY, btree ("Id")

testdb=> select * from iris where SepalLengthCm > 5.0; --<--- NG
ERROR:  column "sepallengthcm" does not exist
LINE 1: select * from iris where SepalLengthCm > 5.0;
                                 ^
HINT:  Perhaps you meant to reference the column "iris.SepalLengthCm".

testdb=> select * from iris where "iris.SepalLengthCm" > 5.0; --<--- NG
ERROR:  column "iris.SepalLengthCm" does not exist
LINE 1: select * from iris where "iris.SepalLengthCm" > 5.0;
                                 ^

testdb=> select * from iris where "SepalLengthCm" > 5.0; --<--- OK

testdb=> select * from iris where iris."SepalLengthCm" > 5.0; --<--- OK
my previous blog post

https://pumpkinpie-tea.blogspot.com/2016/06/postgresql.html