浅谈PostgreSQL中大小写不敏感问题

本文主要讨论PostgreSQL中大小写不敏感存在的问题。

默认情况下,PostgreSQL会将列名和表名全部转换为小写状态。

图1 Person与person

如图1所示,我们创建表person,其中包含name列。然后插入一条记录。执行SELECT查询时,使用列名Name和表名Person而不是name和person,发现仍然可以正常获取刚刚插入表person中的记录。

图2 创建表Person?

此时如果我们再想创建表Person,会得到一个错误,因为此时PostgreSQL实际上把表名从Person转换成了person。由于已经存在表person,所以会报错。

通常情况下,这种大小写不敏感是很方便的,但是当我们想创建大小写敏感的表名和列名(需要使用双引号)时,会产生一些问题。

图3 创建表Person

如图3所示,我们成功创建了表Person,并插入了一条记录,此条记录和插入person中的不同以示区分。再次使用SELECT查询,并且使用表名Person和列名Name,但是返回的结果却是person中的记录。这还是因为PostgreSQL将Person转换成了person。所以想要正确查询,需要使用“Person”和“Name”(如图4所示)。

图4 获取表Person中的记录

此时查看数据库中的表(见图5),可以发现Person和person这两个表都在数据库中。如果我们使用DROP TABLE Person,删除的仍然是表person。

图5 删除操作

综上所述,当创建表或者写SQL查询语句时,建议避免使用双引号。

补充:PostgreSQL大小写不敏感排序

pg12开始支持不区分大小写,或者区分大小写的排序的collate。

语法:

CREATE COLLATION [ IF NOT EXISTS ] name (
  [ LOCALE = locale, ]
  [ LC_COLLATE = lc_collate, ]
  [ LC_CTYPE = lc_ctype, ]
  [ PROVIDER = provider, ]
  [ DETERMINISTIC = boolean, ]
  [ VERSION = version ]
)
CREATE COLLATION [ IF NOT EXISTS ] name FROM existing_collation

其中两个关键参数:

PROVIDER:指定用于与此排序规则相关的区域服务的提供程序。可能的值是: icu、libc。 默认 是libc。但若要设置大小写不敏感,目前只支持icu。

DETERMINISTIC:设置成not deterministic表示大小写不敏感。

例子:

—正常情况的排序

我们可以看到,正常的order by会区分大小写。

bill@bill=>create table test (c1 text);
CREATE TABLE
bill@bill=>insert into test values ('a'),('b'),('c'),('A'),('B'),('C');
INSERT 0 6
bill@bill=>select * from test order by c1;
 c1
----
 A
 B
 C
 a
 b
 c
(6 rows)

同样,在oracle中也是一样:

SQL> select * from test order by c1;

C1
--------------------------------------------------------------------------------
A
B
C
a
b
c

6 rows selected.

—不区分大小写排序

可以看到我们指定collate为zh_CN时便没有区分大小写排序。

bill@bill=>select * from test order by c1 collate "zh_CN";
 c1
----
 a
 A
 b
 B
 c
 C
(6 rows)

我们也可以自定义collation支持不区分大小写的排序,但是需要注意在编译数据库的时候加上 —with-icu才可以,否则会出现报错:

bill@bill=>CREATE COLLATION case_insensitive (provider = icu, locale = 'zh_Hans', deterministic = false);
ERROR: ICU is not supported in this build
HINT: You need to rebuild PostgreSQL using --with-icu.

正常情况:

bill@bill=> CREATE COLLATION case_insensitive (provider = icu, locale = 'zh_Hans', deterministic = false);
CREATE COLLATION
bill@bill=> select * from test order by c1 collate "case_insensitive";
 c1
----
 a
 A
 b
 B
 c
 C
(6 rows)

目前collate不支持=操作不区分大小写,目前需要citext插件。

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

(0)

