Postgresql常用函數及使用方法大全(看一篇就夠瞭)

前言

寫在前面,當我們在用postgresql數據庫進行數據取數時,難免會遇到一些數據處理上的場景,本文會針對一些常用的內置函數進行總結。

-- 查看postgresql的版本(以下任一語句都可以實現)
select version();
show server_version;

1.格式轉換

1.1 格式轉換符顯示轉換

利用雙冒號可以直接進行格式轉換,語法如下:

字段名或數值::數據類型

例子如下:

-- 將文本'123'轉為int8類型
SELECT
    '123' :: int8 num;
-- 將文本類型字段t1轉為int8類型
SELECT
    t1 :: int8 
    from temp;

1.2 利用數據類型顯示轉換

利用數據類型進行轉換,語法如下:

數據類型數值

例子:

-- 將文本'123'轉為int8類型
SELECT
    int8'123' num;

1.3 格式轉換函數顯示轉換

利用數據轉換函數cast進行轉換,語法如下:

cast(字段名或數值 as 數據類型)

例子:

-- 將文本'123'轉為int8類型
SELECT
    cast('123' as int4) num;
-- 將文本字段t1轉為int類型
SELECT CAST( t1 AS INT ) t1_c 
FROM
    TEMP;

1.4 轉換案例

-- 文本轉整數
SELECT CAST
    ( '123' AS int4 );
-- 文本轉浮點數字
SELECT CAST
    ( '123.34' AS DECIMAL );
SELECT CAST
    ( '123.34' AS NUMERIC );
-- 數字轉文本
SELECT CAST
    ( 123 AS VARCHAR );--可變字符串
SELECT CAST
    ( - 123 AS CHAR ( 2 ) );-- 固定字符串,進行截斷,將-123轉為'-1'
SELECT CAST
    ( - 123 AS CHAR ( 6 ) );-- 固定字符串,進行空格填充,將-123轉為'-123  '
SELECT CAST
    ( 124.94 AS TEXT );--可變字符串,將124.94轉為'124.94'
SELECT
    to_char( 124.94, '999D9' );--將124.94轉為'124.9',遵循四舍五入
SELECT
    to_char( 124.94, 'FM999.99' );--將124.94轉為'124.94'
SELECT
    to_char( - 124.94, 'FM9999999.99' );--將-124.94轉為'-124.94'
SELECT
    to_char( - 124.94, 'FM9999999.990' );--將-124.94轉為'-124.940'
SELECT
    to_char( 124, '00000' );--左端用零補齊湊夠5位,將124轉為'00124'
SELECT
    to_char( 124, '99999' );--左端用空格補齊湊夠5位,將124轉為'  124'
SELECT
    to_char( - 124.945, 'FM999' );--隻顯示整數部分,遵循四舍五入
-- 時間戳(timestamp)轉日期(date)
SELECT CAST
    ( now( ) AS DATE );--普通日期模式
-- 時間戳(timestamp)轉文本
SELECT CAST
    ( now( ) AS TEXT );--不指定輸出格式
SELECT
    to_char( now( ), 'yyyy-mm-dd' );--指定輸出格式;
-- 文本轉日期(date)
SELECT
    to_date( '2012-01-01', 'yyyy-mm-dd' );
-- 文本轉時間戳(TIMESTAMP)
SELECT
    to_timestamp( '2012-01-01 12:02:01', 'yyyy-mm-dd HH24:MI:SS' );

2.數學計算

2.1 數學運算操作符

操作符 描述 例子 結果
+ 2 + 3 5
2 – 3 -1
* 2 * 3 6
/ 除(整數除法截斷結果) 4 / 2 2
% 模(取餘) 5 % 4 1
^ 指數(從左至右結合) 2.0 ^ 3.0 8
|/ 平方根 |/ 25.0 5
||/ 立方根 ||/ 27.0 3
! 階乘 5 ! 120
!! 階乘(前綴操作符) !! 5 120
@ 絕對值 @ -5.0 5
& 按位與 91 & 15 11
| 按位或 32 3
# 按位異或 17 # 5 20
~ 按位求反 ~1 -2
<< 按位左移 1 << 4 16
>> 按位右移 8 >> 2 2

2.2 數學運算函數

