PostgreSQL 建表大小写问题

今天被 PostgreSQL 耍了一下午,其实是个表名大小写问题,好再后来终于明白了; 今天应用有个需求,需要从生产库导部分数据到测试库,其中生产库中有几张表比较特殊,表名中含有大写字母和小写字母,然后通过 pg_dump 导表一直报表不存在,以下模拟下今天的情况, 这里表名用 TesT_1 代替。

以 postgis 用户登陆 postgis 库

1
psql -d postgis -U posgis

创建一张测试表 “TesT_1”

1
2
3
4
5
6
7
8
postgis=> create table TesT_1 as select * from pg_tables;  
SELECT

postgis=> \d
List of relations
Schema | Name | Type | Owner
---------+-------------------+-------+----------
postgis | test_1 | table | postgis

说明:奇怪,明明创建的表名为 TesT_1, PG默认转换为小写了。

再次创建测试表 TesT_1

1
2
3
4
5
6
7
8
9
10
11
postgis=> drop table test_1;  
DROP TABLE

postgis=> create table "TesT_1" as select * from pg_tables;
SELECT

postgis=> \d
List of relations
Schema | Name | Type | Owner
---------+-------------------+-------+----------
postgis | TesT_1 | table | postgis

表名加了双引号后,果然创建了张表名为 TesT_1的表,利用pg_dump 导出表 ,导出脚本如下,一直报表不存在;

1
2
[postgres@pgb dump]$ pg_dump -t TesT_1 -U postgis postgis > 1.out  
pg_dump: No matching tables were found

加上schema前缀也不行

1
2
[postgres@pgb dump]$ pg_dump -t postgis.TesT_1 postgis > 1.out  
pg_dump: No matching tables were found

后来psql连接到库

1
psql -d postgis -U posgis

查看库中 postgis有哪些表

1
2
3
4
5
postgis=> \d  
List of relations
Schema | Name | Type | Owner
---------+-------------------+-------+----------
postgis | TesT_1 | table | postgis

查看表结构,报表不存在

1
2
postgis-> \d Test_1;  
Did not find any relation named "Test_1".

表名加下双引号才行

1
2
3
4
5
6
7
8
9
10
11
postgis-> \d "TesT_1"  
Table "postgis.TesT_1"
Column | Type | Modifiers
-------------+---------+-----------
schemaname | name |
tablename | name |
tableowner | name |
tablespace | name |
hasindexes | boolean |
hasrules | boolean |
hastriggers | boolean |

从而想到,可能是PG默认只读小写,当遇到含有大写字母的表时,就读不到了;于是在 pb_dump中,表名加个双引号就行;

加双引号还不行

1
2
[postgres@pgb dump]$ pg_dump -t "TesT_1" -U postgis postgis > 1.out  
pg_dump: No matching tables were found

后来在师傅的提示下,加上转义符 才行

1
[postgres@pgb dump]$ pg_dump -t \"TesT_1\" -U postgis postgis > 1.out

PS : 到了这里,问题终于解决了,真是无语了,PG里表名会区分大小写,SQL语句会自动转换为小写。

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%