PostgreSQL各資料型別的內置函式-有解無憂

參考《PostgreSQL實戰》

3.1.2 數字型別運算子和數學函式

PostgreSQL 支持數字型別運算子和豐富的數學函式

例如支持加、減、乘、除、模取取余運算子

SELECT 1+2, 2*3, 4/2, 8%3;

按模取余

SELECT mod(8,3);
結果：2

四舍五入函式：

SELECT round(10.4) , round(10.5);
結果：10, 11

回傳大于或等于給出引數的最小整數

SELECT ceil(3.6) , ceil(-3.6);
結果：4, -3

回傳小于或等于給出引數的最小整數

floor(3.6) 結果：3

3.2.2 字符型別函式

PostgreSQL 支持豐富的字符函式，下面舉例說明

計算字串中的字符數

select char_length('abcd');
結果: 4

計算字串占用的位元組數

select octet_length('abcd');
結果: 4

指定字符在字串中的位置(首次出現)

select position('a' in 'abcda')
結果: 1
select position('x' in 'abcda')
結果: 0

提取字串中的子串

select substring('hello' from 3 for 4)
結果: llo
select substring('hello' from 4 for 1)
結果: l

拆分字串

split_part (string text, delimiter text, field int )

根據 delimiter 分隔符拆分字串 string,并回傳指定欄位,欄位從 1 開始

select split_part('abc@def1@nb', '@', 1)
結果: abc

3.3.2 時間/日期型別運算子

時間、日期資料型別支持的運算子有加、減、乘、除，

日期相加

select date '2017-07-29' + interval'1 days'
結果: 2017-07-30 00:00:00

日期相減

select date '2017-07-29' - interval'1 hour'
結果: 2017-07-28 23:00:00

日期相乘

select 100* interval '1 second'
結果: 00:01:40

日期相除

select interval '1 hour' / double precision '3'
結果: 00:20:00

3.3.3 時間／日期型別常用函式

顯示當前時間

select current_date, current_time;
結果: 2019-11-23, 20:20:55.635115+08

另一個非常重要的函式為 EXTRACT 函式，可以從日期、時間資料型別中抽取年、月、日、時、分、秒資訊

EXTRACT(field FROM source)

field 值可以為 century、year、 month、 day、 hour 、 minute、 second等， source型別為
timestamp、 time、 interval的值的運算式，

取年份

select extract ( year from now())
結果: 2019

月份&月份里的第幾天(當前時間2019-11-24)

select extract (month from now()), extract(day from now());
結果: 11, 24

抽取小時、分鐘、秒語法同上，單詞為hour,minute,second

抽取當前日期是所在年份的第幾周&多少天

select extract(week from now()), extract(day from now())
結果: 47, 24

3.5.3 網路地址函式

PostgreSQL 網路地址型別支持一系列內置函式，下面舉例說明，

取IP地址 ，回傳文本格式，

select host(cidr '192.168.1.0/24');
結果: 192.168.1.0

取IP地址和網路掩碼，回傳文本格式

select text(cidr '192.168.1.0/24');
結果: 192.168.1.0/24

取網路地址子網掩碼，回傳文本格式

select netmask(cidr '192.168.1.0/24');
結果: 255.255.255.0

3.6.4 資料元素的追加、洗掉、更新

陣列元素的追加使用array_append 函式

array_append(anyarray, anyelement)

array_append 函式向陣列末端追加一個元素

select array_append(array[1,2,3], 4);
結果: {1,2,3,4}

資料元素追加到陣列也可以使用運算子 ||

select array[1,2,3] || 4;
結果: {1,2,3,4}

陣列元素的洗掉使用 array_remove 函式

array_remove(anyarray, anyelement)

array_remove 函式將移除陣列中值等于給定值的所有陣列元素

select array[1,2,2,3], array_remove(array[1,2,2,3], 2);
結果: {1,2,2,3}, {1,3}

陣列元素修改:

update test_array_table set array_i[3] = 4 where id = 1;
UPDATE 1

更新整個陣列

update test_array_table set array_i=array[7,8,9] where id = 1;
UPDATE 1

3.6.5 陣列運算子

陣列運算子

3.6.6 陣列函式

陣列維度

select array_ndims(array[1,2]);
結果: 1

陣列長度(獲取指定維度的長度)

select array_length(array[1,2], 1);
結果: 2

select array_length(array[[1],[2]], 2);
結果: 1
select array_length(array[[1],[2]], 1);
結果: 2

回傳陣列中某個陣列元素第一次出現的位置

select array_position(array['a', 'b', 'c', 'd'], 'd');
結果: 4

陣列元素替換可使用函式 array_replace

array replace(anyarray, anyelement, anyelement)

函式回傳值型別為 anyarray，使用第二個 anyelement 替換陣列中的相同陣列元素