函數 返回類型 描述 例子 結果
abs(x) 和輸入相同 絕對值 abs(-12.43) 12.43
cbrt(dp) double 立方根 cbrt(27.0) 3
ceil(dp or numeric) 和輸入相同 不小於參數的最近的整數 ceil(-42.8) -42
ceiling(dp or numeric) 和輸入相同 不小於參數的最近的整數(ceil的別名) ceiling(-95.3) -95
degrees(dp) dp 把弧度轉為角度 degrees(0.5) 28.6478897565412
div(y numeric, x numeric) numeric y/x的整數商 div(9,4) 2
exp(dp or numeric) 和輸入相同 指數 exp(1.0) 2.71828182845905
floor(dp or numeric) 和輸入相同 不大於參數的最近的整數 floor(-42.8) -43
ln(dp or numeric) 和輸入相同 自然對數 ln(2.0) 0.693147180559945
log(dp or numeric) 和輸入相同 以10為底的對數 log(100.0) 2
log10(dp or numeric) 和輸入相同 以10為底的對數 log10(100.0) 2
log(b numeric, x numeric) numeric 以b為底的對數 log(2.0, 64.0) 6.0000000000
mod(y, x) 和參數類型相同 y/x的餘數 mod(9,4) 1
pi() dp “π”常數 pi() 3.14159265358979
power(a dp, b dp) dp 求a的b次冪 power(9.0, 3.0) 729
power(a numeric, b numeric) numeric 求a的b次冪 power(9.0, 3.0) 729
radians(dp) dp 把角度轉為弧度 radians(45.0) 0.785398163397448
round(dp or numeric) 和輸入相同 圓整為最接近的整數 round(42.4) 42
round(v numeric, s int) numeric 圓整為s位小數數字 round(42.4382, 2) 42.44
scale(numeric) integer 參數的精度(小數點後的位數) scale(8.41) 2
sign(dp or numeric) 和輸入相同 參數的符號(-1, 0, +1) sign(-8.4) -1
sqrt(dp or numeric) 和輸入相同 平方根 sqrt(2.0) 1.4142135623731
trunc(dp or numeric) 和輸入相同 截斷(向零靠近) trunc(42.8) 42
trunc(v numeric, s int) numeric 截斷為s位小數位置的數字 trunc(42.4382, 2) 42.43

3.邏輯計算

3.1 邏輯操作符

postgresql中的邏輯操作符,有以下三種:

  • AND
  • OR
  • NOT

3.2 比較操作符

操作符 描述
< 小於
> 大於
<= 小於等於
>= 大於等於
= 等於
<> or != 不等於

!=操作符在分析器階段被轉換成<>

3.3 比較謂詞

謂詞 描述
a BETWEEN x AND y 在x和y之間
a NOT BETWEEN x AND y 不在x和y之間
a BETWEEN SYMMETRIC x AND y 在對比較值排序後位於x和y之間
a NOT BETWEEN SYMMETRIC x AND y 在對比較值排序後不位於x和y之間
a IS DISTINCT FROM b 不等於,空值被當做一個普通值
a IS NOT DISTINCT FROM b 等於,空值被當做一個普通值
expression IS NULL 是空值
expression IS NOT NULL 不是空值
expression ISNULL 是空值(非標準語法)
expression NOTNULL 不是空值(非標準語法)
boolean_expression IS TRUE 為真
boolean_expression IS NOT TRUE 為假或未知
boolean_expression IS FALSE 為假
boolean_expression IS NOT FALSE 為真或者未知
boolean_expression IS UNKNOWN 值為未知
boolean_expression IS NOT UNKNOWN 為真或者為假

3.4 比較函數

函數 描述 例子 例子結果
num_nonnulls(VARIADIC “any”) 返回非空參數的數量 num_nonnulls(0, NULL, 1 ,2 ,3) 4
num_nulls(VARIADIC “any”) 返回空參數的數量 num_nulls(0, NULL, 1 ,2 ,3) 1

4.字符串及相關匹配函數

