亚洲乱码中文字幕综合,中国熟女仑乱hd,亚洲精品乱拍国产一区二区三区,一本大道卡一卡二卡三乱码全集资源,又粗又黄又硬又爽的免费视频

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

 更新時(shí)間：2022年11月15日 10:46:13   作者：theskylife  
使用函數(shù)可以極大的提高用戶對(duì)數(shù)據(jù)庫(kù)的管理效率,函數(shù)表示輸入?yún)?shù)表示一個(gè)具有特定關(guān)系的值,下面這篇文章主要給大家介紹了關(guān)于Postgresql常用函數(shù)及使用方法的相關(guān)資料,需要的朋友可以參考下

前言

寫在前面，當(dāng)我們?cè)谟胮ostgresql數(shù)據(jù)庫(kù)進(jìn)行數(shù)據(jù)取數(shù)時(shí)，難免會(huì)遇到一些數(shù)據(jù)處理上的場(chǎng)景，本文會(huì)針對(duì)一些常用的內(nèi)置函數(shù)進(jìn)行總結(jié)。

-- 查看postgresql的版本(以下任一語(yǔ)句都可以實(shí)現(xiàn))
select version();
show server_version;

1.格式轉(zhuǎn)換

1.1 格式轉(zhuǎn)換符顯示轉(zhuǎn)換

利用雙冒號(hào)可以直接進(jìn)行格式轉(zhuǎn)換，語(yǔ)法如下：

字段名或數(shù)值::數(shù)據(jù)類型

例子如下：

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

1.2 利用數(shù)據(jù)類型顯示轉(zhuǎn)換

利用數(shù)據(jù)類型進(jìn)行轉(zhuǎn)換，語(yǔ)法如下：

數(shù)據(jù)類型數(shù)值

例子：

-- 將文本'123'轉(zhuǎn)為int8類型
SELECT
    int8'123' num;

1.3 格式轉(zhuǎn)換函數(shù)顯示轉(zhuǎn)換

利用數(shù)據(jù)轉(zhuǎn)換函數(shù)cast進(jìn)行轉(zhuǎn)換，語(yǔ)法如下：

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

例子：

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

1.4 轉(zhuǎn)換案例

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

2.數(shù)學(xué)計(jì)算

2.1 數(shù)學(xué)運(yùn)算操作符

操作符描述例子結(jié)果
+加2 + 35
-減2 - 3-1
*乘2 * 36
/除（整數(shù)除法截?cái)嘟Y(jié)果）4 / 22
%模（取余）5 % 41
^指數(shù)（從左至右結(jié)合）2.0 ^ 3.08
|/平方根|/ 25.05
||/立方根||/ 27.03
!階乘5 !120
!!階乘（前綴操作符）!! 5120
@絕對(duì)值@ -5.05
&按位與91 & 1511
|按位或323
#按位異或17 # 520
~按位求反~1-2
<<按位左移1 << 416
>>按位右移8 >> 22

2.2 數(shù)學(xué)運(yùn)算函數(shù)

函數(shù)返回類型描述例子結(jié)果
abs(x)和輸入相同絕對(duì)值abs(-12.43)12.43
cbrt(dp)double立方根cbrt(27.0)3
ceil(dp or numeric)和輸入相同不小于參數(shù)的最近的整數(shù)ceil(-42.8)-42
ceiling(dp or numeric)和輸入相同不小于參數(shù)的最近的整數(shù)（ceil的別名）ceiling(-95.3)-95
degrees(dp)dp把弧度轉(zhuǎn)為角度degrees(0.5)28.6478897565412
div(y numeric, x numeric)numericy/x的整數(shù)商div(9,4)2
exp(dp or numeric)和輸入相同指數(shù)exp(1.0)2.71828182845905
floor(dp or numeric)和輸入相同不大于參數(shù)的最近的整數(shù)floor(-42.8)-43
ln(dp or numeric)和輸入相同自然對(duì)數(shù)ln(2.0)0.693147180559945
log(dp or numeric)和輸入相同以10為底的對(duì)數(shù)log(100.0)2
log10(dp or numeric)和輸入相同以10為底的對(duì)數(shù)log10(100.0)2
log(b numeric, x numeric)numeric以b為底的對(duì)數(shù)log(2.0, 64.0)6.0000000000
mod(y, x)和參數(shù)類型相同y/x的余數(shù)mod(9,4)1
pi()dp“π”常數(shù)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把角度轉(zhuǎn)為弧度radians(45.0)0.785398163397448
round(dp or numeric)和輸入相同圓整為最接近的整數(shù)round(42.4)42
round(v numeric, s int)numeric圓整為s位小數(shù)數(shù)字round(42.4382, 2)42.44
scale(numeric)integer參數(shù)的精度（小數(shù)點(diǎn)后的位數(shù)）scale(8.41)2
sign(dp or numeric)和輸入相同參數(shù)的符號(hào)（-1, 0, +1）sign(-8.4)-1
sqrt(dp or numeric)和輸入相同平方根sqrt(2.0)1.4142135623731
trunc(dp or numeric)和輸入相同截?cái)啵ㄏ蛄憧拷?/td>trunc(42.8)42
trunc(v numeric, s int)numeric截?cái)酁閟位小數(shù)位置的數(shù)字trunc(42.4382, 2)42.43

