Postgresql學習筆記
使用可選的步長形參,來生成整數序列
select x from generate_series(1,51,13) as x;
字元和字串lpad(要補齊的字串,總長度,補上的字串)
select lpad('ab',4,'0') as ab_lpad, rpad('ab',4,'0') as ab_rpad, lpad('abcde',4,'0') as ab_lpad_trunc;
split_part(string text, delimiter text, column integer)
select split_part('abc.123.z45', '.', 2);
select split_part('abc_123_z45', '_', 2);
string_to_array(text, text)可以將一個字串 拆分成為一個陣列
select a[1] from string_to_array('abc_123_z45', '_') as a;
"unnest"(anyarray) string_to_array 和 unnest結合使用, 可以將字串展開為若干記錄
select a[1] from string_to_array('abc_123_z45', '_') as a;
正規表示式和模式匹配
\1 和 \2是模式匹配表示式中的元素. ( 是特殊字元 ( 的轉義
select regexp_replace(
'6197306254',
'([0-9]{3})([0-9]{3})([0-9]{4})',
E'\(\\1) \\2-\\3') as x;`
將文字中的電話號碼作為單獨的行返回
select unnest(regexp_matches('Cell (619)852-5083. Casa 619-730-6254. Besame mucho.',
E'[(]{0,1}[0-9]{3}[)-.]{0,1}[0-9]{3}[-.]{0,1}[0-9]{4}','g')
) as x;
除了正規表示式專用的那些函式外,正規表示式還和 ~ 運算子 一起使用,如下:查出所有內嵌了電話號碼的字串
select description from mytable
where description ~ E'[(]{0,1}[0-9]{3}[)-.]{0,1}[0-9]{3}[-.]{0,1}[0-9]{4}';
5.3時間型別
最先進的,最全的時間型別
一個小時前
select current_timestamp - interval '1 hour';
提取當前小時
select extract(hour from current_timestamp);
獲取當前時間戳
select current_timestamp;
select now()::timestamp(0)without time zone;
select to_timestamp(to_char(current_timestamp,'yyyy-mm-dd hh24'),'yyyy-mm-dd hh24');
select EXTRACT(HOUR FROM current_timestamp) - 1;
1. date
該型別僅儲存 月,日,年,沒有失去 , 小時 , 分 和 秒 的資訊
2. time
該型別僅儲存 小時,分,秒 資訊, 不帶日期 和 時區資訊
3. timestamp
該資料型別儲存了日期(年月日) 和 時間(時分秒)
4. timestamptz
儲存了日期 和 時間 以及時區
5. timetz
攜帶了時區資訊 沒有日期資訊
6. interval
該型別描述了時間段的長度,單位可以是小時,天, 月, 分鐘, 或者 其他粒度.該型別適用於數學運算場景
7. tsrange
開區間閉區間限定符,定義了一個 從14:00 開始 到 15:00之前結束的一個時間段 timestamp
select '[2012-01-01 14:00 2012-01-01 15:00)'::tsrange;
8. tstzrange
定義 timestamptz 的開區間和閉區間
9. daterange
定義日期的開區間和閉區間
select '2016-05-26 3:43:00 PM'::timestamp - interval '1 hour';
select '2016-05-26 15:43:00'::timestamp - interval '1 hour';
重疊運算, 對時間戳和日期型別 使用 OVERLAPS overlaps 時間區域重疊運算子
select ('2012-10-25 10:00 AM'::timestamp ,'2012-10-25 2:00 PM'::timestamp) overlaps
('2012-10-25 11:00 AM'::timestamp ,'2012-10-25 2:00 PM'::timestamp) as x,
('2012-10-25'::date ,'2012-10-26'::date) overlaps
('2012-10-26'::date ,'2012-10-27'::date) as y;`
generate_series 用 interval 作為步長生成時間序列陣列
select (dt - interval '1 day')::date as eom
from generate_series('2012-02-01','2012-06-30',interval '1 month') as dt;
date_part 或者 to_char 從日期型別和時間型別的資料值中抽取一部分
select dt, date_part('hour',dt) as mh,to_char(dt,'HH12:MI:AM') as tm
from generate_series('2012-03-11 12:30 AM','2012-03-11 3:00 AM',interval '15 minutes') as dt;
generate_series函式預設生成的是timestamptz型別資料,需要顯示轉換為timestamp型別
更多時間計算問題請見:https://www.cnblogs.com/mchina/archive/2013/04/15/3010418.html
5.4 陣列型別
5.4.1 陣列建構函式
array(子查詢,或者 陣列)
select array[2001,2002,2003] as yrs;
select array(
select distinct date_part('year',log_ts) from logs order_by date_part('year',log_ts)
);
可以直接把一個字串格式書寫的陣列轉化為真正的陣列,語法如下
select '{Alex,Sonia}'::text[] as name, '{43,40}'::smallint[] as age;
string_to_array(text, text) 將一個用固定分隔符分隔的字串轉化為陣列
select string_to_array('ca.ma.tx','.') as estados;
array_agg是一種變型聚合函式,它可以使用一組任何型別的資料並將其轉換為陣列
select array_agg(log_ts order by log_ts) as x from logs
where log_ts between '2011-01-01'::timestamptz and '2011-01-15'::timestamptz;
5.4.2 引用陣列中的元素
select a[1] from string_to_array('abc_123_z45_12s_12_we', '_') as a;
陣列拆分與連線
第一個和第5個元素 a[1:5]
select a[1:5] from string_to_array('abc_123_z45_12s_12_we', '_') as a;
select a[1:3] || a[4:5] from string_to_array('abc_123_z45_12s_12_we', '_') as a;
將陣列元素展開為記錄行 unnest
select unnest('{xox,oxo,xox}'::char(3)[]) as tic_tac_toe
你可以在一個 SELECT 語句中使用多個 unnest 函式，
但如果每個 unnest 展開後的記錄行數不一致，或者說“對不齊”，
那麼得到的最終結果將是這些結果集之間的笛卡兒積，看起來不太好理解。
SELECT
unnest('{three,blind,mice}'::text[]) As t,
unnest('{1,2,3}'::smallint[]) As i;
如果你從上述一個陣列中拿掉一個元素，
那麼兩個陣列的元素就無法對齊了，此時展開得到的結果如示例 5-17 所示。
SELECT
unnest( '{blind,mouse}'::varchar[]) As v,
unnest('{1,2,3}'::smallint[]) As i;
示例 5-18：使用多實參 unnest 取消不平衡陣列的巢狀
多實參 unnest 函式,只能在from 子句中出現
SELECT * FROM unnest('{blind,mouse}'::text[], '{1,2,3}'::int[]) As f(t,i);
5.5 區間型別
select int4range(1,5) = '[1,4]'::int4range;
select int4range(1,4) = '[1,4]'::int4range;
select int4range(1,5) @> '[1,4]'::int4range;
select 4 <@ '[1,4]'::int4range;
區間操作和集合操作
select '[2011-01-01,2011-03-01)'::tsrange @> '2011-01-10'::timestamp;
select '2011-01-10'::timestamp between '2011-01-01'::timestamp and '2011-01-10'::timestamp ;
離散區間
int4range、int8range
這是整數型離散區間，其定義符合前閉後開的規範化要求。
select '[-2,2]'::int4range; --[-2,3)
select 1 <@ '[-2,2]'::int4range; --t
select 1.2 <@ '[-2,2]'::int4range; --錯誤
--錯誤: 操作符不存在: numeric <@ int4range --沒有匹配指定名稱和引數型別的操作符. 您也許需要增加明確的型別轉換.
numrange
-- 這是連續區間，可以用於描述小數、浮點數、或者雙精度數字的區間。
select '[-2,2]'::numrange;--[-2,2]
select 1.2 <@ '[-2,2]'::numrange;--t
daterange
-- 這是不帶時區資訊的日期離散區間。
select '[2011-01-01,2011-03-01)'::daterange --[2011-01-01,2011-03-01)
SELECT daterange('2013-01-05','infinity','[]'); --[2013-01-05,infinity)
tsrange、tstzrange
這是時間戳（日期加時間）型別的連續區間，秒值部分支援小數。tsrange 不帶時區資訊，tstzrange 帶時區資訊。
5.5.4 定義含區間型別欄位的表
-- 時間型別區間是很常用的，假設你有一張 employment 表，表中儲存了公司聘請僱員的歷史記錄。 -- 你可以像示例 5-20 那樣用時間區間來定義一個員工在公司的服務年限， -- 而不需要用起始時間和結束時間兩個欄位來表示。在本例中， -- 我們給 period 列新增了一個索引以使用我們的區間列加速查詢。
CREATE TABLE employment (id serial PRIMARY KEY, employee varchar(20), period daterange);
CREATE INDEX idx_employment_period ON employment USING gist (period);
INSERT INTO employment (employee, period)
VALUES ('Alex', '[2012-04-24, infinity)'::daterange),
('Sonia', '[2011-04-24,2012-06-01)'::daterange),
('Leo', '[2012-06-20, 2013-04-20)'::daterange),
('Regina', '[2012-06-20, 2013-04-20)'::daterange);
5.5.5 適用於區間型別的運算子
-- 區間型別上用得最多的兩個運算子是重疊運算子（&&）和包含運算子（@>）。 -- 要了解區間運算子的完整列表，請參考 PostgreSQL 官方手冊中的“區間型別運算子”一節
1. 重疊運算子
-- 顧名思義，重疊運算子 && 的作用就是判定兩個區間是否有重疊部分，如果有則返回 true，否則返回 false。
示例 5-21：查詢誰與誰曾經同時在公司工作過
SELECT e1.employee, string_agg(DISTINCT e2.employee, ', ' ORDER BY e2.employee) As
colleagues
FROM employment As e1 INNER JOIN employment As e2
ON e1.period && e2.period
WHERE e1.employee <> e2.employee
GROUP BY e1.employee;
-- employee | colleagues
-- ---------+-------
-- Alex | Leo, Regina, Sonia
-- Leo | Alex, Regina
-- Regina | Alex, Leo
-- Sonia | Alex
2. 包含與被包含關係運算子
SELECT employee FROM employment WHERE period @> CURRENT_DATE GROUP BY employee;
-- employee
-- ----
-- Alex
5.6 JSON資料型別
CREATE TABLE families_j (id serial PRIMARY KEY, profile json);
INSERT INTO families_j (profile) VALUES (
'{"name":"Gomez", "members":[
{"member":{"relation":"padre", "name":"Alex"}},
{"member":{"relation":"madre", "name":"Sonia"}},
{"member":{"relation":"hijo", "name":"Brandon"}},
{"member":{"relation":"hija", "name":"Azaleah"}}
]}');
json_extract_path、json_array_elements 以及 json_extract_path_text 這三個函式來讀取表中所有家庭成員的資訊。
SELECT json_extract_path_text(profile, 'name') As family,
json_extract_path_text(
json_array_elements(
json_extract_path(profile,'members')), 'member','name' ) As member
FROM families_j;
select json_extract_path_text(json_array_elements(json_extract_path(profile,'members')),'member','name') from families_j;
運算子 ->> 和 #>> 是 json_extract_path_text 的簡寫。#>> 取用某個路徑陣列。
--示例 5-25 使用這些符號運算子對示例 5-24 進行了重寫。
5.6.3 輸出JSON資料
SELECT row_to_json(f) As x
FROM (SELECT id, profile->>'name' As name FROM families_j) As f;
5.8.1 所有表都有一個對應的自定義資料型別
5.8.2 構建自定義資料型別
-- 儘管僅僅通過建表就可以輕鬆建立複合資料型別， -- 但有時候我們仍會需要從頭開始構建自己的資料型別。例如，使用以下語句可以構建一個複雜數字資料型別：
CREATE TYPE complex_number AS (r double precision, i double precision);
CREATE TABLE circuits (circuit_id serial PRIMARY KEY, ac_volt complex_number);
SELECT circuit_id, (ac_volt).* FROM circuits;
SELECT circuit_id, (ac_volt).r, (ac_volt).i FROM circuits;
5.8.3 為自定義資料型別構建運算子和函式
在構建自定義資料型別後，你自然就會需要為其建立相應的函式和運算子。 我們接下來將演示如何為 complex_number 型別建立一個 + 運算子， 而建立處理函式的方法將放在本書後面的第 8 章中進行介紹。我們在前面已經介紹過， 每個運算子都有一個底層實現函式，該函式需要一個或者兩個實參，運算子就是這個函式的符號化別名。 在 PostgreSQL 官方手冊的“建立運算子”這一節 （http://www.postgresql.org/docs/current/interactive/sql-createoperator.html） 中你可以看到系統允許使用哪些字元來定義新的運算子。
運算子不僅僅是其底層實現函式的別名，它還可以提供一些可以幫助規劃器更好工作的優化資訊， 規劃器藉助這些資訊可以判定如何使用索引，如何以最低的成本訪問資料，以及哪些運算子表示式是等價的。 這些資訊的完整列表以及每一類資訊的具體作用可以參考官方手冊中“運算子的優化資訊”這一節的內容 （http://www.postgresql.org/docs/current/interactive/xoper-optimization.html）。
建立運算子的第一步是建立其底層實現函式，如示例 5-34 所示。
示例 5-34：為 complex_number 建立底層實現函式
CREATE OR REPLACE FUNCTION add(complex_number, complex_number) RETURNS complex_number AS
$$
SELECT ( (COALESCE(($1).r,0) + COALESCE(($2).r,0)),
(COALESCE(($1).i,0) + COALESCE(($2).i,0)) )::complex_number;
$$
language sql;
接下來要建立一個運算子來代表此函式，如示例 5-35 所示。
示例 5-35：為 complex_number 型別定義 + 運算子
CREATE OPERATOR +(
PROCEDURE = add,
LEFTARG = complex_number,
RIGHTARG = complex_number,
COMMUTATOR = +);
然後我們測試一下這個新的 + 運算子：
SELECT (1,2)::complex_number + (3,-10)::complex_number;
-- 輸出結果是 (4,-8)。
-- 雖然我們在此處沒有舉例說明，但你可以對函式和運算子進行過載，
-- 以使其可以接受多種不同型別的輸入。例如，你可以建立一個支援 complex_number
-- 和 integer 相加的 add 函式和相應的 + 計算符，這就實現了對原邏輯的擴充套件。
-- 支援自定義資料型別和運算子讓 PostgreSQL 從機制上具有了自我演進的能力，
-- 開源社群無數開發人員利用此能力為 PostgreSQL 平臺添磚加瓦，
-- 隨著這個開發平臺的羽翼日漸豐滿，我們離“一切皆以表驅動”的理想境界也越來越近。
select dt
from generate_series('2016-10-20 15:00:00','2016-11-02 10:00:00',interval '1 hour') as dt;
相關文章
- 【PostgreSQL】入門學習筆記SQL筆記
- numpy的學習筆記\pandas學習筆記筆記
- PostgreSQL筆記SQL筆記
- postgresql 筆記SQL筆記
- IT學習筆記筆記
- 學習筆記筆記
- 《JAVA學習指南》學習筆記Java筆記
- postgreSql 使用筆記SQL筆記
- CUUG筆記 ORACLE索引學習筆記筆記Oracle索引
- Elasticsearch學習筆記Elasticsearch筆記
- 字串學習筆記字串筆記
- Scala學習筆記筆記
- MySql學習筆記MySql筆記
- jQuery 學習筆記jQuery筆記
- react學習筆記React筆記
- 學習筆記（4.3）筆記
- 學習筆記（4.4）筆記
- 學習筆記（3.29）筆記
- 學習筆記（4.1）筆記
- AOP學習筆記筆記
- AspectJ學習筆記筆記
- 學習筆記（3.27）筆記
- 學習筆記（4.2）筆記
- golang 學習筆記Golang筆記
- Zookeeper學習筆記筆記
- 學習筆記（3.24）筆記
- 學習筆記（3.25）筆記
- 學習筆記（3.21）筆記
- GitHub學習筆記Github筆記
- jest 學習筆記筆記
- CSS 學習筆記CSS筆記
- typescript 學習筆記TypeScript筆記
- Echarts學習筆記Echarts筆記
- js學習筆記JS筆記
- shell學習筆記筆記
- Dubbo 學習筆記筆記
- Docker學習筆記Docker筆記
- SVN 學習筆記筆記