函數 返回類型 描述 例子 結果
string || string text 串接 ‘Hello’ || ‘Word’ ‘HelloWord’
string || non-string or non-string || string text 使用一個非字符串輸入的串接 'Value: ’ || 42 Value: 42
bit_length(string) int 串中的位數 bit_length(‘Hello’) 40
char_length(string) or character_length(string) int 串中字符數 char_length(‘Hello’) 4
lower(string) text 將字符串轉換為小寫形式 lower(‘Hello’) hello
overlay(string placing string from int [for int]) text 替換子串,for後面是指替換的位數 overlay(‘Hexxx,word’ placing ‘llo’ from 3 for 4) Helloword
position(substring in string) int 定位指定子串位置,可利用值是否大於0來判斷是否包含子串 position(‘lo’ in ‘hello’) 4
substring(string [from int] [for int]) text 提取子串 substring(‘hello’ from 1 for 3) hel
substring(string from pattern) text 提取匹配POSIX正則表達式的子串 substring(‘hello’ from ‘^…’) hel
substr(string, from [, count]) text 提取子串 substr(‘Hello’, 1, 3) hel
trim([leading | trailing | both] [characters] from string) text 從string的開頭、結尾或者兩端(both是默認值)移除隻包含characters(默認是一個空格)中字符的最長字符串 trim(both ‘Hes’ from ‘sHehelloeHs’) hello
trim([leading | trailing | both] [from] string [, characters] ) text trim()的非標準版本 trim(both from ‘hhHellohh’, ‘h’) 或trim(‘hhHellohh’, ‘h’) Tom
upper(string) text 將字符串轉換成大寫形式 upper(‘hello’) HELLO
concat(str “any” [, str “any” [, …] ]) text 串接所有參數的文本表示。NULL 參數被忽略。 concat(‘abcde’, 2, NULL, 22) abcde222
concat_ws(sep text, str “any” [, str “any” [, …] ]) text 將除瞭第一個參數外的其他參數用分隔符串接在一起。第一個參數被用作分隔符字符串。NULL 參數被忽略。 concat_ws(‘,’, ‘abcde’, 2, NULL, 22) abcde,2,22
left(str text, n int) text 返回字符串中的前n個字符。當n為負時,將返回除瞭最後|n|個字符之外的所有字符。 left(‘abcde’, 2) ab
length(string) int string中的字符數 length(‘hello’) 5
length(string bytea, encoding name ) int string在給定編碼中的字符數。string必須在這個編碼中有效。 length(‘hello’, ‘UTF8’) 5
lpad(string text, length int [, fill text]) text 將string通過前置字符fill(默認是一個空格)填充到長度length。如果string已經長於length,則它被(從右邊)截斷。 lpad(‘hi’, 5, ‘ab’) abahi
ltrim(string text [, characters text]) text 從string的開頭刪除最長的隻包含characters(默認是一個空格)的串 ltrim(‘zzzytest’, ‘xyz’) test
regexp_match(string text, pattern text [, flags text]) text[] 返回一個POSIX正則表達式與string的第一個匹配得到的子串。 regexp_match(‘foobarbequetarz’, ‘(foo)(bar)’) 一行:{foo,bar}
regexp_matches(string text, pattern text [, flags text]) setof text[] 返回一個POSIX正則表達式與string匹配得到的子串 regexp_matches(‘foobarbequetarz’, ‘.ar’, ‘g’) 兩行:{bar} {tar}
regexp_replace(string text, pattern text, replacement text [, flags text]) text 替換匹配一個POSIX正則表達式的子串。 regexp_replace(‘Hello’, ‘l+.’, ‘r’) Her
regexp_split_to_array(string text, pattern text [, flags text ]) text[] 使用一個POSIX正則表達式作為分隔符劃分string。 regexp_split_to_array(‘hello world’, ‘\s+’) 一行:{hello,world}
regexp_split_to_table(string text, pattern text [, flags text]) setof text 使用一個POSIX正則表達式作為分隔符劃分string。 regexp_split_to_table(‘hello world’, ‘\s+’) 兩行:hello world
repeat(string text, number int) text 重復string指定的number次 repeat(‘he’, 3) hehehe
replace(string text, from text, to text) text 將string中出現的所有子串from替換為子串to replace(‘hello’, ‘ello’, ‘is’) his
reverse(str) text 返回反轉的字符串 reverse(‘abcde’) edcba
right(str text, n int) text 返回字符串中的最後n個字符。如果n為負,返回除最前面的|n|個字符外的所有字符。 right(‘abcde’, 2) de
rpad(string text, length int [, fill text]) text 將string通過增加字符fill(默認為一個空格)填充到長度length。如果string已經長於length則它會被截斷。 rpad(‘hi’, 5, ‘xy’) hixyx
rtrim(string text [, characters text]) text 從string的結尾刪除最長的隻包含characters(默認是一個空格)的串 rtrim(‘testxxzx’, ‘xyz’) test
split_part(string text, delimiter text, field int) text 按delimiter劃分string並返回給定域(從1開始計算) split_part(‘you!hello!world!’, ‘!’, 2) hello
strpos(string, substring) int 指定子串的位置(和position(substring in string)相同,但是註意相反的參數順序) strpos(‘hello’, ‘o’) 5
starts_with(string, prefix) bool 如果string以prefix開始則返回真。 starts_with(‘alphabet’, ‘alph’) t

5.時間與日期函數

5.1時間類操作符

操作符 例子 結果
+ date ‘2001-09-28’ + integer ‘7’ date ‘2001-10-05’
+ date ‘2001-09-28’ + interval ‘1 hour’ timestamp ‘2001-09-28 01:00:00’
+ date ‘2001-09-28’ + time ‘03:00’ timestamp ‘2001-09-28 03:00:00’
+ interval ‘1 day’ + interval ‘1 hour’ interval ‘1 day 01:00:00’
+ timestamp ‘2001-09-28 01:00’ + interval ‘23 hours’ timestamp ‘2001-09-29 00:00:00’
+ time ‘01:00’ + interval ‘3 hours’ time ‘04:00:00’
– interval ‘23 hours’ interval ‘-23:00:00’
date ‘2001-10-01’ – date ‘2001-09-28’ integer ‘3’ (days)
date ‘2001-10-01’ – integer ‘7’ date ‘2001-09-24’
date ‘2001-09-28’ – interval ‘1 hour’ timestamp ‘2001-09-27 23:00:00’
time ‘05:00’ – time ‘03:00’ interval ‘02:00:00’
time ‘05:00’ – interval ‘2 hours’ time ‘03:00:00’
timestamp ‘2001-09-28 23:00’ – interval ‘23 hours’ timestamp ‘2001-09-28 00:00:00’
interval ‘1 day’ – interval ‘1 hour’ interval ‘1 day -01:00:00’
timestamp ‘2001-09-29 03:00’ – timestamp ‘2001-09-27 12:00’ interval ‘1 day 15:00:00’
* 900 * interval ‘1 second’ interval ‘00:15:00’
* 21 * interval ‘1 day’ interval ‘21 days’
* double precision ‘3.5’ * interval ‘1 hour’ interval ‘03:30:00’
/ interval ‘1 hour’ / double precision ‘1.5’ interval ‘00:40:00’

5.2 時間、日期類函數