3.邏輯計(jì)算

3.1 邏輯操作符

postgresql中的邏輯操作符，有以下三種：

  • AND
  • OR
  • NOT

3.2 比較操作符

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

!=操作符在分析器階段被轉(zhuǎn)換成<>

3.3 比較謂詞

謂詞描述
a BETWEEN x AND y在x和y之間
a NOT BETWEEN x AND y不在x和y之間
a BETWEEN SYMMETRIC x AND y在對(duì)比較值排序后位于x和y之間
a NOT BETWEEN SYMMETRIC x AND y在對(duì)比較值排序后不位于x和y之間
a IS DISTINCT FROM b不等于，空值被當(dāng)做一個(gè)普通值
a IS NOT DISTINCT FROM b等于，空值被當(dāng)做一個(gè)普通值
expression IS NULL是空值
expression IS NOT NULL不是空值
expression ISNULL是空值（非標(biāo)準(zhǔn)語(yǔ)法）
expression NOTNULL不是空值（非標(biāo)準(zhǔn)語(yǔ)法）
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 比較函數(shù)

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

4.字符串及相關(guān)匹配函數(shù)

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

5.時(shí)間與日期函數(shù)

5.1時(shí)間類操作符

操作符例子結(jié)果
+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 時(shí)間、日期類函數(shù)

函數(shù)返回類型描述例子結(jié)果
age(timestamp, timestamp)interval減去參數(shù)，生成一個(gè)使用年、月（而不是只用日）的“符號(hào)化”的結(jié)果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-1465 years 1 day
clock_timestamp()timestamp with time zone當(dāng)前日期和時(shí)間（在語(yǔ)句執(zhí)行期間變化）clock_timestamp()2022-06-14 19:06:54.034672+08
current_datedate當(dāng)前日期current_date2022-06-14
current_timetime with time zone當(dāng)前時(shí)間（一天中的時(shí)間），帶時(shí)區(qū)current_time19:11:04.336139+08
current_timestamptimestamp with time zone當(dāng)前日期和時(shí)間（當(dāng)前事務(wù)開(kāi)始時(shí)），帶時(shí)區(qū)current_timestamp2022-06-14 19:11:57.83455+08
date_part(text, timestamp)double precision獲得子域（等價(jià)于extract）date_part(‘hour’, timestamp ‘2022-06-14 20:38:40’)20
date_part(text, interval)double precision獲得子域（等價(jià)于extract）date_part(‘month’, interval ‘2 years 3 months’)3
date_trunc(text, timestamp)timestamp截?cái)嗟街付ň?/td>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在指定的時(shí)區(qū)截?cái)嗟街付ǖ木?/td>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截?cái)嗟街付ň?/td>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測(cè)試有限日期（不是+/-無(wú)限）isfinite(date ‘2022-06-14’)true（實(shí)際縮寫為t）
isfinite(timestamp)boolean測(cè)試有限時(shí)間戳（不是+/-無(wú)限）isfinite(timestamp ‘2022-06-14 21:28:30’)true（實(shí)際縮寫為t）
isfinite(interval)boolean測(cè)試有限間隔isfinite(interval ‘2 minutes’)true（實(shí)際縮寫為t）
justify_days(interval)interval調(diào)整間隔這樣30天時(shí)間周期可以表示為月justify_days(interval ‘35 days’)1 mon 5 days
justify_hours(interval)interval調(diào)整間隔這樣24小時(shí)時(shí)間周期可以表示為日justify_hours(interval ‘27 hours’)1 day 03:00:00
justify_interval(interval)interval使用justify_days和justify_hours調(diào)整間隔，使用額外的符號(hào)調(diào)整justify_interval(interval ‘1 mon -1 hour’)29 days 23:00:00
localtimetime當(dāng)前時(shí)間（一天中的時(shí)間），不帶時(shí)區(qū)localtime19:21:14.958286
localtimestamptimestamp當(dāng)前日期和時(shí)間（當(dāng)前事務(wù)的開(kāi)始），不帶時(shí)區(qū)LOCALTIMESTAMP2022-07-22 19:23:54.073462
make_date(year int, month int, day int)date從年、月、日域創(chuàng)建日期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從年、月、周、日、時(shí)、分、秒域創(chuàng)建intervalmake_interval(days => 10)10 days
make_time(hour int, min int, sec double precision)time從時(shí)、分、秒域創(chuàng)建時(shí)間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從年、月、日、時(shí)、分、秒域創(chuàng)建時(shí)間戳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從年、月、日、時(shí)、分、秒域創(chuàng)建帶時(shí)區(qū)的時(shí)間戳。如果沒(méi)有指定timezone， 則使用當(dāng)前時(shí)區(qū)。make_timestamptz(2022, 6, 14, 19, 30, 50.5)2022-06-14 19:30:50.5+08
now()timestamp with time zone當(dāng)前日期和時(shí)間（當(dāng)前事務(wù)的開(kāi)始），帶時(shí)區(qū)now()2022-07-22 19:28:15.804042+08
statement_timestamp()timestamp with time zone當(dāng)前日期和時(shí)間（當(dāng)前語(yǔ)句的開(kāi)始），在一個(gè)事務(wù)的第一條命令期間返回值與CURRENT_TIMESTAMP相同statement_timestamp()2022-07-22 19:31:35.75589+08
timeofday()text當(dāng)前日期和時(shí)間（像clock_timestamp，但是作為一個(gè)text字符串）timeofday()Fri Jul 22 19:35:19.000959 2022 CST
transaction_timestamp()timestamp with time zone當(dāng)前日期和時(shí)間（當(dāng)前事務(wù)的開(kāi)始）；等同于CURRENT_TIMESTAMPtransaction_timestamp()2022-07-22 19:34:02.369665+08
to_timestamp(double precision)timestamp with time zone把 Unix 時(shí)間（從 1970-01-01 00:00:00+00 開(kāi)始的秒）轉(zhuǎn)換成 timestampto_timestamp(1655211000)2022-06-14 20:50:00+08

