PostgreSQL中对象名的大小写, 真的清楚了吗？

数据库杂记 2023-03-15

1、前言介绍

其实这篇内容蛮简单的，见到老有人问起，干脆整理一下。问题是这样的：

[postgres@xiongcc ~]$ psql -d Postgres
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  database "Postgres" does not exist
[postgres@xiongcc ~]$ psql -d POstgres
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  database "POstgres" does not exist
[postgres@xiongcc ~]$ psql -d postgres
psql (15.2)
Type "help" for help.
postgres=# \q
postgres=# select * from TEST limit 1;
   id   
--------
 250000
(1 row)
postgres=# select * from Test limit 1;
   id   
--------
 250000
(1 row)

复制

用psql直连时，参数里的dbname好像是区分大小写的。而到了psql命令行里头，表名之类的，就不区分大小写了。跟“自己”臆想的结论不一样，问问题的人可能印象里头，应该默认情况两都一样，都是不区分大小写的。事实上不是。

2. 分析

事实上呢，把psql客户端命令行程序，和psql工具命令行内的SQL语句分开就好。

只要是客户端程序，里边涉及到的对象名参数就是区分大小写的，无论你是否用''括起来。

请看示例1，建一个库ABC, 然后列举出来，再试图连abc，失败

[19:26:38-postgres@centos2:/var/lib/pgsql]$ createdb ABC;
[19:26:53-postgres@centos2:/var/lib/pgsql]$ psql -c '\l'
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 ABC       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 mydb      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgis   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | postgis=CTc/postgres
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(6 rows)
[19:27:15-postgres@centos2:/var/lib/pgsql]$ psql ABC -c '\quit'
[19:28:17-postgres@centos2:/var/lib/pgsql]$ psql abc -c '\quit'
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5555" failed: FATAL:  database "abc" does not exist

复制

接着我们再建一个库abc，我们能看到，它们是两个完全不同的库。

[19:28:22-postgres@centos2:/var/lib/pgsql]$ createdb abc;
[19:29:40-postgres@centos2:/var/lib/pgsql]$ psql abc -c '\quit'
[19:29:44-postgres@centos2:/var/lib/pgsql]$ psql -c '\l'
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 ABC       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 abc       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 mydb      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgis   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | postgis=CTc/postgres
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(7 rows)

复制

dropdb的命令效果也是一样的，不再缀述。

那么在psql命令行里头对这两个库是怎么区分的？

[19:29:55-postgres@centos2:/var/lib/pgsql]$ psql
psql (14.5)
Type "help" for help.
postgres=# \c abc
You are now connected to database "abc" as user "postgres".
abc=# \c ABC
You are now connected to database "ABC" as user "postgres".
ABC=# \c Abc
connection to server on socket "/var/run/postgresql/.s.PGSQL.5555" failed: FATAL:  database "Abc" does not exist
Previous connection kept
ABC=#

复制

发现使用元命令\c的时候，后边也是区分大小写的。

再来看看表名之类的：

ABC=# create table t(id int);
CREATE TABLE
ABC=# create table T(id int);
ERROR:  relation "t" already exists
ABC=# create table 'T'(id int);
ERROR:  syntax error at or near "'T'"
LINE 1: create table 'T'(id int);
                     ^
ABC=# create table "T"(id int);
CREATE TABLE
ABC=# \d
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | T    | table | postgres
 public | t    | table | postgres
(2 rows)

复制

要想区分大小写，就得加上""quote起来。就这么点要求。如果不加限制，你建起来的表或其它对象，最后保存的时候都是小写形式。

ABC=# create table TestAbc(Id int, col2 varchar(32));
CREATE TABLE
ABC=# \d
          List of relations
 Schema |  Name   | Type  |  Owner
--------+---------+-------+----------
 public | T       | table | postgres
 public | t       | table | postgres
 public | testabc | table | postgres
(3 rows)

复制

建表的时候用的是TestAbc, 查出来的是testabc。

以建database为例，也很明显，默认情况不区分大小写的。SQL执行的时候。

ABC=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# create database ABCD;
CREATE DATABASE
postgres=# create database ABcd;
ERROR:  database "abcd" already exists
postgres=#

复制

上边只是以database, table为例，其它对象，如用户名之类的，也都一样的规则。

3. 小结一下

写的挺啰嗦的，但是平时也确实容易让人犯错。如果你想把PG里头的应用代码变得尽量通用，可移植尽量强些，那就尽量全部不用""括起来，用默认的。全让它变成内置的小写好了。

有些人为了让代码“好看”，搞的表名字段名不仅括起来，还要区分上大小写，弄起驼峰式的名字，到了移植的时候，估计想杀他的心都有。无端端的制造麻烦。为了支持这种大小写区分的migration, 移植工具要做很多额外的工作。

另外，对象名的长度，也可能是一场恶梦。记得Oracle老版本的30个字符的限制不? 可别为了好看，把对象名弄的长长的。不一定说是最终要可移植。有的应用程序，本来就要求同时支持多种数据库，那么你就只能取其公共的限制了。

比如你就干脆限制长度为最小值的那个。

对象名全局唯一，还是基于某一级对象下的唯一，也是问题。

mydb=# create table t2(id int);
CREATE TABLE
mydb=# create index idx_t11 on t1(id);
CREATE INDEX
mydb=# create index idx_t11 on t2(id);
ERROR:  relation "idx_t11" already exists

复制

index名在PG里头是要求全局唯一的。别的数据库好多都不是这样的。。。。

谈到可移植，又有人问：postgres里有类似有mysql的if（1=1，1，2）的函数吗?

结果是，还真没有。不过，网上有人加答了，
参考：https://qa.1r1g.com/sf/ask/869027841/
也一并帖在这里。

使用布尔参数,除非在函数被调用之前,除以零将始终抛出异常(这是一件好事).有没有什么可以做的.它已经发生了.

CREATE OR REPLACE FUNCTION if(boolean, anyelement, anyelement)
 RETURNS anyelement LANGUAGE SQL AS
$func$
SELECT CASE WHEN $1 THEN $2 ELSE $3 END
$func$;

复制

我强烈建议不要使用一个名为的函数if
.IF
是PL/pgSQL中的关键字.如果使用用PL/pgSQL编写的用户定义函数,这将非常混乱.

只需CASE
直接使用标准SQL表达式即可.

实际上这样弄等于改变了if关键字的原义。

下边是一个推荐的解决方案：

采用text
参数并使用动态SQL对其进行评估......

仔细看的话，需要注意SQL注入等安全风险。

我的建议基本上是函数名if改一下，然后注意一下使用安全。

[ END ]

复制

文章转载自数据库杂记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。