函數 返回類型 描述 例子 結果
age(timestamp, timestamp) interval 減去參數,生成一個使用年、月(而不是隻用日)的“符號化”的結果 age(timestamp ‘2001-04-10’, timestamp ‘1957-06-13’) 43 years 9 mons 27 days
age(timestamp) interval 從current_date(在午夜)減去 age(timestamp ‘1957-06-13’),假如今日為2022-06-14 65 years 1 day
clock_timestamp() timestamp with time zone 當前日期和時間(在語句執行期間變化) clock_timestamp() 2022-06-14 19:06:54.034672+08
current_date date 當前日期 current_date 2022-06-14
current_time time with time zone 當前時間(一天中的時間),帶時區 current_time 19:11:04.336139+08
current_timestamp timestamp with time zone 當前日期和時間(當前事務開始時),帶時區 current_timestamp 2022-06-14 19:11:57.83455+08
date_part(text, timestamp) double precision 獲得子域(等價於extract) date_part(‘hour’, timestamp ‘2022-06-14 20:38:40’) 20
date_part(text, interval) double precision 獲得子域(等價於extract) date_part(‘month’, interval ‘2 years 3 months’) 3
date_trunc(text, timestamp) timestamp 截斷到指定精度 date_trunc(‘hour’, timestamp ‘2022-06-14 20:38:40’) 2022-06-14 20:00:00
date_trunc(text, timestamp with time zone, text) timestamp with time zone 在指定的時區截斷到指定的精度 ddate_trunc(‘day’, timestamptz ‘2022-06-14 20:38:40+00’, ‘Australia/Sydney’) 2022-06-14 22:00:00+08
date_trunc(text, interval) interval 截斷到指定精度 date_trunc(‘hour’, interval ‘2 days 3 hours 40 minutes’) 2 days 03:00:00
extract(field from timestamp) double precision 獲得子域 extract(MINUTE from timestamp ‘2022-06-14 20:38:40’) 38
extract(field from interval) double precision 獲得子域 extract(month from interval ‘2 years 3 months’) 3
isfinite(date) boolean 測試有限日期(不是+/-無限) isfinite(date ‘2022-06-14’) true(實際縮寫為t)
isfinite(timestamp) boolean 測試有限時間戳(不是+/-無限) isfinite(timestamp ‘2022-06-14 21:28:30’) true(實際縮寫為t)
isfinite(interval) boolean 測試有限間隔 isfinite(interval ‘2 minutes’) true(實際縮寫為t)
justify_days(interval) interval 調整間隔這樣30天時間周期可以表示為月 justify_days(interval ‘35 days’) 1 mon 5 days
justify_hours(interval) interval 調整間隔這樣24小時時間周期可以表示為日 justify_hours(interval ‘27 hours’) 1 day 03:00:00
justify_interval(interval) interval 使用justify_days和justify_hours調整間隔,使用額外的符號調整 justify_interval(interval ‘1 mon -1 hour’) 29 days 23:00:00
localtime time 當前時間(一天中的時間),不帶時區 localtime 19:21:14.958286
localtimestamp timestamp 當前日期和時間(當前事務的開始),不帶時區 LOCALTIMESTAMP 2022-07-22 19:23:54.073462
make_date(year int, month int, day int) date 從年、月、日域創建日期 make_date(2022, 7, 15) 2022-07-15
make_interval(years int DEFAULT 0, months int DEFAULT 0, weeks int DEFAULT 0, days int DEFAULT 0, hours int DEFAULT 0, mins int DEFAULT 0, secs double precision DEFAULT 0.0) interval 從年、月、周、日、時、分、秒域創建interval make_interval(days => 10) 10 days
make_time(hour int, min int, sec double precision) time 從時、分、秒域創建時間 make_time(8, 15, 23.5) 08:15:23.5
make_timestamp(year int, month int, day int, hour int, min int, sec double precision) timestamp 從年、月、日、時、分、秒域創建時間戳 make_timestamp(2013, 7, 15, 8, 15, 23.5) 2013-07-15 08:15:23.5
make_timestamptz(year int, month int, day int, hour int, min int, sec double precision, [ timezone text ]) timestamp with time zone 從年、月、日、時、分、秒域創建帶時區的時間戳。如果沒有指定timezone, 則使用當前時區。 make_timestamptz(2022, 6, 14, 19, 30, 50.5) 2022-06-14 19:30:50.5+08
now() timestamp with time zone 當前日期和時間(當前事務的開始),帶時區 now() 2022-07-22 19:28:15.804042+08
statement_timestamp() timestamp with time zone 當前日期和時間(當前語句的開始),在一個事務的第一條命令期間返回值與CURRENT_TIMESTAMP相同 statement_timestamp() 2022-07-22 19:31:35.75589+08
timeofday() text 當前日期和時間(像clock_timestamp,但是作為一個text字符串) timeofday() Fri Jul 22 19:35:19.000959 2022 CST
transaction_timestamp() timestamp with time zone 當前日期和時間(當前事務的開始);等同於CURRENT_TIMESTAMP transaction_timestamp() 2022-07-22 19:34:02.369665+08
to_timestamp(double precision) timestamp with time zone 把 Unix 時間(從 1970-01-01 00:00:00+00 開始的秒)轉換成 timestamp to_timestamp(1655211000) 2022-06-14 20:50:00+08

6.數組函數

6.1 數組操作符

