六、模式比對:
PostgreSQL中提供了三種實現模式比對的方法:SQL LIKE操作符,更近一些的SIMILAR TO操作符,和POSIX-風格Regex。
1. LIKE:
複製代碼 代碼如下:
string LIKE pattern [ ESCAPE escape-character ]
string NOT LIKE pattern [ ESCAPE escape-character ]
每個pattern定義一個字串的集合。如果該string包含在pattern代表的字串集合裡,那麼LIKE運算式返回真。和我們想象的一樣,如果LIKE返回真,那麼NOT LIKE運算式返回假,反之亦然。在pattern裡的底線(_)代表匹配任何單個字元,而一個百分比符號(%)匹配任何零或更多字元,如:
複製代碼 代碼如下:
'abc' LIKE 'abc' true
'abc' LIKE 'a%' true
'abc' LIKE '_b_' true
'abc' LIKE 'c' false
要匹配文本的底線或者百分比符號,而不是匹配其它字元,在pattern裡相應的字元必須前置逸出字元。預設的逸出字元是反斜線,但是你可以用ESCAPE子句指定一個。要匹配逸出字元本身,寫兩個逸出字元。我們也可以通過寫成ESCAPE ''的方式有效地關閉轉義機制,此時,我們就不能關閉底線和百分比符號的特殊含義了。
關鍵字ILIKE可以用於替換LIKE,令該匹配就當前的地區設定是大小寫無關的。這個特性不是SQL標準,是PostgreSQL的擴充。操作符~~等效於LIKE, 而~~*對應ILIKE。還有!~~和!~~*操作符分別代表NOT LIKE和NOT ILIKE。所有這些操作符都是PostgreSQL特有的。
2. SIMILAR TORegex:
SIMILAR TO根據模式是否匹配給定的字串而返回真或者假。
複製代碼 代碼如下:
string SIMILAR TO pattern [ESCAPE escape-character]
string NOT SIMILAR TO pattern [ESCAPE escape-character]
它和LIKE非常類似,支援LIKE的萬用字元('_'和'%')且保持其原意。除此之外,SIMILAR TO還支援一些自己專屬的元字元,如:
1). | 標識選擇(兩個候選之一)。
2). * 表示重複前面的項零次或更多次。
3). + 表示重複前面的項一次或更多次。
4). 可以使用圓括弧()把項組合成一個邏輯項。
5). 一個方括弧運算式[...]聲明一個字元表,就像POSIXRegex一樣。
見如下樣本:
複製代碼 代碼如下:
'abc' SIMILAR TO 'abc' true
'abc' SIMILAR TO 'a' false
'abc' SIMILAR TO '%(b|d)%' true
'abc' SIMILAR TO '(b|c)%' false
帶三個參數的substring,substring(string from pattern for escape-character),提供了一個從字串中抽取一個匹配SQLRegex模式的子字串的函數。和SIMILAR TO一樣,聲明的模式必須匹配整個資料串,否則函數失效並返回NULL。為了標識在成功的時候應該返回的模式部分,模式必須出現後跟雙引號(")的兩個逸出字元。匹配這兩個標記之間的模式的字串將被返回,如:
MyTest=# SELECT substring('foobar' from '%#"o_b#"%' FOR '#'); --這裡#是轉義符,雙引號內的模式是返回部分。
複製代碼 代碼如下:
substring
-----------
oob
(1 row)
MyTest=# SELECT substring('foobar' from '#"o_b#"%' FOR '#'); --foobar不能完全符合後面的模式,因此返回NULL。
substring
-----------
(1 row)
七、資料類型格式化函數:
PostgreSQL格式化函數提供一套有效工具用於把各種資料類型(日期/時間、integer、floating point和numeric)轉換成格式化的字串以及反過來從格式化的字串轉換成指定的資料類型。下面列出了這些函數,它們都遵循一個公用的調用習慣:第一個參數是待格式化的值,而第二個是定義輸出或輸出格式的模板。
函數 |
傳回型別 |
描述 |
例子 |
to_char(timestamp, text) |
text |
把時間戳記轉換成字串 |
to_char(current_timestamp, 'HH12:MI:SS') |
to_char(interval, text) |
text |
把時間間隔轉為字串 |
to_char(interval '15h 2m 12s', 'HH24:MI:SS') |
to_char(int, text) |
text |
把整數轉換成字串 |
to_char(125, '999') |
to_char(double precision, text) |
text |
把實數/雙精確度數轉換成字串 |
to_char(125.8::real, '999D9') |
to_char(numeric, text) |
text |
把numeric轉換成字串 |
to_char(-125.8, '999D99S') |
to_date(text, text) |
date |
把字串轉換成日期 |
to_date('05 Dec 2000', 'DD Mon YYYY') |
to_timestamp(text, text) |
timestamp |
把字串轉換成時間戳記 |
to_timestamp('05 Dec 2000', 'DD Mon YYYY') |
to_timestamp(double) |
timestamp |
把UNIX紀元轉換成時間戳記 |
to_timestamp(200120400) |
to_number(text, text) |
numeric |
把字串轉換成numeric |
to_number('12,454.8-', '99G999D9S') |
1. 用於日期/時間格式化的模式:
模式 |
描述 |
HH |
一天的小時數(01-12) |
HH12 |
一天的小時數(01-12) |
HH24 |
一天的小時數(00-23) |
MI |
分鐘(00-59) |
SS |
秒(00-59) |
MS |
毫秒(000-999) |
US |
微秒(000000-999999) |
AM |
正午標識(大寫) |
Y,YYY |
帶逗號的年(4和更多位) |
YYYY |
年(4和更多位) |
YYY |
年的後三位 |
YY |
年的後兩位 |
Y |
年的最後一位 |
MONTH |
全長大寫月份名(空白填充為9字元) |
Month |
全長混合大小寫月份名(空白填充為9字元) |
month |
全長小寫月份名(空白填充為9字元) |
MON |
大寫縮寫月份名(3字元) |
Mon |
縮寫混合大小寫月份名(3字元) |
mon |
小寫縮寫月份名(3字元) |
MM |
月份號(01-12) |
DAY |
全長大寫日期名(空白填充為9字元) |
Day |
全長混合大小寫日期名(空白填充為9字元) |
day |
全長小寫日期名(空白填充為9字元) |
DY |
縮寫大寫日期名(3字元) |
Dy |
縮寫混合大小寫日期名(3字元) |
dy |
縮寫小寫日期名(3字元) |
DDD |
一年裡的日子(001-366) |
DD |
一個月裡的日子(01-31) |
D |
一周裡的日子(1-7;周日是1) |
W |
一個月裡的周數(1-5)(第一周從該月第一天開始) |
WW |
一年裡的周數(1-53)(第一周從該年的第一天開始) |
2. 用於數值格式化的模板模式:
模式 |
描述 |
9 |
帶有指定數值位元的值 |
0 |
帶前置字元為零的值 |
.(句點) |
小數點 |
,(逗號) |
分組(千)分隔字元 |
PR |
角括弧內負值 |
S |
帶符號的數值 |
L |
貨幣符號 |
D |
小數點 |
G |
分組分隔字元 |
MI |
在指明的位置的負號(如果數字 < 0) |
PL |
在指明的位置的正號(如果數字 > 0) |
SG |
在指明的位置的正/負號 |
八、時間/日期函數和操作符:
1. 下面是PostgreSQL中支援的時間/日期操作符的列表:
操作符 |
例子 |
結果 |
+ |
date '2001-09-28' + integer '7' |
date '2001-10-05' |
+ |
date '2001-09-28' + interval '1 hour' |
timestamp '2001-09-28 01:00' |
+ |
date '2001-09-28' + time '03:00' |
timestamp '2001-09-28 03:00' |
+ |
interval '1 day' + interval '1 hour' |
interval '1 day 01:00' |
+ |
timestamp '2001-09-28 01:00' + interval '23 hours' |
timestamp '2001-09-29 00:00' |
+ |
time '01:00' + interval '3 hours' |
time '04:00' |
- |
- interval '23 hours' |
interval '-23:00' |
- |
date '2001-10-01' - date '2001-09-28' |
integer '3' |
- |
date '2001-10-01' - integer '7' |
date '2001-09-24' |
- |
date '2001-09-28' - interval '1 hour' |
timestamp '2001-09-27 23:00' |
- |
time '05:00' - time '03:00' |
interval '02:00' |
- |
time '05:00' - interval '2 hours' |
time '03:00' |
- |
timestamp '2001-09-28 23:00' - interval '23 hours' |
timestamp '2001-09-28 00:00' |
- |
interval '1 day' - interval '1 hour' |
interval '23:00' |
- |
timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00' |
interval '1 day 15:00' |
* |
interval '1 hour' * double precision '3.5' |
interval '03:30' |
/ |
interval '1 hour' / double precision '1.5' |
interval '00:40' |
2. 日期/時間函數:
函數 |
傳回型別 |
描述 |
例子 |
結果 |
age(timestamp, timestamp) |
interval |
減去參數,產生一個使用年、月的"符號化"的結果 |
age('2001-04-10', timestamp '1957-06-13') |
43 years 9 mons 27 days |
age(timestamp) |
interval |
從current_date減去得到的數值 |
age(timestamp '1957-06-13') |
43 years 8 mons 3 days |
current_date |
date |
今天的日期 |
|
|
current_time |
time |
現在的時間 |
|
|
current_timestamp |
timestamp |
日期和時間 |
|
|
date_part(text, timestamp) |
double |
擷取子域(等效於extract) |
date_part('hour', timestamp '2001-02-16 20:38:40') |
20 |
date_part(text, interval) |
double |
擷取子域(等效於extract) |
date_part('month', interval '2 years 3 months') |
3 |
date_trunc(text, timestamp) |
timestamp |
截斷成指定的精度 |
date_trunc('hour', timestamp '2001-02-16 20:38:40') |
2001-02-16 20:00:00+00 |
extract(field from timestamp) |
double |
擷取子域 |
extract(hour from timestamp '2001-02-16 20:38:40') |
20 |
extract(field from interval) |
double |
擷取子域 |
extract(month from interval '2 years 3 months') |
3 |
localtime |
time |
今日的時間 |
|
|
localtimestamp |
timestamp |
日期和時間 |
|
|
now() |
timestamp |
當前的日期和時間(等效於 current_timestamp) |
|
|
timeofday() |
text |
當前日期和時間 |
|
|
3. EXTRACT,date_part函數支援的field:
域 |
描述 |
例子 |
結果 |
CENTURY |
世紀 |
EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13'); |
20 |
DAY |
(月分)裡的日期域(1-31) |
EXTRACT(DAY from TIMESTAMP '2001-02-16 20:38:40'); |
16 |
DECADE |
年份域除以10 |
EXTRACT(DECADE from TIMESTAMP '2001-02-16 20:38:40'); |
200 |
DOW |
每周的星期號(0-6;星期天是0) (僅用於timestamp) |
EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40'); |
5 |
DOY |
一年的第幾天(1 -365/366) (僅用於 timestamp) |
EXTRACT(DOY from TIMESTAMP '2001-02-16 20:38:40'); |
47 |
HOUR |
小時域(0-23) |
EXTRACT(HOUR from TIMESTAMP '2001-02-16 20:38:40'); |
20 |
MICROSECONDS |
秒域,包括小數部分,乘以 1,000,000。 |
EXTRACT(MICROSECONDS from TIME '17:12:28.5'); |
28500000 |
MILLENNIUM |
千年 |
EXTRACT(MILLENNIUM from TIMESTAMP '2001-02-16 20:38:40'); |
3 |
MILLISECONDS |
秒域,包括小數部分,乘以 1000。 |
EXTRACT(MILLISECONDS from TIME '17:12:28.5'); |
28500 |
MINUTE |
分鐘域(0-59) |
EXTRACT(MINUTE from TIMESTAMP '2001-02-16 20:38:40'); |
38 |
MONTH |
對於timestamp數值,它是一年裡的月份數(1-12);對於interval數值,它是月的數目,然後對12模數(0-11) |
EXTRACT(MONTH from TIMESTAMP '2001-02-16 20:38:40'); |
2 |
QUARTER |
該天所在的該年的季度(1-4)(僅用於 timestamp) |
EXTRACT(QUARTER from TIMESTAMP '2001-02-16 20:38:40'); |
1 |
SECOND |
秒域,包括小數部分(0-59[1]) |
EXTRACT(SECOND from TIMESTAMP '2001-02-16 20:38:40'); |
40 |
WEEK |
該天在所在的年份裡是第幾周。 |
EXTRACT(WEEK from TIMESTAMP '2001-02-16 20:38:40'); |
7 |
YEAR |
年份域 |
EXTRACT(YEAR from TIMESTAMP '2001-02-16 20:38:40'); |
2001 |
4. 當前日期/時間:
我們可以使用下面的函數擷取當前的日期和/或時間∶
複製代碼 代碼如下:
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME (precision)
CURRENT_TIMESTAMP (precision)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME (precision)
LOCALTIMESTAMP (precision)
該部落格中提供的所有資訊均源自PostgreSQL官方文檔,編寫該篇部落格的主要目的是便於今後的查閱,特此聲明。