PostgreSQL 教學

mac 安裝

1. 透過 homebrew 安裝 postgresql

目前最新版為 14

brew install postgresql

2. 創建 super user

createuser -s postgres

3. 透過 cli 連接至 postgresql

psql -U postgres

假如是透過 homebrew 安裝的可以透過以下指令查看 postgresql.conf

brew info postgresql // 查看 postgresql 相關資訊

進入到以下資料夾位置即可看到 postgresql.conf

cd /usr/local/var/postgresql@14

安裝 postgresql 專用的 GUI

1. 安裝 pgAdmin

File Browser

資料型別 data type:

跟 mysql 不太一樣的是 postgresql 提供了更多樣的資料型別，例如 array 、path、jsonb 等多種型別。

serial 型別:

設定欄位為 auto increment

例如:

以下為建立一個 orders 資料表且 id 為 auto increment

CREATE TABLE orders (
    ID serial NOT NULL PRIMARY KEY,
    info json NOT NULL
);

參考資料:

PostgreSQL Serial Type

array 型別:

提供了 integer array 以及 string array 等陣列相關的資料型態。

time 型別:

儲存時間的型別，範圍從 00:00 到 24:00 且精度可以到秒的小數點 6位數。

以下為格式範例:

1. HH:MM

2. HH:MM:SS

3. HHMMSS

4. HH:MM:SS pppppp

取得目前時間（有 timezone)

SELECT current_time;

取得當地時間 localtime (沒有 timezone)

SELECT localtime;

將 current_time 進行時區轉換

SELECT current_time AS TIME ZONE 'timezone'

以下範例將 current_time 轉為 EST 時區

SELECT current_time AS TIME ZONE 'EST'

時間運算:

1. 透過 interval 增加時間

SELECT current_time + interval '6 hours' AS res

2. 透過 time 調整時間

SELECT localtime - time '02:00' AS res

參考資源:

PostgreSQL TIME Data Type

timestamp 型別:

postgresql 針對 timestmap 資料型別分為兩種，一個是有時區的而另外一個是沒有時區的型別，且 postgresql timestamp 已解決 mysql 2038 年問題。

timestamp: 沒有時區

timestampz: 有時區

範例：

CREATE TABLE timestamp_sample (
    ts   TIMESTAMP, 
    tsz TIMESTAMPTZ
);

path 線性向量相關型別:

待續

identity column:

類似 serial 的 auto increment 作法。

主要分為以下兩種形式

1. generated always as identity: 不能使用 insert、update 去操作 auto increment , 想要操作的話必須使用 overriding system value
2. generated by default as identity: 可以使用 insert, update 去操作 auto increment

語法如下:

創建一個 cars 資料表並且設定 id 為 auto increment

create table cars (
 id int generated always as identity,
 brand_name varchar not null
);

參考資源:

Identity Column in PostgreSQL

json vs jsonb:

postgresql 比 mysql 提供了更多樣的 json 操作函式，且可以對 jsonb 進行索引設定，不像 mysql 需要對 json 設定索引的話需要使用到虛擬欄位。

比較:

json: 儲存原始的 json 資料。

jsonb: 以二進制的方式儲存 json 資料，方便資料的處理以及 index。

針對 jsonb 設定索引:

jsonb 索引的演算法可以設定成 b-tree 或者是 GIN (Generalize Inverted Index)

針對 orders 的 info 欄位設定索引且演算法使用 GIN。

create index idx_info ON orders using GIN (info);

json 相關的 operator:

• -> : 取得 json 特定的 key
• ->>: 取得 json 特定的 key 並且回傳 text
• ?: 檢查 json 是否存在指定的 key
• @>: 檢查 json 是否擁有指定的內容

json 相關內建函示:

• json_each: 將 json 資料的轉換成 key-value pair
• json_typeof: 顯示 json 特定 key 的資料型別
• json_object_keys: 取得 json 物件的 key

參考資源：

PostgreSQL - JSON Data Type - GeeksforGeeks

PostgreSQL JSON Data Type