操作符 描述 例子 結果
= 等於 ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3] t
<> 不等於 ARRAY[1,2,3] <> ARRAY[1,2,4] t
< 小於 ARRAY[1,2,3] < ARRAY[1,2,4] t
> 大於 ARRAY[1,4,3] > ARRAY[1,2,4] t
<= 小於等於 ARRAY[1,2,3] <= ARRAY[1,2,3] t
>= 大於等於 ARRAY[1,4,3] >= ARRAY[1,4,3] t
@> 包含 ARRAY[1,4,3] @> ARRAY[3,1,3] t
<@ 被包含 ARRAY[2,2,7] <@ ARRAY[1,7,4,2,6] t
&& 重疊(具有公共元素) ARRAY[1,4,3] && ARRAY[2,1] t
|| 數組和數組串接 ARRAY[1,2,3] || ARRAY[4,5,6] {1,2,3,4,5,6}
|| 數組和數組串接 ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]] {{1,2,3},{4,5,6},{7,8,9}}
|| 元素到數組串接 3 || ARRAY[4,5,6] {3,4,5,6}
|| 數組到元素串接 ARRAY[4,5,6] || 7 {4,5,6,7}

6.2 數組函數

函數 返回類型 描述 例子 結果
array_append(anyarray, anyelement) anyarray 向一個數組的末端追加一個元素 array_append(ARRAY[1,2], 3) {1,2,3}
array_cat(anyarray, anyarray) anyarray 連接兩個數組 array_cat(ARRAY[1,2,3], ARRAY[4,5]) {1,2,3,4,5}
array_ndims(anyarray) int 返回數組的維度數 array_ndims(ARRAY[[1,2,3], [4,5,6]]) 2
array_dims(anyarray) text 返回數組的維度的文本表示 array_dims(ARRAY[[1,2,3], [4,5,6]]) [1:2][1:3]
array_fill(anyelement, int[], [, int[]]) anyarray 返回一個用提供的值和維度初始化好的數組,可以選擇下界不為1 array_fill(6, ARRAY[3], ARRAY[5]) [5:7]={6,6,6}
array_length(anyarray, int) int 返回被請求的數組維度的長度 array_length(array[1,2,3], 1) 3
array_lower(anyarray, int) int 返回被請求的數組維度的下界 array_lower(‘[0:2]={1,2,3}’::int[], 1) 0
array_position(anyarray, anyelement [, int]) int 返回在該數組中從第三個參數指定的元素開始或者第一個元素開始(數組必須是一維的)、第二個參數的第一次出現的下標 array_position(ARRAY[‘sun’,‘mon’,‘tue’,‘wed’,‘thu’,‘fri’,‘sat’], ‘mon’) 2
array_positions(anyarray, anyelement) int[] 返回在第一個參數給定的數組(數組必須是一維的)中,第二個參數所有出現位置的下標組成的數組 array_positions(ARRAY[‘A’,‘A’,‘B’,‘A’], ‘A’) {1,2,4}
array_prepend(anyelement, anyarray) anyarray 向一個數組的首部追加一個元素 array_prepend(1, ARRAY[2,3]) {1,2,3}
array_remove(anyarray, anyelement) anyarray 從數組中移除所有等於給定值的所有元素(數組必須是一維的) array_remove(ARRAY[1,2,3,2], 2) {1,3}
array_replace(anyarray, anyelement, anyelement) anyarray 將每一個等於給定值的數組元素替換成一個新值 array_replace(ARRAY[1,2,5,4], 5, 3) {1,2,3,4}
array_to_string(anyarray, text [, text]) text 使用提供的定界符和可選的空串連接數組元素 array_to_string(ARRAY[1, 2, 3, NULL, 5], ‘,’, ‘*’) ‘1,2,3,*,5’
array_upper(anyarray, int) int 返回被請求的數組維度的上界 array_upper(ARRAY[1,8,3,7], 1) 4
cardinality(anyarray) int 返回數組中元素的總數,如果數組為空則返回0 cardinality(ARRAY[[1,2],[3,4]]) 4
string_to_array(text, text [, text]) text[] 使用提供的定界符和可選的空串將字符串劃分成數組元素 string_to_array(‘a-b-c-d-e-g-’, ‘-’, ‘’) {a,b,c,d,e,g,NULL}
unnest(anyarray) setof anyelement 將一個數組擴展成一組行 unnest(ARRAY[1,2]) 2行:1 2

7.范圍函數

7.1 范圍操作符

操作符 描述 例子 結果
= 等於 int4range(1,5) = ‘[1,4]’::int4range t
<> 不等於 numrange(1.1,2.2) <> numrange(1.1,2.3) t
< 小於 int4range(1,10) < int4range(2,3) t
> 大於 int4range(1,10) > int4range(1,5) t
<= 小於等於 numrange(1.1,2.2) <= numrange(1.1,2.2) t
>= 大於等於 numrange(1.1,2.2) >= numrange(1.1,2.0) t
@> 包含范圍 int4range(2,4) @> int4range(2,3) t
@> 包含元素 ‘[2011-01-01,2011-03-01)’::tsrange @> ‘2011-01-10’::timestamp t
<@ 范圍被包含 int4range(2,4) <@ int4range(1,7) t
<@ 元素被包含 42 <@ int4range(1,7) f
&& 重疊(有公共點) int8range(3,7) && int8range(4,12) t
<< 嚴格左部 int8range(1,10) << int8range(100,110) t
>> 嚴格右部 int8range(50,60) >> int8range(20,30) t
&< 不超過右部 int8range(1,20) &< int8range(18,20) t  
&> 不超過左部 int8range(7,20) &> int8range(5,10) t
-|- 相鄰 numrange(1.1,2.2) -|- numrange(2.2,3.3) t
+ numrange(5,15) + numrange(10,20) [5,20)
* int8range(5,15) * int8range(10,20) [10,15)
int8range(5,15) – int8range(10,20) [5,10)