6.數(shù)組函數(shù)

6.1 數(shù)組操作符

操作符描述例子結(jié)果
=等于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
||數(shù)組和數(shù)組串接ARRAY[1,2,3] || ARRAY[4,5,6]{1,2,3,4,5,6}
||數(shù)組和數(shù)組串接ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]]{{1,2,3},{4,5,6},{7,8,9}}
||元素到數(shù)組串接3 || ARRAY[4,5,6]{3,4,5,6}
||數(shù)組到元素串接ARRAY[4,5,6] || 7{4,5,6,7}

6.2 數(shù)組函數(shù)

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

7.范圍函數(shù)

7.1 范圍操作符

操作符描述例子結(jié)果
=等于int4range(1,5) = ‘[1,4]’::int4ranget
<>不等于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’::timestampt
<@范圍被包含int4range(2,4) <@ int4range(1,7)t
<@元素被包含42 <@ int4range(1,7)f
&&重疊（有公共點(diǎn)）int8range(3,7) && int8range(4,12)t
<<嚴(yán)格左部int8range(1,10) << int8range(100,110)t
>>嚴(yán)格右部int8range(50,60) >> int8range(20,30)t
&<不超過(guò)右部int8range(1,20) &< int8range(18,20) t 
&>不超過(guò)左部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 范圍函數(shù)

函數(shù)返回類型描述例子結(jié)果
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下界包含在內(nèi)？lower_inc(numrange(1.1,2.2))true
upper_inc(anyrange)boolean上界包含在內(nèi)？upper_inc(numrange(1.1,2.2))false
lower_inf(anyrange)boolean下界無(wú)限？lower_inf(‘(,)’::daterange)true
upper_inf(anyrange)boolean上界無(wú)限？upper_inf(‘(,)’::daterange)true
range_merge(anyrange, anyrange)anyrange包含兩個(gè)給定范圍的最小范圍range_merge(‘[1,2)’::int4range, ‘[3,4)’::int4range)[1,4)

8.聚集函數(shù)

8.1 常用函數(shù)

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

8.2 統(tǒng)計(jì)類函數(shù)

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

8.3 有序集聚集函數(shù)