window function:

類似 aggregate function，但是回傳的資料筆數不會像 aggregate function 那樣受到影響。

參考資源:

SQL語法-window function - iT 邦幫忙::一起幫忙解決難題，拯救 IT 人的一天

Generated Columns:

目前 postgresql 只支援 stored 尚未支援 virtual。

注意事項:

1. Generated express 不能參考另外的 generated column

2. Partition key 不能用作於 generated column

3. Generate column 不能參考 system column

語法如下:

GENERATED ALWAYS AS (expression) Stored

參考資源:

PostgreSQL Generated Always: Generate Computed Columns

With Query (CTE):

創建一個暫時的結果集，可以有效的將複雜的語法切割成細小的語法使其容易閱讀

語法如下:

WITH <CTE_name>(<column_list>) AS  (
 <CTE_query>
)
<sql_statement>;

優點:

1. 簡化語法使其更容易閱讀, 因為可以將複雜的 SQL 切割成細小的 SQL 語法
2. 可重複利用性

參考資源:

PostgreSQL: WITH Queries (Common Table Expressions)

FulText Search:

full text search 有 trigram 、ts_vector 等方式

triple-ngram 分詞:

啟用 extension

CREATE EXTENSION pg_trgm;

查看 trigrams 如何切詞

select show_trgm('Gary') as trigrams;

計算文字相似度(similarity)

例如:

計算文字 A 跟 文字 B 之間的相似度

SELECT similarity('Gary', 'Gary xingobar');

要使用 trgm 的 operator 前須先針對欄位設定 GIN index

CREATE INDEX <index name> ON <table> USING GIN (<column> gin_trgm_ops);

語法類似如下:

SELECT * FROM cars
WHERE username % 'Leonardo';

以上語法會針對 username 跟 Leonardo 的文字作 similarity 且計算出的分數需要大於預設值(0.3) (pg_trgm.similarity_threshold)

還有很多其他的 operator 可以使用，可以參考官網的教學做使用

備註:

有些情境下想要使用模糊搜尋但是又要使用 index 的話可以使用 trgm 搭配 gin index，因為 gin、gist index 加快了 like 的速度以及字串的比對。

以下情境為 1000萬筆資料，然後我使用 like 前後模糊所花費的時間

explain analyze select * from cars
where username like '%有%';

發現 1000 萬筆資料只花費了快 4 秒的時間而已，比起用 mysql 快上了 N 倍

但是假如使用 regular expression 的話則花費變為 7–8秒

參考資源:

PostgreSQL Extensions: A Guide to pg_trgm | Timescale

Full-text search engine with PostgreSQL | Fibertide

F.33. pg_trgm - support for similarity of text using trigram matching

Muhammad Azeez - String similarity search and fast LIKE operator using pg_trgm

gin vs gist index:

GIN: 比較常使用於 fulltext search、jsonb、array 等情境

GIST: 比較常使用於地理位置、範圍等查詢情境

tsvector & tsquery

tsvector: 會產生 key-value pair, key 為語意的詞而 value 則為 position

例如:

select to_tsvector('The decline in stock market values has shaken investor confidence, as they expected the market to fall further.');

則會產生如下結果

tsquery: 查詢使用通常會搭配 tsvector 使用，且可以結合 & (AND)、| (OR)、 ! (NOT) 等 operator 使用

例如:

檢查 tsvector 的文字裡面是否有 confidence 以及 hello 的文字

select to_tsvector('The decline in stock market values has shaken investor confidence, as they expected the market to fall further.') @@ to_tsquery('confidence & hello');

結果:

雖然有 confidence 的字，但是沒有 hello 所以回傳 false

在使用 tsvector 跟 tsquery 的時候官方建議建立 index 的方式可以使用 stored 的方式建立。

使用上面所教的 generated column

GENERAETD ALWAYS AS (to_tsvector(....)) STORED;

參考資源:

SQL and PostgreSQL for Advanced Full-Text Search

Comprehend TSVECTOR and TSQUERY in Postgres for Full-Text Search