相关推荐

  • 解决PostgreSQL日志信息占用磁盘过大的问题

    当PostgreSQL启用日志时,若postgresql.conf日志的相关参数还使用默认值的话磁盘很容易被撑爆.因此在启用了logging_collector参数时,需要对其它相关的参数进行调整. 系统默认参数如下 #log_destination = 'stderr' #日志格式,值为stderr, csvlog, syslog, and eventlog之一. logging_collector = on #启用日志 #log_directory = 'log' #日志文件存储目录 #lo

  • PostgreSQL pg_archivecleanup与清理archivelog的操作

    pg_archivecleanup 和 pg_rewind 是PG 中两个重要的功能,一个是为了清理过期的 archive log 使用的命令,另一个是你可以理解为物理级别的 wal log的搬运工. 我们先说第一个 pg_archivecleanup 命令,这个命令主要是用于使用了archive log 功能的 postgresql 但在 archive log 堆积如山的情况下,你怎么来根据某些规则,清理这些日志呢? 这里面就要使用 pg_archivecleanup 这个命令了,可以定时的

  • PostgreSQL LIKE 大小写实例

    PostgreSQL 数据库 函数upper("字符串"):转成大写字符串 WHERE UPPER("User_Name") LIKE upper(username) 此句查询"User_Name" 中值大小写不区分. SELECT "User_Id","User_Image","User_Name","User_Birthday","User_Sex&qu

  • PostgreSQL归档配置及自动清理归档日志的操作

    在一般的生产环境中,数据库都需要开启归档模式,那么在pg中如何开启归档模式呢? pg中的归档配置涉及几个参数如下: # - Archiving - 是否开启归档 #archive_mode = off # enables archiving; off, on, or always # (change requires restart) 归档命令,注意 %p %f %% 格式化的含义. %p 是被归档的redo文件的路径, %f 是被归档的redo文档的文件名 %% 是百分号 #archive_c

  • PostgreSQL 实现distinct关键字给单独的几列去重

    PostgreSQL去重问题一直困扰着我,distinct和group by远不如MySQL用起来随便,但是如果掌握了规律,还是和MySQL差不多的 主要介绍的是distinct关键字 select distinct id,name,sex,age from student 假如有一张student表,字段如上图,我查询student表中所有信息用distinct去重(上面的SQL语句),pgsql就会根据所有的字段通过算法取得重复行的第一行,但是很明显,ID这个字段我在设计的时候不会让它重复,

  • PostgreSQL 实现列转行问题

    1 测试表数据 SELECT relative_label_content FROM frk_s.label_cor_gene relative_label_content ------ AA BB CC 2 列转行写法 写法1: string_agg SELECT frwybs, string_agg (relative_label_content, ',') as relative_label_content FROM frk_s.label_cor_gene GROUP BY frwybs

  • Postgresql 如何清理WAL日志

    WAL是Write Ahead Log的简写,和oracle的redo日志类似,存放在$PGDATA/pg_xlog中,10版本以后在$PGDATA/pg_wal目录. 如果开启了归档,在目录archive_status下会有一些文件,以ready结尾的,表示可以归档但还没有归档,done结尾的表示已经归档. 和WAL日志数量相关的几个参数: wal_keep_segments = 300 # in logfile segments, 16MB each; 0 disables checkpoi

  • postgresql连续归档及时间点恢复的操作

    简介 前面我们介绍了通过pgsql的流复制在生产环境中搭建高可用环境来保证服务的可持续性:我们也要对数据库进行周期备份,来防止数据的丢失,这就需要连续归档,它不仅可以用于大型数据库的增量备份和恢复,也可以用于搭建standby镜像备份.    PostgreSQL默认处于非归档模式.开启归档模式,主要涉及到三个参数:wal_level,archive_mode和archive_commandwal_level参数默认为mininal,设置此参数为archive或者之上的级别都可以打开归档.当po

  • postgresql 实现启动、状态查看、关闭

    利用psql启动数据库 [postgres@highgo ~]$ pg_ctl start 查看系统中运行的postgres进程 #ps -ef | grep postgres 连接postgresql数据库 #psql -h 127.0.0.1 -d postgres -U postgres 停止postgresql数据库实例 #pg_ctl stop #ps -ef | grep postgres 启动服务器最简单的方法是像下面这样: $ postgres -D /usr/local/pgs

  • Postgresql中LIKE和ILIKE操作符的用法详解

    LIKE和ILIKE操作符可以模糊匹配字符串,LIKE是一般用法,ILIKE匹配时则不区分字符串的大小写. 它们需要结合通配符使用,下面介绍两种常用的通配符. %:百分号用于匹配字符串序列,可匹配任意组合: _:下划线用于匹配任何单一字符. 举例来说明LIKE和ILIKE操作符的区别. 先创建一张数据表table1,包含两列:id列和name列,代码如下: create table table1(id int, name varchar); insert into table1 values(1

随机推荐