记录几个PostgreSQL的简单内置函数

缘起

1.问题背景:

PostgreSQL是类似于MySQL的关系型数据库，完全开源免费.并有详细的中文文档

2.问题描述:

在通过视图的方式给第三方提供数据,对于查询到的表user表(一个假装的表)的年龄user_age字段(一个假装的字段),必须为数字类型.而数据库中存储的是各种各样.

user_age以文本类型存储,形式包括但不限于87岁,11月25天,7岁9月.这些需要对应修改成87,0,7.

解决

分析

问题很简单,因为提供的视图即是数据,不能使用程序处理.只能使用SQL去处理.之前很少用到,百度上关于pg 的这方面资料比较少．

代码

SELECT 
    CASE WHEN POSITION('岁' IN user_age) > 0 THEN REGEXP_REPLACE(user_age :: TEXT, '岁.*' :: TEXT, '' :: TEXT):: INTEGER
         ELSE  0
    END AS age
FROM
    user

简单解释

从user中提取user_age字段,如果user_age中存在’岁’这个字符,使用正则替换以’岁’开头到结尾的字串为’’.如果不存在则是0岁.

总结

这里主要是在下面记录用到的PostgreSQL语法

另外：pg的中文文档可太香了: http://www.postgres.cn/docs/12/

再提供一个正则练习网站:https://regex101.com/

各函数详细解析

1. POSITION

• 语法:

POSITION(substring IN string)

• 作用: 定位指定子串.

  可以用返回值是否大于0,判断substring是否存在于string.

  官方文档: https://www.postgresql.org/docs/9.3/static/functions-string.html

• 示例

  SELECT POSITION('a' IN 'abcd')
  //结果
  1
  

  SELECT POSITION('ab' IN 'abcd')
  //结果
  1
  

  SELECT POSITION('om' IN 'Thomas')
  //结果
  3
  

2. CASE WHEN

• 语法:

  CASE WHEN condition THEN result
       [WHEN ...]
       [ELSE result]
  END
  --变种语法:
  CASE expression
      WHEN value THEN result
      [WHEN ...]
      [ELSE result]
  END
  

• 作用: CASE表达式是一种通用的条件表达式，类似于其它编程语言中的 if/else 语句.而变种语法类似于``Switch`语句.

  官方文档: http://postgres.cn/docs/12/functions-conditional.html

• Case示例

  SELECT * FROM test;
  //结果
   a
  ---
   1
   2
   3
  
  
  SELECT a,
         CASE WHEN a=1 THEN 'one'
              WHEN a=2 THEN 'two'
              ELSE 'other'
         END
  FROM test;
  //结果
   a | case
  ---+-------
   1 | one
   2 | two
   3 | other
  

  变种示例:

  SELECT a,
         CASE a WHEN 1 THEN 'one'
                WHEN 2 THEN 'two'
                ELSE 'other'
         END
  FROM test;
  
   a | case
  ---+-------
   1 | one
   2 | two
   3 | other
  

  CASE表达式并不计算任何无助于判断结果的子表达式。例如，下面是一个可以避免被零除错误的方法：

  SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;
  

3. REPLACE

• 语法: REPLACE(text, from , to )

• 作用: 替换text中的所有from为to

• 示例

  SELECT REPLACE('aiana', 'a', 'n')
  

4. REGEXP_REPLACE

• 语法: REGEXP_REPLACE(string text, pattern text, replacement* text [, flags text])

• 作用: 替换匹配一个POSIX正则表达式的子串。

• 示例:

  SELECT REGEXP_REPLACE('Thomas', '.[mN]a.', 'M')
  --结果
  ThM
  

  SELECT REGEXP_REPALCE('foobarbaz', 'b..', 'X')
  --结果
  fooXbaz
  

  SELECT REGEXP_REPLACE('abcde', 'c.*', '')
  --结果
  ab
  

结尾

参考资料:

pg中文官网: http://www.postgres.cn/docs/12/.
正则练习: https://regex101.com/.