7.2 范圍函數

函數 返回類型 描述 例子 結果
lower(anyrange) 范圍的元素類型 范圍的下界 lower(numrange(1.1,2.2)) 1.1
upper(anyrange) 范圍的元素類型 范圍的上界 upper(numrange(1.1,2.2)) 2.2
isempty(anyrange) boolean 范圍為空? isempty(numrange(1.1,2.2)) false
lower_inc(anyrange) boolean 下界包含在內? lower_inc(numrange(1.1,2.2)) true
upper_inc(anyrange) boolean 上界包含在內? upper_inc(numrange(1.1,2.2)) false
lower_inf(anyrange) boolean 下界無限? lower_inf(‘(,)’::daterange) true
upper_inf(anyrange) boolean 上界無限? upper_inf(‘(,)’::daterange) true
range_merge(anyrange, anyrange) anyrange 包含兩個給定范圍的最小范圍 range_merge(‘[1,2)’::int4range, ‘[3,4)’::int4range) [1,4)

8.聚集函數

8.1 常用函數

函數 參數類型 返回類型 部分模式 描述
array_agg(expression) 任何非數組類型 參數類型的數組 No 輸入值(包括空)被連接到一個數組
array_agg(expression) 任意數組類型 和參數數據類型相同 No 輸入數組被串接到一個更高維度的數組中 (輸入必須都具有相同的維度並且不能為空或者 NULL)
avg(expression) smallint, int, bigint、real、double precision、numeric或interval 對於任何整數類型參數是numeric,對於一個浮點參數是double precision,否則和參數數據類型相同 Yes 所有非空輸入值的平均值(算術平均)
bit_and(expression) smallint、int、bigint或bit 與參數數據類型相同 Yes 所有非空輸入值的按位與,如果沒有非空值則結果是空值
bit_or(expression) smallint, int, bigint, or bit 與參數數據類型相同 Yes 所有非空輸入值的按位或,如果沒有非空值則結果是空值
bool_and(expression) bool bool Yes 如果所有輸入值為真則結果為真,否則為假
bool_or(expression) bool bool Yes 至少一個輸入值為真時結果為真,否則為假
count(*)   bigint Yes 輸入的行數
count(expression) any bigint Yes expression值非空的輸入行的數目
every(expression) bool bool Yes 等價於bool_and
json_agg(expression) any json No 將值,包含空值,聚集成一個 JSON 數組
jsonb_agg(expression) any jsonb No 把值,包含空值,聚合成一個 JSON 數組
json_object_agg(name, value) (any, any) json No 將名字/值對聚集成一個 JSON 對象,值可以為空,但不能是名字。
jsonb_object_agg(name, value) (any, any) jsonb No 把名字/值對聚合成一個 JSON 對象,值可以為空,但不能是名字。
max(expression) 任意數組、數字、串、日期/時間、網絡或者枚舉類型,或者這些類型的數組 與參數數據類型相同 Yes 所有非空輸入值中expression的最大值
min(expression) 任意數組、數字、串、日期/時間、網絡或者枚舉類型,或者這些類型的數組 與參數數據類型相同 Yes 所有非空輸入值中expression的最小值
string_agg(expression, delimiter) (text, text) 或 (bytea, bytea) 與參數數據類型相同 No 非空輸入值連接成一個串,用定界符分隔
sum(expression) smallint、int、 bigint、real、double precision、numeric、 interval或money 對smallint或int參數是bigint,對bigint參數是numeric,否則和參數數據類型相同 Yes 所有非空輸入值的expression的和
xmlagg(expression) xml xml No 連接非空XML值

8.2 統計類函數

