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!
推薦閱讀:
- PostgreSQL數據類型格式化函數操作
- Postgres bytea類型 轉換及查看操作
- postgresql 索引之 hash的使用詳解
- 淺談PostgreSQL和SQLServer的一些差異
- PostgreSQL中的日期/時間函數詳解