postgresql-pgadmin

postgresql db database pgadmin

zyx 2022-05-24 15:56:57 ‧ 3333 瀏覽

分享至

今天要介紹PostgreSQL(Relational database)
安裝postgresql請點我

PostgreSQL:哈囉，我叫 post-gress-Q-L
PostgreSQL的怎麼唸?

個人推薦GUI:pgadmin
安裝pgadmin請點我

建立資料庫

先建立資料庫，需要一個name(不要亂取，最好是跟專案有關)，因為會是未來後端連資料庫時需要的dbname

建立資料表

先建立資料表，需要一個name(不要亂取，最好是跟這個功能有關)，因為會是未來後端連資料庫時需要的

建立一個account的表欄位包含id,name,gender和age，此外，id為Primary key

CREATE TABLE public.account
(
    id uuid,
    name character varying(100),
    gender character(1),
    age integer,
    PRIMARY KEY (id)
);

ALTER TABLE public.account
    OWNER to postgres;

可以在Constraints底下找到我們定義的Primary key

CRUD小工具

要職人手作一個欄位多的新增或是更新sql command真的蠻要命的，還好GUI可以幫我們產生

建立foreign key

建立foreign key之前需要另一個表的某個值做為account的某個欄位做對應，所以我們需要:
step.1:建立一張company的DataTable
step.2:新增了一些資料進去
step.3:回到account建立一個新的欄位對應company的某個欄位(兩個表的欄位型別要一致，這邊是用uuid)
step.4:建立foreign key

step.5:幫這個foreign key命名，一般都會用fk_ThisTableName_RelationalTableName，這個範例來說就是fk_account_company。Local column是指account要對應到的company的欄位,referenes就是要對應到的dataTable,Referencing就是company要給account參考的欄位

step.6:確認好沒問題後,就可以按加號的icon並且save，就會看到新增成功了

step.7:驗證我們的foreign key真的有效，在company_id隨便使用一組uuid在新增時被擋下來了

step.8:驗證我們的foreign key真的有效，在company_id使用campany的id成功新增

step.9:讓postgresql幫我們驗證foreign key

Gorm在做什麼？

sequence

摘要：方便產生Primary key

除了uuid之外也可以用PostgreSQL的sequence，欄位id的Data type使用serial,排序的初始設定為1,2,3......

未來再新增資料時請勿用在給id，它會自己幫我們新增，如果自己硬要給數值的話，未來可能會造成當次id撞在一起的錯誤，當次錯誤之後，sequence還是會累加，所以下一次新增的id如果沒有被別人先佔用掉就會成功了

Indexs

摘要：增加查詢效率(減少時間)

要注意的是，在建立index時，會有一個exclusive lock資料表的行為，可能無法查詢的形況發生，此外有一定的資料量的話也需要執行時間，盡量在還沒建立資料前，先規劃好index
使用的好可以增加查詢效率，一般都會用where裡面的條件作為index的欄位

很常使用的方式是使用該表的Primary key，命名習慣為idx_tablePrimaryKeyColumnName

建立成功 :)

Trigger Functions & trigger

摘要：可以寫一些在觸發insert，update，delete或truncate時，必定會觸發的function，可以用來限制資料的寫入或是確認格式等，操作得好也可以達到更新A表時，同步更新B表某些資料的效果

建立建立一個trigger function

在code裡面寫邏輯，這邊用年齡要大於18歲作為範例，裡面的old表示舊的值(理論上在insert不用到)，new表示新進來的值

新增成功

找到需要這個限制的DataTable，這邊就前面做過的account

在definition底下選擇我們剛剛建立好的functio(必填)

在events底下選擇insert，表示我們只在新增資料時檢查這個人有沒有18歲了(必填)

雖然語法成功執行了，但是新增的筆數為0筆

view & Materialized View

View摘要：可以寫一些落落長(台)的sql command存起來放，未來直接查詢這個view就看以得到結果，實際上並沒有建立這張表(virtual structure)，每次執行理論上還是執行相同的指令(理論上是相同的執行時間)

Materialized View摘要：跟view很類似，與其不同處為，MView是真的建出一張表進行查詢，除此之外還有一個特色是透過Refresh Data的方式更新資料內容，否則每次查詢則會是上次Refresh Data時的結果

先從view開始示範，試著建立一個View，

名稱可以以view_開頭後面描述這個view要查的東西，主要是要在code裡面寫sql command，這邊做了一個查詢的範例

SELECT account.name,
  account.gender,
  account.company_id,
  company.id,
  company.name AS company_name
  FROM account
    JOIN company ON account.company_id = company.id
WHERE account.age > 10 AND account.gender = 'M';

建立成功之後，直接對這個view查詢就可以得到結果了，未來在程式裡面要查這筆資料時，就不用寫一狗票的select了

接著介紹MView，從建立一個MVIew開始，名稱可以以mview_開頭後面描述這個mview要查的東西，這邊的查詢語法使用和上面的view相同的內容

接著直接對它查詢一定會噴錯，因為我們還沒做Refresh Data

在可以用With data讓他開始執行語法(如果量大需要一定的等待時間)，如果不爽也可以用With no data把過去跑完的資料清掉(只會清掉這個MView的內容，並不會影響到原本的資料表內容)

有看到右下角的這個小視窗出現並且表示成功了

再查一次就可以看到結果了

補充說明：Refresh Data的同時仍然可以查詢該MView，只是會拿到上次Refresh後的結果而已

新增Function

如果你使用gin,fiber裡面有用到gorm(uuid)可以參考以下方法(理論上後端的go server 會在AutoMigrate時自動檢查並執行)
執行以下語法即可新增 uuid_generate_v4

CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";

新完成就會在Functions右鍵Refresh後就會看到

資料庫的匯出&匯入

匯出指令:pg_dump -U postgres -d report_test -f postgresql_report.sql
常用參數：
-a:僅匯出資料，不匯出結構
-s:僅匯出結構，不匯出資料
完整參數表
匯入指令:psql -d import_test -f report_test.sql (匯入前記得先建立一個空個資料庫，這邊是用import_test作為範例)

有興趣可以匯入玩玩看