函數 參數類型 返回類型 部分模式 描述
array_agg(expression) 任何非數組類型 參數類型的數組 No 輸入值(包括空)被連接到一個數組
array_agg(expression) 任意數組類型 和參數數據類型相同 No 輸入數組被串接到一個更高維度的數組中 (輸入必須都具有相同的維度並且不能為空或者 NULL)
avg(expression) smallint, int, bigint、real、double precision、numeric或interval 對於任何整數類型參數是numeric,對於一個浮點參數是double precision,否則和參數數據類型相同 Yes 所有非空輸入值的平均值(算術平均)
bit_and(expression) smallint、int、bigint或bit 與參數數據類型相同 Yes 所有非空輸入值的按位與,如果沒有非空值則結果是空值
bit_or(expression) smallint, int, bigint, or bit 與參數數據類型相同 Yes 所有非空輸入值的按位或,如果沒有非空值則結果是空值
bool_and(expression) bool bool Yes 如果所有輸入值為真則結果為真,否則為假
bool_or(expression) bool bool Yes 至少一個輸入值為真時結果為真,否則為假
count(*)   bigint Yes 輸入的行數
count(expression) any bigint Yes expression值非空的輸入行的數目
every(expression) bool bool Yes 等價於bool_and
json_agg(expression) any json No 將值,包含空值,聚集成一個 JSON 數組
jsonb_agg(expression) any jsonb No 把值,包含空值,聚合成一個 JSON 數組
json_object_agg(name, value) (any, any) json No 將名字/值對聚集成一個 JSON 對象,值可以為空,但不能是名字。
jsonb_object_agg(name, value) (any, any) jsonb No 把名字/值對聚合成一個 JSON 對象,值可以為空,但不能是名字。
max(expression) 任意數組、數字、串、日期/時間、網絡或者枚舉類型,或者這些類型的數組 與參數數據類型相同 Yes 所有非空輸入值中expression的最大值
min(expression) 任意數組、數字、串、日期/時間、網絡或者枚舉類型,或者這些類型的數組 與參數數據類型相同 Yes 所有非空輸入值中expression的最小值
string_agg(expression, delimiter) (text, text) 或 (bytea, bytea) 與參數數據類型相同 No 非空輸入值連接成一個串,用定界符分隔
sum(expression) smallint、int、 bigint、real、double precision、numeric、 interval或money 對smallint或int參數是bigint,對bigint參數是numeric,否則和參數數據類型相同 Yes 所有非空輸入值的expression的和
xmlagg(expression) xml xml No 連接非空XML值

8.3 有序集聚集函數

函數 參數類型 返回類型 部分模式 描述
corr(Y, X) double precision double precision Yes 相關系數
covar_pop(Y, X) double precision double precision Yes 總體協方差
covar_samp(Y, X) double precision double precision Yes 樣本協方差
regr_avgx(Y, X) double precision double precision Yes 自變量的平均值 (sum(X)/N)
regr_avgy(Y, X) double precision double precision Yes 因變量的平均值 (sum(Y)/N)
regr_count(Y, X) double precision bigint Yes 兩個表達式都不為空的輸入行的數目
regr_intercept(Y, X) double precision double precision Yes 由(X, Y)對決定的最小二乘擬合的線性方程的 y截距
regr_r2(Y, X) double precision double precision Yes 相關系數的平方
regr_slope(Y, X) double precision double precision Yes 由(X, Y)對決定的最小二乘擬合的線性方程的斜率
regr_sxx(Y, X) double precision double precision Yes sum(X^2) – sum(X)^2/N(自變量的“平方和”)
regr_sxy(Y, X) double precision double precision Yes sum(X*Y) – sum(X) * sum(Y)/N(自變量乘以因變量的“積之合”)
regr_syy(Y, X) double precision double precision Yes sum(Y^2) – sum(Y)^2/N(因變量的“平方和”)
stddev(expression) smallint、int、 bigint、real、double precision或numeric 浮點參數為double precision,否則為numeric Yes stddev_samp的歷史別名
stddev_pop(expression) smallint、int、 bigint、real、double precision或numeric 浮點參數為double precision,否則為numeric Yes 輸入值的總體標準偏差
stddev_samp(expression) smallint、int、 bigint、real、double precision或numeric 浮點參數為double precision,否則為numeric Yes 輸入值的樣本標準偏差
variance(expression) smallint、int、 bigint、real、double precision或numeric 浮點參數為double precision,否則為numeric Yes var_samp的歷史別名
var_pop(expression) smallint、int、 bigint、real、double precision或numeric 浮點參數為double precision,否則為numeric Yes 輸入值的總體方差(總體標準偏差的平方)
var_samp(expression) smallint、int、 bigint、real、double precision或numeric 浮點參數為double precision,否則為numeric Yes 輸入值的樣本方差(樣本標準偏差的平方)

8.4 有序數據集

函數 直接參數類型 聚集參數類型 返回類型 部分模式 描述
mode() WITHIN GROUP (ORDER BY sort_expression)   任何可排序類型 與排序表達式相同 No 返回最頻繁的輸入值(如果有多個頻度相同的值就選第一個)
percentile_cont(fraction) WITHIN GROUP (ORDER BY sort_expression) double precision double precision或者interval 與排序表達式相同 No 連續百分率:返回一個對應於排序中指定分數的值,如有必要就在相鄰的輸入項之間插值
percentile_cont(fractions) WITHIN GROUP (ORDER BY sort_expression) double precision[] double precision或者interval 排序表達式的類型的數組 No 多重連續百分率:返回一個匹配fractions參數形狀的結果數組, 其中每一個非空元素都用對應於那個百分率的值替換
percentile_disc(fraction) WITHIN GROUP (ORDER BY sort_expression) double precision 一種可排序類型 與排序表達式相同 No 離散百分率:返回第一個在排序中位置等於或者超過指定分數的輸入值
percentile_disc(fractions) WITHIN GROUP (ORDER BY sort_expression) double precision[] 任何可排序類型 排序表達式的類型的數組 No 多重離散百分率:返回一個匹配fractions參數形狀的結果數組, 其中每一個非空元素都用對應於那個百分率的輸入值替換

8.5 假想集聚集函數(排序)

