=Start=

缘由：

很早就听说过 PostgreSQL 这个数据库，近期翻阅blog的时候发现14/15年的时候也简单记录过一些 PostgreSQL 的知识点，但主要都是安装和查看信息的命令的总结，具体的一些查询语法没怎么涉及到，近期因为工作上的原因接触了一下，虽然大部分语法和功能同 MySQL/Hive 的SQL语法一样，但也是有一些不同的地方，这里总结一下，方便后面有需要的时候参考。

正文：

参考解答：

从json格式的字符串中提取特定字段

PostgreSQL 的官方文档真的很好，全面且有样例，比较容易理解和使用

-- 我习惯常用的一般是 ->> 这种取出结果为text字符串的形式，方便比较
select
 '[1,2,3]'::json->2 as get_json_arr_by_index -- 3
,'[1,2,3]'::json->>2 as get_json_arr_by_index_as_text -- 3
,'{"a":1,"b":2}'::json->'b' as get_json_str_by_key -- 2
,'{"a":1,"b":2}'::json->>'b' as get_json_str_by_key_as_text -- 2
,'{"a":[1,2,3],"b":[4,5,6]}'::json#>'{a,2}' as get_json_str_by_path -- 3
,'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}' as get_json_str_by_path_as_text -- 3
,json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]')    -- 5
;

select
*
from
json_each('{"a":"foo", "b":"bar"}')
;
/*
 key | value
-----+-------
 a   | "foo"
 b   | "bar"
*/

-- 下面这一部分和上面的显示输出是相同的，但是下面这个的返回的value的类型是text的，可以直接用于字符串比较，上面那个是json类型的
select
*
from
json_each_text('{"a":"foo", "b":"bar"}')
;

timestamp类型的比较

PostgreSQL 比较智能，可以直接用日期时间字符串直接作为条件，也可以手动进行类型转换

-- 在日期时间字符串之前加上 timestamp 关键字进行类型转换
select *
from the_table
where
the_timestamp_column >= timestamp '2015-07-15 00:00:00'
and the_timestamp_column < timestamp '2015-07-16 00:00:00'
;
-- 也可以直接比较
select *
from the_table
where
the_timestamp_column >= '2015-07-15'
and the_timestamp_column < '2015-07-16'
;

字符串切分和子串提取等操作

简单测试了一下发现 PostgreSQL 支持的功能真的很多而且很方便，更多的功能点后面根据实际需求慢慢探索吧

select
-- 字符串切分
split_part('abc~@~def~@~ghi', '~@~', 2) -- def
,regexp_split_to_array('abc~@~def~@~ghi', '~@~') -- ["abc","def","ghi"]
,regexp_split_to_array('hello world', '\s+') -- ["hello","world"]

-- 字符串查找定位
,strpos('high', 'ig') -- 2
,position('ig' in 'high') -- 2 #和上面的写法等价
,strpos('high', 'x') -- 0 #找不到的时候返回0，因为找到的时候是从1开始计算

-- 字符串子串
,left('abcde', 2) -- ab
,right('abcde', 2) -- de
,substr('alphabet', 3, 2) -- ph #说明index从1开始，而不是0
,substring('alphabet' from 3 for 2) -- ph #和上面的写法等价

-- 字符串映射转换/替换
,translate('12345', '135', 'abc') -- a2b4c

,replace('abcdefabcdef', 'cd', 'XX') -- abXXefabXXef
,regexp_replace('Thomas', '.[mN]a.', 'M') -- ThM

,ltrim('zzzytest', 'xyz') -- test
,rtrim('testxxzx', 'xyz') -- test
,lpad('hi', 5, 'xy') -- xyxhi
,rpad('hi', 5, 'xy') -- hixyx

-- 字符串翻转
,reverse('abcde') -- edcba

-- 计算字符串的md5哈希值
,md5('abc')

-- 编码/解码
,encode('123\000\001', 'base64')
,decode('MTIzAAE=', 'base64')

-- 字符串长度计算
,length('jose')
,length('jose', 'UTF8')
,char_length('jose')

-- 字符串连接
,concat('abcde', 2, NULL, 22) -- abcde222
,concat_ws(',', 'abcde', 2, NULL, 22) -- abcde,2,22

,ascii('x') -- 120
,chr(120) -- x

,lower('TOM') -- tom
,upper('tom') -- TOM
;

模式匹配/正则匹配

先了解一下简单和常用的匹配方法，后面根据实际需求慢慢整理总结

select
'abc' LIKE 'abc' -- true
,'abc' LIKE 'a%'  -- true
,'abc' LIKE '_b_' -- true
,'abc' LIKE 'c'   -- false

,'abc' SIMILAR TO 'abc'     -- true
,'abc' SIMILAR TO 'a'       -- false
,'abc' SIMILAR TO '%(b|d)%' -- true
,'abc' SIMILAR TO '(b|c)%'  -- false

,'abc' ~ 'abc'    -- true
,'abc' ~ '^a'     -- true
,'abc' ~ '(b|d)'  -- true
,'abc' ~ '^(b|c)' -- false
;

类型转换

-- 将字符串转换成整数integer类型
SELECT
NULLIF(your_value, '')::int
,CAST(coalesce(your_value, '0') AS integer) as new_field
;

-- If you need to treat empty columns as NULLs, try this:
SELECT CAST(nullif(<column>, '') AS integer);

-- On the other hand, if you do have NULL values that you need to avoid, try:
SELECT CAST(coalesce(<column>, '0') AS integer);

日期时间的处理

SELECT
CURRENT_DATE
,date(now())
,now()
-- ,CURRENT_TIME
,CURRENT_TIMESTAMP
-- ,CURRENT_TIME(2)
,CURRENT_TIMESTAMP(2)
;

参考链接：

9.15. JSON Functions and Operators
https://www.postgresql.org/docs/9.3/functions-json.html

Postgres where clause compare timestamp
https://stackoverflow.com/questions/31433747/postgres-where-clause-compare-timestamp

9.4. String Functions and Operators
https://www.postgresql.org/docs/9.3/functions-string.html

9.7. Pattern Matching
https://www.postgresql.org/docs/9.3/functions-matching.html

How to split string with different characters in postgresql
https://stackoverflow.com/questions/24236167/how-to-split-string-with-different-characters-in-postgresql

Typecast string to integer
https://stackoverflow.com/questions/10518258/typecast-string-to-integer

9.9. Date/Time Functions and Operators
https://www.postgresql.org/docs/9.3/functions-datetime.html

标签： PostgreSQL
https://ixyzero.com/blog/archives/tag/postgresql

=END=