函數(shù)參數(shù)類型返回類型部分模式描述
corr(Y, X)double precisiondouble precisionYes相關(guān)系數(shù)
covar_pop(Y, X)double precisiondouble precisionYes總體協(xié)方差
covar_samp(Y, X)double precisiondouble precisionYes樣本協(xié)方差
regr_avgx(Y, X)double precisiondouble precisionYes自變量的平均值 （sum(X)/N）
regr_avgy(Y, X)double precisiondouble precisionYes因變量的平均值 （sum(Y)/N）
regr_count(Y, X)double precisionbigintYes兩個(gè)表達(dá)式都不為空的輸入行的數(shù)目
regr_intercept(Y, X)double precisiondouble precisionYes由（X, Y）對(duì)決定的最小二乘擬合的線性方程的 y截距
regr_r2(Y, X)double precisiondouble precisionYes相關(guān)系數(shù)的平方
regr_slope(Y, X)double precisiondouble precisionYes由（X, Y）對(duì)決定的最小二乘擬合的線性方程的斜率
regr_sxx(Y, X)double precisiondouble precisionYessum(X^2) - sum(X)^2/N（自變量的“平方和”）
regr_sxy(Y, X)double precisiondouble precisionYessum(X*Y) - sum(X) * sum(Y)/N（自變量乘以因變量的“積之合”）
regr_syy(Y, X)double precisiondouble precisionYessum(Y^2) - sum(Y)^2/N（因變量的“平方和”）
stddev(expression)smallint、int、 bigint、real、double precision或numeric浮點(diǎn)參數(shù)為double precision，否則為numericYesstddev_samp的歷史別名
stddev_pop(expression)smallint、int、 bigint、real、double precision或numeric浮點(diǎn)參數(shù)為double precision，否則為numericYes輸入值的總體標(biāo)準(zhǔn)偏差
stddev_samp(expression)smallint、int、 bigint、real、double precision或numeric浮點(diǎn)參數(shù)為double precision，否則為numericYes輸入值的樣本標(biāo)準(zhǔn)偏差
variance(expression)smallint、int、 bigint、real、double precision或numeric浮點(diǎn)參數(shù)為double precision，否則為numericYesvar_samp的歷史別名
var_pop(expression)smallint、int、 bigint、real、double precision或numeric浮點(diǎn)參數(shù)為double precision，否則為numericYes輸入值的總體方差（總體標(biāo)準(zhǔn)偏差的平方）
var_samp(expression)smallint、int、 bigint、real、double precision或numeric浮點(diǎn)參數(shù)為double precision，否則為numericYes輸入值的樣本方差（樣本標(biāo)準(zhǔn)偏差的平方）

8.4 有序數(shù)據(jù)集

函數(shù)直接參數(shù)類型聚集參數(shù)類型返回類型部分模式描述
mode() WITHIN GROUP (ORDER BY sort_expression) 任何可排序類型與排序表達(dá)式相同No返回最頻繁的輸入值（如果有多個(gè)頻度相同的值就選第一個(gè)）
percentile_cont(fraction) WITHIN GROUP (ORDER BY sort_expression)double precisiondouble precision或者interval與排序表達(dá)式相同No連續(xù)百分率：返回一個(gè)對(duì)應(yīng)于排序中指定分?jǐn)?shù)的值，如有必要就在相鄰的輸入項(xiàng)之間插值
percentile_cont(fractions) WITHIN GROUP (ORDER BY sort_expression)double precision[]double precision或者interval排序表達(dá)式的類型的數(shù)組No多重連續(xù)百分率：返回一個(gè)匹配fractions參數(shù)形狀的結(jié)果數(shù)組， 其中每一個(gè)非空元素都用對(duì)應(yīng)于那個(gè)百分率的值替換
percentile_disc(fraction) WITHIN GROUP (ORDER BY sort_expression)double precision一種可排序類型與排序表達(dá)式相同No離散百分率：返回第一個(gè)在排序中位置等于或者超過(guò)指定分?jǐn)?shù)的輸入值
percentile_disc(fractions) WITHIN GROUP (ORDER BY sort_expression)double precision[]任何可排序類型排序表達(dá)式的類型的數(shù)組No多重離散百分率：返回一個(gè)匹配fractions參數(shù)形狀的結(jié)果數(shù)組， 其中每一個(gè)非空元素都用對(duì)應(yīng)于那個(gè)百分率的輸入值替換

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

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

8.6 分組操作

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

使用方法舉例

