postgresql查询自动将大写的名称转换为小写的案例
我就废话不多说了，大家还是直接看代码吧~
SELECTsum(aa)as"recordNumber"FROMtable SELECTsum(aa)asrecordNumberFROMtable
postgis查询字段是将字段字段转为小写，如果需要大写的字符，需要加双引号
补充：Postgresql中表名、列名、用户名大小写问题
注意：是双引号，单引号可能会被解析成普通字符，因而是不识别的字段
highgo=#createtable"ExChange"(idint); CREATETABLE highgo=#createtableExChange(idint); CREATETABLE highgo=#\d Listofrelations Schema|Name|Type|Owner ----------------+----------+-------+-------- oracle_catalog|dual|view|highgo public|ExChange|table|highgo public|exchange|table|highgo public|myt|table|highgo public|t1|table|highgo public|tran|table|highgo (6rows) highgo=#insertintoexchangevalues(1); INSERT01 highgo=#insertinto"ExChange"values(2); INSERT01 highgo=#select*FROMexchange; id ---- 1 (1row) highgo=#select*FROMExChange; id ---- 1 (1row) highgo=#select*FROM"ExChange"; id ---- 2 (1row) highgo=#insertintoExChangevalues(2); INSERT01 highgo=#select*FROM"ExChange"; id ---- 2 (1row) highgo=#select*FROMexchange; id ---- 1 2 (2rows)
>从上面可以看出，如果不加双引号，那么表名都会被转化为小写。如果想要大小写混用，需要添加双引号。
highgo=#createtableexchange(IDint,idint);
ERROR:42701:column"id"specifiedmorethanonce
highgo=#createtableexchange(IDint,nametext);
CREATETABLE
highgo=#selectidfromexchange;
id
----
(0rows)
highgo=#selectIDfromexchange;
id
----
(0rows)
highgo=#select"ID"fromexchange;
ERROR:42703:column"ID"doesnotexist
LINE1:select"ID"fromexchange;
highgo=#\dexchange
Table"public.exchange"
Column|Type|Modifiers
--------+---------+-----------
id|integer|
name|text|
highgo=#\du
Listofroles
Rolename|Attributes|Memberof
-----------+------------------------------------------------------------+-----------
aaa||{}
gpadmin|Superuser,Createrole,CreateDB|{}
highgo|Superuser,Createrole,CreateDB,Replication,BypassRLS|{}
replica|Replication|{}
highgo=#createtableAAA;
ERROR:42601:syntaxerroratornear";"
LINE1:createtableAAA;
^
highgo=#createuserAAA;
ERROR:42710:role"aaa"alreadyexists
highgo=#createuser"AAA";
CREATEROLE
highgo=#\du
Listofroles
Rolename|Attributes|Memberof
-----------+------------------------------------------------------------+-----------
AAA||{}
aaa||{}
gpadmin|Superuser,Createrole,CreateDB|{}
highgo|Superuser,Createrole,CreateDB,Replication,BypassRLS|{}
replica|Replication|{}
实验证明，字段与用户同样会被自动转化为小写，除非添加双引号。其实最好的办法就是全部用小写，这样才能尽量减少问题的出现。
以上为个人经验，希望能给大家一个参考，也希望大家多多支持毛票票。如有错误或未考虑完全的地方，望不吝赐教。