PostgreSQL函数

1. 时间函数

TO_DATE()

字符串转日期类型(不带时分秒)

TO_TIMESTAMP()

字符串转日期类型(带时分秒)

2. COALESCE()

COALESCE函数是返回参数中的第一个非null的值
它要求参数中至少有一个是非null的，如果参数都是null会报错。

select COALESCE(null,null); // 报错
select COALESCE(null,null,now(),''); // 结果会得到当前的时间
select COALESCE(null,null,'',now()); // 结果会得到''

// 可以和其他函数配合来实现一些复杂点的功能：
// 查询学生姓名，如果学生名字为null或''则显示“姓名为空”
select
  case 
    when coalesce(name, '') = '' 
      then '姓名为空' 
    else name 
    end 
from
  student;

3. CONFLICT ()

是新数据则执行插入动作，如果插入的字段和已有字段重复，则更新该行对应的部分字段.多用主键或唯一字段作为判断基准

-- 创建一个表
create table meta_data( 
  id serial
  , user_id varchar (128) DEFAULT NULL
  , file_name varchar (1024) DEFAULT NULL
  , file_path varchar (1024) DEFAULT NULL
  , update_time TIMESTAMP DEFAULT NULL
  -- 设置表的user_id和file_name字段唯一
  , UNIQUE (user_id, file_name)
);

-- 向表中插入数据
INSERT 
INTO meta_data(user_id, file_name, file_path, UPDATE_TIME) 
VALUES ( 
  'user_id02'
  , 'file_name02'
  , '/usr/local/file_name02'
  , now()
)
  -- 因为user_id和file_name是唯一的,因此当出现冲突的时候
  -- 就更新file_path和UPDATE_TIME字段
  ON CONFLICT(user_id, file_name) DO UPDATE 
SET
  file_path = EXCLUDED.file_path
  , UPDATE_TIME = EXCLUDED.UPDATE_TIME;

4. 字符串函数

||

字符串拼接

SELECT
  TO_CHAR(now(), 'YYYY') || '年' || TO_CHAR(now(), 'MM') || '月' 年月;
-- 结果 --
-- 2021年03月

SELECT
  'Post' || 'gresql' || ' good!';
-- 结果 --
-- Postgresql good!

SELECT
  1 || ' one';
-- 结果 --
-- 1 one

bit_length(string)

字符串的位数，注意一个汉字是3个字节

select
  bit_length('one');  -- 24

char_length(string)

字符串中字符的个数

select
  char_length('中国');  -- 2

length(string)

字符串中字符的个数,和char_length(string)作用相同

select
  char_length('中国');  -- 2

length(string,encoding name)

以name的编码格式字符串的字符个数

select
  length('中国', 'GBK');  -- 3

octet_length(string)

字符串的字节数

select
  octet_length('中国');  -- 6

lower(string)

将字符串转换成小写

select lower('HeLLO');  -- hello

upper(string)

将字符串转换成大写

select lower('HellO');  -- HELLO

initcap(string)

将字符串中每个单词的首字母大写

select initcap('hello world !');  -- Hello World !

overlay(string placing string from int [for int])

替换字符串，其中第一个int是开始位置，第二个int是长度，如果没有第二个int则长度默认为第二个字符串的长度

select
  overlay('Txxxxas' placing 'hom' from 2 for 4);  -- Thomas

replace(string,string,string)

替换字符串，将第一个字符串中的第二个字符串替换成第三个字符串

select
  replace ('Txxxxas', 'xxxx', 'hom');  -- Thomas

translate(string text, from text, to text)

• 把string中from字符替换成对应to中的字符，如(‘text’,‘tx’,‘nd’)中t->n,x->d,替换后结果为nedn;
• 如果from比to长，则删除from多出的字符，如(‘Txxxxas’,‘Txas’,‘hom’)结果为hooom;
• 如果from中有重复字符，对应顺序也是一一对应的，但是重复字符以第一个为准，如(‘txxxa’,‘xxx’,‘abcd’),结果为taaad

select translate('Txxxxas','xxxxa','hom');  -- Thhhhs
select translate('Txxxxas','Txas','hom');  -- hoooom
select translate('txxxa','xxxa','abcd');  -- taaad

position(substring in string)

给定子字符串在字符串的位置

select position('lo' in 'hello');  -- 4

strpos(string, substring)

给定子字符串在字符串的位置,功能和position()相同,只是参数的位置不同

select strpos('hello','lo');  -- 4

substring(string from int [for int])

截取字符串,从from位置截取长度for,如果for省略,则是从from至结尾

select substring('hello world' from 2 for 3);  -- ell
select substring('hello world' from 2);  -- ello world

substring(string from pattern)

截取匹配posix正则表达式的字符串

select substring('hello world' from '...$');  -- rld

substring(string from pattern for escape)

截取匹配posix正则表达式的字符串,for为转义字符

select substring('Thomas' from '%#"o_a#"_' for '#');  -- oma

trim()

删除字符串头部(leading),尾部(trailing)或两边的空白活[characters]字符

select trim(both 'x' from 'xxjdhdxxxx');  -- jdhd
select trim(both from '  jdhd   ' , ' ');  -- jdhd

btrim(string text [, characters text])

删除字符串两边指定的字符

select btrim('xxhhhxxx','x');  -- hhh

rtrim(string text [, characterstext])

删除字符串尾部指定的字符

select rtrim('xxhhhxxx','x');  -- xxhhh

ltrim(string text [, characterstext])

删除字符串开头指定的字符

select ltrim('xxhhhxxx','x');  -- hhhxxx

ascii(string)

字符串第一个字符的ASCII值

select ascii('xa');  -- 120
select ascii('x');  -- 120

chr(int)

将数字转换成字符

select chr(65);  -- A

concat(str “any” [, str “any” [, …] ])

连接所有参数，个数不限，类型不限

select concat('x','man',3);  -- xman3

concat_ws(sep text, str “any” [,str “any” [, …] ])

连接所有参数,第一个参数是连接分隔符

select concat_ws(',','x','man',3);  -- x,man,3

convert(string bytea,src_encoding name, dest_encodingname)

将字符串从指定编码转换至目的编码格式

select convert('Hello','UTF8','GBK');  -- \x48656c6c6f

format(formatstr text [,formatarg “any” [, …] ])

格式化字符串，类似C语言的sprintf,其中n$表示第n个参数

select format('Hello %s, %1$s', 'World');  -- Hello World, World

left(str text, n int)

返回字符串前n个字符，n为负数时返回除最后|n|个字符以外的所有字符

select left('hello',-2);  -- hel

right(str text, n int)

返回字符串后n个字符，n为负数时返回除最前|n|个字符以外的所有字符

select right('hello',2);  -- he

lpad(string text, length int [,fill text])

在字符串开头填充text至长度为length，缺省为空白，如果string的长度已经大于length，则会截断后面多余length的字符

select lpad('123',5,'0');  -- 00123

rpad(string text, length int [,fill text])

在字符串尾部填充text至长度为length，缺省为空白，如果string的长度已经大于length，则会截断后面多余length的字符

select rpad('he',1,'o');  -- h

parse_ident(qualified_identifiertext [, strictmode boolean default true ] )

将qualified_identifier拆分解析到一个数组中，以句点为分隔符。(返回一个数组)

select parse_ident('SomeSchema.someTable');  -- {someschema,sometable}

参考资料

1. https://www.cnblogs.com/ilifeilong/p/11573525.html
2. https://www.cnblogs.com/dongyaotou/p/13224307.html
3. https://www.cnblogs.com/dancesir/p/10036290.html
4. https://www.cnblogs.com/alianbog/p/5656722.html