postgresql-pgadmin

今天要介绍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作为范例)

有兴趣可以汇入玩玩看