select array_replace( array[1,2,3,3,4], 3, 6);
結果: {1,2,6,6,4}

將陣列元素輸出到字串，可以使用 array_to_string 函式

array_to_striiing( anyarray, text [, test])

函式回傳值型別為text ，第一個text 引數指分隔符，第二個 text 表示將值為 NULL 的
元素使用這個字串替換

select array_to_string(array[1,2,null,3], ',', '-1');
結果: 1,2,-1,3

還有array_to_json(array)函式，將陣列直接轉換為json

3.7.4 范圍型別函式

以下列舉范圍型別常用函式

取范圍下界

select lower(int4range(1,10));
結果: 1

取范圍上界

select upper(int4range(1,10));
結果: 10

范圍是否為空

select isempty(int4range(1,10));
結果: f
select isempty(int4range(10,10));
結果: t

3.8.5 jsonb與json函式

json 與 jsonb 相關的函式非常豐富，下面舉例說明

擴展最外層的 json 物件成為一組鍵／值結果集

select * from json_each('{"a":"foo", "b":"bar"}');
結果:
a | "foo"
b | "bar"

文本形式回傳結果

select * from json_each_text('{"a":"foo", "b":"bar"}');
結果：
a |	foo
b |	bar

row_tojson()函式，能夠將行作為json物件回傳，
此函式常用來生成 json 測驗資料，比如將一個普通表轉換成 json 型別表

select * from test_copy where id = 1;

id | name
---+-----
1  | a

select row_to_json(test_copy) from test_copy where id = 1;

row_to_json
--------------
{"id":1,"name":"a"}

回傳最外層的 json 物件中的鍵的集合

select * from json_object_keys('{"a":"foo", "b":"bar"}');

json_object_keys
---------------
a
b

3.8.6 jsonb 鍵／值的追加、洗掉、更新

jsonb 鍵／值追加可通過“||”運算子

select '{"name":"francs","age":"31"}' :: jsonb || '{"sex":"male"}' :: jsonb;

{"age": "31", "sex": "male", "name": "francs"}

jsonb 鍵／值的洗掉有兩種方法，一種是通過運算子
“-”洗掉，另一種通過運算子“#_”洗掉指定鍵／值

第一種

SELECT '{"name" : "James", "email": "james@localhost"}':: jsonb - 'email';
{"name": "James"}

select '["red","green","blue"]'::jsonb - 0;
["green", "blue"]

第二種運算子“#_”洗掉指定鍵／值，通常用于有嵌套 json 資料洗掉的場
景

洗掉嵌套contact中的fax 鍵值

SELECT
'{"name":"James","contant":{"phone":"01234567890", "fax":"01923 342234"}}' :: jsonb 
#-
'{contant,fax}' :: TEXT []

結果：
{"name": "James", "contant": {"phone": "01234567890"}}

洗掉嵌套contant中，坐標為0的鍵值

SELECT
'{"name":"James","contant":["first", "second", "third"]}' :: jsonb 
#-
'{contant,0}' :: TEXT []

結果: 
{"name": "James", "contant": ["second", "third"]}

鍵／值的更新也有兩種方式

第一種方式為“||”運算子，“||”運算子可以連接 json鍵，也可以覆寫重復的鍵值

select '{"name":"AganRun", "age":"31"}'::jsonb || '{"age":"100"}' :: jsonb;

{"age": "100", "name": "AganRun"}

第二種方式是通過jsonb_set函式
jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])

target 指源jsonb資料， path指路徑， new_value 指更新后的鍵值， creat_missing 值為
true 表示如果鍵不存在則添加, create_missing 值為 false 表示如果鍵不存在則不添加

SELECT jsonb_set ('{"name":"francs","age":"31"}':: jsonb ,'{age}','"32"'::jsonb, false);

{"age": "32", "name": "francs"}

SELECT jsonb_set  ('{"name":"francs","age":"31"}':: jsonb ,'{sex }','"male"':: jsonb, true);

{"age": "31", "sex": "male", "name": "francs"}

資料型別轉換

格式化函式、CAST函式、運算子

3.9.1 通過格式化函式進行轉換

格式化函式

3.9.2 通過 CAST 函式進行轉換

varchar 字符型別轉換成 text 型別

select cast(varchar'123' as test);
 
123

varchar 字符型別轉換成 int4 型別

select cast(varchar'123' as text);

123

3.9.3 通過::運算子進行轉換

例子轉換成 int4 或 numeric 型別

select 1::int4, 3/2::numeric;

1 , 1.5

三種資料型別轉換方法，第－種方法兼容性相對較好，第三種方法用法簡捷

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/209936.html

標籤：PostgreSQL

上一篇：Debugging: MISCONF Redis is configured to save RDB snapshots（譯）

下一篇：分布式檔案存盤資料庫之MongoDB副本集