乘风原创程序

    当前位置:首页>>数据库>>Mssql>>postgresql查询自动将大写的名称转换为小写的案例
  • postgresql查询自动将大写的名称转换为小写的案例
  • 2021/1/28 11:35:51
  • 我就废话不多说了,大家还是直接看代码吧~

     select sum(aa) as "recordnumber" from table
     select sum(aa) as recordnumber from table

    postgis查询字段是将字段字段转为小写,如果需要大写的字符,需要加双引号

    补充:postgresql中表名、列名、用户名大小写问题

    注意:是双引号,单引号可能会被解析成普通字符,因而是不识别的字段

    highgo=# create table "exchange" (id int);
    create table
    highgo=# create table exchange (id int);
    create table
    highgo=# \d
      list of relations
     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
    (6 rows) 
     
    highgo=# insert into exchange values (1);
    insert 0 1
    highgo=# insert into "exchange" values (2);
    insert 0 1
     
    highgo=# select * from exchange ;
     id
    ----
     1
    (1 row)
    highgo=# select * from exchange ;
     id
    ----
     1
    (1 row)
     
    highgo=# select * from "exchange" ;
     id
    ----
     2
    (1 row) 
     
    highgo=# insert into exchange values (2);
    insert 0 1
    highgo=# select * from "exchange" ;
     id
    ----
     2
    (1 row)
     
    highgo=# select * from exchange ;
     id
    ----
     1
     2
    (2 rows)

    > 从上面可以看出,如果不加双引号,那么表名都会被转化为小写。如果想要大小写混用,需要添加双引号。

    highgo=# create table exchange (id int,id int);
    error: 42701: column "id" specified more than once
    highgo=# create table exchange (id int,name text);
    create table
    highgo=# select id from exchange ;
     id
    ----
    (0 rows)
     
    highgo=# select id from exchange ;
     id
    ----
    (0 rows)
     
    highgo=# select "id" from exchange ;
    error: 42703: column "id" does not exist
    line 1: select "id" from exchange ;
     
    highgo=# \d exchange
     table "public.exchange"
     column | type | modifiers
    --------+---------+-----------
     id | integer |
     name | text |
     
    highgo=# \du
         list of roles
     role name |    attributes    | member of
    -----------+------------------------------------------------------------+-----------
     aaa |        | {}
     gpadmin | superuser, create role, create db    | {}
     highgo | superuser, create role, create db, replication, bypass rls | {}
     replica | replication      | {}
     
    highgo=# create table aaa;
    error: 42601: syntax error at or near ";"
    line 1: create table aaa;
       ^
    highgo=# create user aaa;
    error: 42710: role "aaa" already exists
    highgo=# create user "aaa";
    create role
    highgo=# \du
         list of roles
     role name |    attributes    | member of
    -----------+------------------------------------------------------------+-----------
     aaa |        | {}
     aaa |        | {}
     gpadmin | superuser, create role, create db    | {}
     highgo | superuser, create role, create db, replication, bypass rls | {}
     replica | replication      | {}
    

    实验证明,字段与用户同样会被自动转化为小写,除非添加双引号。 其实最好的办法就是全部用小写,这样才能尽量减少问题的出现。

    以上为个人经验,希望能给大家一个参考,也希望大家多多支持本教程网。如有错误或未考虑完全的地方,望不吝赐教。