WITH test_table AS (
	SELECT UNNEST( ARRAY [ '財(cái)務(wù)', '行政', '銷售', '財(cái)務(wù)', '行政', '行政' ] ) 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.條件類函數(shù)

函數(shù)語(yǔ)法使用說(shuō)明使用例子
caseCASE WHEN condition THEN result [WHEN …] [ELSE result] ENDCASE子句可以用于任何表達(dá)式可以出現(xiàn)的地方。每一個(gè)condition是一個(gè)返回boolean結(jié)果的表達(dá)式。如果結(jié)果為真，那么CASE表達(dá)式的結(jié)果就是符合條件的result，并且剩下的CASE表達(dá)式不會(huì)被處理。如果條件的結(jié)果不為真，那么以相同方式搜尋任何隨后的WHEN子句。如果沒(méi)有WHEN condition為真，那么CASE表達(dá)式的值就是在ELSE子句里的result。如果省略了ELSE子句而且沒(méi)有條件為真，結(jié)果為空。CASE WHEN a=1 THEN ‘one’ WHEN a=2 THEN ‘two’ ELSE 'other’END
coalesceCOALESCE(value [, …])返回它的第一個(gè)非空參數(shù)的值。當(dāng)且僅當(dāng)所有參數(shù)都為空時(shí)才會(huì)返回空。它常用于在為顯示目的檢索數(shù)據(jù)時(shí)用缺省值替換空值。COALESCE(description, short_description, ‘(none)’)
nullifNULLIF(value1, value2)當(dāng)value1和value2相等時(shí)，NULLIF返回一個(gè)空值。 否則它返回value1。NULLIF(value, ‘(none)’)
greatestGREATEST(value [, …])從一個(gè)任意的數(shù)字表達(dá)式列表里選取最大的數(shù)值。列表中的 NULL 數(shù)值將被忽略。只有所有表達(dá)式的結(jié)果都是 NULL 的時(shí)候，結(jié)果才會(huì)是 NULL。greatest(2,5,1)
leastGREATEST(value [, …])從一個(gè)任意的數(shù)字表達(dá)式列表里選取最小的數(shù)值。列表中的 NULL 數(shù)值將被忽略。只有所有表達(dá)式的結(jié)果都是 NULL 的時(shí)候，結(jié)果才會(huì)是 NULL。least(2,6,5)

10.窗口函數(shù)

函數(shù)返回類型描述
row_number()bigint當(dāng)前行在其分區(qū)中的行號(hào)，從1計(jì)
rank()bigint帶間隙的當(dāng)前行排名； 與該行的第一個(gè)同等行的row_number相同
dense_rank()bigint不帶間隙的當(dāng)前行排名； 這個(gè)函數(shù)計(jì)數(shù)同等組
percent_rank()double precision當(dāng)前行的相對(duì)排名： (rank- 1) / (總行數(shù) - 1)
cume_dist()double precision累積分布：(在當(dāng)前行之前或者平級(jí)的分區(qū)行數(shù)) / 分區(qū)行總數(shù)
ntile(num_buckets integer)integer從1到參數(shù)值的整數(shù)范圍，盡可能等分分區(qū)
lag(value anyelement [, offset integer [, default anyelement ]])和value的類型相同返回value，它在分區(qū)內(nèi)當(dāng)前行的之前offset個(gè)位置的行上計(jì)算；如果沒(méi)有這樣的行，返回default替代（必須和value類型相同）。offset和default都是根據(jù)當(dāng)前行計(jì)算的結(jié)果。如果忽略它們，則offset默認(rèn)是1，default默認(rèn)是空值
lead(value anyelement [, offset integer [, default anyelement ]])和value類型相同返回value，它在分區(qū)內(nèi)當(dāng)前行的之后offset個(gè)位置的行上計(jì)算；如果沒(méi)有這樣的行，返回default替代（必須和value類型相同）。offset和default都是根據(jù)當(dāng)前行計(jì)算的結(jié)果。如果忽略它們，則offset默認(rèn)是1，default默認(rèn)是空值
first_value(value any)same type as value返回在窗口幀中第一行上計(jì)算的value
last_value(value any)和value類型相同返回在窗口幀中最后一行上計(jì)算的value
nth_value(value any, nth integer)和value類型相同返回在窗口幀中第nth行（行從1計(jì)數(shù)）上計(jì)算的value；沒(méi)有這樣的行則返回空值

11.查看內(nèi)部所有函數(shù)

如何查看postgresql中所有的函數(shù)名稱，SQL語(yǔ)句如下：

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

總結(jié)

到此這篇關(guān)于Postgresql常用函數(shù)及使用方法詳解的文章就介紹到這了,更多相關(guān)Postgresql函數(shù)使用內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

相關(guān)文章

最新評(píng)論