Postgresql学习笔记之——数据类型之字符串类型

一、Postgresql数据库字符串类型

类型	描述
character varying(n) 或 varchar(n)	变长字符串类型，最大空间1GB，存储空间4+实际的字符串长度。与MySQL中的varchar(n)或text(n)，以及Oracle中的varchar2(n)类似，但是在MySQL中varchar最多只有64KB，Oracle中的varchar2最多只有4000字节。
character(n)或char(n)	定长字符串类型，不足空白补充。最大1GB，存储空间4+n
text	变长字符串类型。无长度限制，与MySQL中的LONGTEXT类似

varchar(n) 在没有指定 n 的值时，表示可以接受1GB内的任何长度的字符串，建议加上 n
char(n) 在没有指定 n 值时，表示 char(1) ,
不管varchar还是char类型最小存储空间时4字节。而在Postgresql数据库中varchar和char指定长度过长时，实际在字段中存储的可能只是一个指针，具体内容会存储在toast表中，指针便于对字段值的快速访问。
在一些其他种类的数据库中定长的char有一定的优势。但是在postgresql中没有什么差别。大多数还是会用varchar或text。

二、字符串函数和操作符

支持标准的SQL字符串的函数和操作符：

函数	返回类型	描述	例子	结果
string ll string	text	字符串链接	selec ‘abc’ ll ‘EFG’；	‘abcEFG’
length(string)	int	string 中字符的个数，主要区分的是中文和英文不同	select length(‘abc’)	3
bit_length(string)	int	字符串二进制位长度（个数）	select bit_length(‘jack’);	32
char_length(string)或character_length(string)	int	字符串中字符的个数	select char_length(‘jack’);	4
octet_length(string)	int	字符串中的字节数	select octet_length(‘jack’);	4
convert(string using conversion_name)	test	使用指定的转换名字改变编码。转换可以通过CREATE CONVERSION定义，系统中也有一些预定义的转换名字	select convert(‘Postgresql’ using iso_8859_1_to_utf8);	UTF8编码的Postgresql
convert( string text, [ src_encoding name, ], dest_encoding name )	text	把字符串原来编码位 src_encoding 转成 dest_encoding 编码。如果没有指定src_encoding，则src_encoding为数据库编码	select convert( ‘abc’, ‘UTF8’, ‘GBK’ )	以GBK编码的abc
lower(string)	text	把所有的字符串转成小写	select lower(‘TOM’);	tom
upper(string)	text	把所有字符串转成大写	select upper(‘tom’);	TOM
initcap(string)	text	将字符串中每个单词的第一个字符转为大写，其他变为小写（字符串中区分单词：非字符数字分割）	initcap(‘hi jack’)	Hi Jack
overlay(string placing string from int[for int])	text	指定子字符串替换的范围，例子里时用 hom 字符串替换 Txxxxas 中从第2个字符开始的4个字符，如果没有指定 for int ，int 是子字符串的长度	select overlay(‘Txxxxas’ placing ‘hom’ from 2 for 4);	Thomas
position( substring in string )	int	查找substring 在 string 中的起始位置	select position( ‘om’ in ‘Thomas’);	3
strpos(string , substring)	int	查找指定的子字符串substring在string中的起始位置	select strpos(‘helloworld’ ,‘llo’);	3
substring(string, from [, count] )	text	截取string中指定位置from的指定长度count的子字符串	select substring ( ‘jack’ , 2, 2)	ac
substring(string from int for int )	text	抽取指定位置（from int ）开始指定个数（for int）的字符串，如果没有指定 for int 则从指定位置一直到结束全部截取	select substring( ’ Thomas’ from 2 for 3 );	hom
substring(string from pattern)	text	截取匹配posix正则表达式的字符串	select substring(‘hello world’ from ‘…$’);	rld
substring(string from pattern for escape)	text	截取匹配posix正则表达式的字符串,for为转移字符	select substring(‘Thomas’ from ‘%#“o_a#”_’ for ‘#’);	oma
replace(string text, from text, to text )	text	把字符串string中所有出现的from 都替换成 to	select replace(‘abcddggdd’,‘d’,‘5’)	abc55gg55
ascii(string)	int	字符串中第一个字符的ASCII码	select ascii(‘abc’)	97
chr(int)	text	给出ASCII码所对应的字符	select chr(97)	a
decode(string text, type text )	bytea	把早先用encode编码的string里面的二进制数据解码，支持类型：base64、hex、escape
encode(data bytea, type text)	text	把二进制数据编码位只包含ASCII形式的数据，支持类型与decode类似
lpad(string text, length int [, fill text] )	text	通过在指定的string左边填充字符 fill （不指定默认空白），把string填充为指定的length长度，如果string长度比指定的length长，则将其尾部截断,例子里是将字符串 OK 左边使用 1 填充直到长度为5	select lpad( ‘OK’, ‘5’, ‘1’)	111OK
rpad(string text, length int [, fill text] )	text	通过在指定的string右边填充字符 fill （不指定默认空白），把string填充为指定的length长度，如果string长度比指定的length长，则将其尾部截断,例子里是将字符串 OK 右边使用 1 填充直到长度为5	select rpad( ‘OK’, ‘5’, ‘1’)	OK111
btrim( string text [ , character text ] )	text	从string的开头和结尾删除含有 character 中的字符，不指定character默认空格	select btrim( ‘aajackaaaa’, ‘aa’ )	jack
ltrim( string text [ , character text ] )	text	从string的开头（左边）删除含有 character 中的字符，不指定character默认空格	select btrim( ‘aajackaaaa’, ‘aa’ )	jackaaaa
rtrim( string text [ , character text ] )	text	从string的结尾（右边）删除含有 character 中的字符，不指定character默认空格	select btrim( ‘aajackaaaa’, ‘aa’ )	aajack
quote_ident(string)	text	返回适用于SQL语句的标识符形式（使用适当的引号进行界定），只有在必要的时候才会添加引号（字符串包含非标识符字符或者会转换大小写的字符），嵌入的引号会被恰当的写双份
quote_literal(string)	text	返回适用于在SQL语句里当作文本的形式，嵌入的引号和反斜杠被恰当的写双份
pg_client_encoding()	name	当前客户端编码名称	select pg_client_encoding();	UTF8
repeat(string text, number int )	text	将string重复number次	select repeat( ‘abc’, 2);	abcabc
split_part(string text, delimiter text, field int)	text	根据delimiter分隔string，返回生成第 field 个字符串	split_part( ‘123#456#789’, ‘#’, 2 )	456
regexp_replace(string text, pattern text, replacement text [ , flages text ])	text	替换匹配POSIX正则表达式的子字符串

三、特别注释

字符串length类似函数用法：

postgres=# select length('abcd');
 length 
--------
      4
(1 row)

postgres=# select octet_length('abcd');
 octet_length 
--------------
            4
(1 row)

postgres=# select bit_length('abcd');
 bit_length 
------------
         32
(1 row)

postgres=# select length('测试');
 length 
--------
      2
(1 row)

postgres=# select octet_length('测试');
 octet_length 
--------------
            6
(1 row)

postgres=# select bit_length('测试');
 bit_length 
------------
         48
(1 row)