函數 直接參數類型 聚集參數類型 返回類型 部分模式 描述
rank(args) WITHIN GROUP (ORDER BY sorted_args) VARIADIC “any” VARIADIC “any” bigint No 假想行的排名,為重復的行留下間隔
dense_rank(args) WITHIN GROUP (ORDER BY sorted_args) VARIADIC “any” VARIADIC “any” bigint No 假想行的排名,不留間隔
percent_rank(args) WITHIN GROUP (ORDER BY sorted_args) VARIADIC “any” VARIADIC “any” double precision No 假想行的相對排名,范圍從 0 到 1
cume_dist(args) WITHIN GROUP (ORDER BY sorted_args) VARIADIC “any” VARIADIC “any” double precision   No 假想行的相對排名,范圍從 1/N 到 1

8.6 分組操作

函數 返回類型 描述
GROUPING(args…) integer 整數位掩碼指示哪些參數不被包括在當前分組集合中

使用方法舉例

WITH test_table AS (
	SELECT UNNEST( ARRAY [ '財務', '行政', '銷售', '財務', '行政', '行政' ] ) AS depart,
		UNNEST ( ARRAY [ 'A', 'B', 'A', 'C', 'D', 'C' ] ) AS NAME,
		UNNEST ( ARRAY [ 200, 100, 50, 30, 200, 100 ] ) AS donate 
	) SELECT
	depart,
	NAME,
	GROUPING ( depart, NAME ),
	SUM ( donate ),
	COUNT ( donate ) 
FROM
	test_table 
GROUP BY
	ROLLUP ( depart, NAME );

9.條件類函數

函數 語法 使用說明 使用例子
case CASE WHEN condition THEN result [WHEN …] [ELSE result] END CASE子句可以用於任何表達式可以出現的地方。每一個condition是一個返回boolean結果的表達式。如果結果為真,那麼CASE表達式的結果就是符合條件的result,並且剩下的CASE表達式不會被處理。如果條件的結果不為真,那麼以相同方式搜尋任何隨後的WHEN子句。如果沒有WHEN condition為真,那麼CASE表達式的值就是在ELSE子句裡的result。如果省略瞭ELSE子句而且沒有條件為真,結果為空。 CASE WHEN a=1 THEN ‘one’ WHEN a=2 THEN ‘two’ ELSE 'other’END
coalesce COALESCE(value [, …]) 返回它的第一個非空參數的值。當且僅當所有參數都為空時才會返回空。它常用於在為顯示目的檢索數據時用缺省值替換空值。 COALESCE(description, short_description, ‘(none)’)
nullif NULLIF(value1, value2) 當value1和value2相等時,NULLIF返回一個空值。 否則它返回value1。 NULLIF(value, ‘(none)’)
greatest GREATEST(value [, …]) 從一個任意的數字表達式列表裡選取最大的數值。列表中的 NULL 數值將被忽略。隻有所有表達式的結果都是 NULL 的時候,結果才會是 NULL。 greatest(2,5,1)
least GREATEST(value [, …]) 從一個任意的數字表達式列表裡選取最小的數值。列表中的 NULL 數值將被忽略。隻有所有表達式的結果都是 NULL 的時候,結果才會是 NULL。 least(2,6,5)

10.窗口函數

函數 返回類型 描述
row_number() bigint 當前行在其分區中的行號,從1計
rank() bigint 帶間隙的當前行排名; 與該行的第一個同等行的row_number相同
dense_rank() bigint 不帶間隙的當前行排名; 這個函數計數同等組
percent_rank() double precision 當前行的相對排名: (rank- 1) / (總行數 – 1)
cume_dist() double precision 累積分佈:(在當前行之前或者平級的分區行數) / 分區行總數
ntile(num_buckets integer) integer 從1到參數值的整數范圍,盡可能等分分區
lag(value anyelement [, offset integer [, default anyelement ]]) 和value的類型相同 返回value,它在分區內當前行的之前offset個位置的行上計算;如果沒有這樣的行,返回default替代(必須和value類型相同)。offset和default都是根據當前行計算的結果。如果忽略它們,則offset默認是1,default默認是空值
lead(value anyelement [, offset integer [, default anyelement ]]) 和value類型相同 返回value,它在分區內當前行的之後offset個位置的行上計算;如果沒有這樣的行,返回default替代(必須和value類型相同)。offset和default都是根據當前行計算的結果。如果忽略它們,則offset默認是1,default默認是空值
first_value(value any) same type as value 返回在窗口幀中第一行上計算的value
last_value(value any) 和value類型相同 返回在窗口幀中最後一行上計算的value
nth_value(value any, nth integer) 和value類型相同 返回在窗口幀中第nth行(行從1計數)上計算的value;沒有這樣的行則返回空值

11.查看內部所有函數

如何查看postgresql中所有的函數名稱,SQL語句如下:

-- 查看所有函數名,返回類型,及參數個數
SELECT
  pg_proc.proname AS "函數名稱",
  pg_type.typname AS "返回值數據類型",
  pg_proc.pronargs AS "參數個數"
FROM
  pg_proc
JOIN pg_type ON (pg_proc.prorettype = pg_type.oid)
-- WHERE pronamespace = (SELECT pg_namespace.oid FROM pg_namespace WHERE nspname = '模式')

總結

到此這篇關於Postgresql常用函數及使用方法詳解的文章就介紹到這瞭,更多相關Postgresql函數使用內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!

推薦閱讀: