# PostgreSQL
###### tags: `db`
## download
* [runtime](https://postgresapp.com/downloads.html)
* [GUI](https://eggerapps.at/postico/v1.php)
learn
https://docs.postgresql.tw/the-sql-language/sql-syntax/4.3.-han-shu-hu-jiao
https://www.prisma.io/blog/backend-prisma-typescript-orm-with-postgresql-data-modeling-tsjs1ps7kip1
## add PATH to zshrc
添加PostgreSQL環境變數就可以指定psql cli默認執行的lib位置
```javascript=
~/.zshrc
# psql
export PATH=$PATH:/Applications/Postgres.app/Contents/Versions/15/bin
```
以上等同
`> /Applications/Postgres.app/Contents/Versions/15/bin/psql -p5432 "danny"`
## DB command
使用psql進入db，在pq中所有指令開頭都是反斜線開頭 `\help`則可以查看當前可用的指令（跟你設定權限有關）。
`\?`則是指令查詢工具
`\l`列出當前可用的db
### 創建db
使用pq的sql指令創建，注意在pq中所有的SQL指令結尾都一定要加;不然指令不會成功
在查看pq admin這樣我們就成功創建test db了
### 切換DB
### data type
* char 資料有固定長度，並且都為英文數字。
* nchar 資料有固定長度，但不確定是否皆為英文數字。
* varchar 資料沒有固定長度，並且都為英文數字。
* nvarchar 資料沒有固定長度，且不確定是否皆為英文數字。
### 創建tabel
在創建table時psql很貼心會偵測你（）的位置來判斷增新的結尾，）代表整個table的schema，;則是執行的符號
### 查看當前db的table的schema
### 查看schema info
### Insert Into Database
### 查看table
### 生成假資料
[mockdata](https://www.mockaroo.com/)
### 從SQL黨載入
```javascript
//查看person 所有欄位
danny=# SELECT * FROM PERSON
//查看person中欄位是first_name、last_name的資料
danny=# SELECT first_name,last_name FROM PERSON
//將資料已country_of_birth排序 [asc|desc]
danny=# SELECT first_name,last_name FROM PERSON ORDER BY country_of_birth ASC
//將資料先已id排再以email排序
danny=# select * from person order by id,email;
//distinct filter重複資料
danny=#
select distinct country_of_birth from person
order by country_of_birth;
//combine and in where
danny=#
select * from person
where gender = 'Female'
AND ( country_of_birth='Japan' OR country_of_birth='China' );
danny=#
select * from person
where gender = 'Female'
AND ( country_of_birth in ('Japan','China') )
order by country_of_birth;
```
### condition select
### like是相似的operator語法，返回fileds中有符合like的敘述，%代表萬用字符
```javascript
danny=#
select * from person
where email like '%@google.com.%';
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
-----+------------+-----------+--------------------------+--------+---------------+------------------
67 | Der | Al Hirsi | dalhirsi1u@google.com.hk | Male | 2022-10-03 | Ghana
201 | Maris | Lowey | mlowey5k@google.com.hk | Female | 2022-05-23 | United States
523 | Bobbye | Aiken | baikenei@google.com.br | Female | 2022-07-26 | China
635 | Baillie | Sowle | bsowlehm@google.com.hk | Male | 2022-12-31 | Thailand
697 | Crissie | Barcke | cbarckejc@google.com.br | Female | 2022-09-23 | Thailand
749 | Norman | Skyme | nskymeks@google.com.au | Male | 2022-06-17 | Sweden
(6 rows)
//________@% 代表找尋7個字元後加@的email
danny=#
select * from person
where email like '________@%'
limit 5;
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
----+------------+-----------+-------------------------+--------+---------------+------------------
9 | Freddy | Savege | fsavege8@pinterest.com | Male | 2022-11-16 | Brazil
10 | Alex | Grover | agrover9@wisc.edu | Male | 2023-01-12 | Indonesia
18 | Oralla | Harley | oharleyh@reddit.com | Female | 2022-03-30 | China
32 | Sarette | Walder | swalderv@bravesites.com | Female | 2022-02-18 | Netherlands
33 | Lynne | Darwin | ldarwinw@scribd.com | Female | 2022-10-28 | Cuba
(5 rows)
```
### group by
```javascript
danny=#
select country_of_birth,count(*) from person
group by country_of_birth
order by country_of_birth;
```
### having 接在group by 後面
[aggregate doc](https://www.postgresql.org/docs/9.5/functions-aggregate.html)
```javascript
danny=#
select country_of_birth,count(*) from person
group by country_of_birth
having count(*)>40
order by country_of_birth;
country_of_birth | count
------------------+-------
China | 195
Indonesia | 120
Philippines | 43
Russia | 50
(4 rows)
```
### aggregate
```javascript
//get avage data
danny=# select MAX(price) from car;
max
-----------
$99900.20
(1 row)
danny=#
select make,model,min(price) from car
group by make ,model;
```
### Operators
在sql中可以透過Operator去計算filed新值
```javascript
danny=#
select id , make ,model,Round( price *.10,2),Round( price-(price *.10),2),price from car;
```
### Alias
甚至你可以幫你的tabel命名
```javascript
danny=#
select id , make ,model,Round( price *.10,2) as ten_percent,Round( price-(price *.10),2) as discount_after_10_percent,price as origin_price from car;
```
### coalesce
當我們下指令列出user所有email時你會發現有些人沒有email
```javascript
danny=# slect email from person;
```
這時候就可以下coalesce
```javascript
danny=# select coalesce(email,'envaild email') from person;
```
**hint** :
coalesce(arg1,arg2,...) 返回第一個非null的值
### nullif
```javascript
NULLIF ( expression , expression )
```
NULLIF如果兩個express相等就回傳null，如果不相等就回傳第一個值。
```javascript
danny=# select nullif(100,100);
nullif
--------
(1 row)
danny=# select nullif(100,19);
nullif
--------
100
(1 row)
//demo1
danny=# select coalesce(10/nullif(0,0),0);
coalesce
----------
0
(1 row)
//demo2
danny=# select coalesce(null,0);
coalesce
----------
0
(1 row)
//結合coalesce demo1跟demo2的做法是一樣的。
```
### time format
[備註](https://docs.postgresql.tw/the-sql-language/functions-and-operators/date-time-functions-and-operators)
```javascript
danny=# select now();
now
-------------------------------
2023-02-25 20:04:00.925601+08
(1 row)
danny=# select now()::date;
now
------------
2023-02-25
(1 row)
danny=# select now()::time;
now
-----------------
20:04:19.478294
(1 row)
//在psql中日期可以累加透過interval(間隔)運算符推做日期加總
danny=# select date '2001-09-28' + interval '1 hours';
?column?
---------------------
2001-09-28 01:00:00
(1 row)
```
### 時間運算
```javascript
// etc 找出去年的時間
danny=# select now() - interval '1 yaers';
?column?
---------------------
2022-02-25 20:15:12.742052+08
(1 row)
```
但你會發現output格式多了小時，這時可以透過formate decorator只計算日期
```javascript
danny=# select now()::date - interval '1 years';
?column?
---------------------
2022-02-25 00:00:00
(1 row)
```
或是可以在簡化成以下的output
```javascript
danny=# select (now() - interval '1 years')::date;
date
------------
2022-02-25
(1 row)
//extract
danny=# select extract(year from now());
extract
---------
2023
(1 row)
//甚至可以選取世紀
danny=# select extract(century from now());
extract
---------
21
(1 row)
```
### 計算歲數
```javescript
danny=#
select first_name ,last_name,date_of_birth,age(now()::date,date_of_birth) as age from person;
```
### 修改table 欄位（ALTER TABLE）
移除pkey,constraint則是用來約束欄位的宣告例如宣告pkey就要加constraint
```javascript=
danny=#
alter table person drop constraint person_pkey;
```
新增主鍵，注意如果要宣告的欄位如果資料重複會無法宣告主鍵
```javascript
danny=# select * from person where id=1;
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
----+------------+-----------+--------------------------+--------+---------------+------------------
1 | Rafa | Hallagan | rhallagan0@amazonaws.com | Female | 2022-12-20 | Japan
1 | Rafa | Hallagan | rhallagan0@amazonaws.com | Female | 2022-12-20 | Japan
(2 rows)
danny=# alter table person add primary key(id);
//如果你要射pk的欄位data有重複就不能設置主鍵
ERROR: could not create unique index "person_pkey"
DETAIL: Key (id)=(1) is duplicated.
//解決方法重新新增唯一性data
danny=# delete from person where id = 1
danny=# insert into person (id,first_name, last_name, email, gender, date_of_birth, country_of_birth) values (1,'Rafa', 'Hallagan', 'rhallagan0@amazonaws.com', 'Female', '12/20/2022', 'Japan');
```
### 查詢email重否重複
```javascript
danny=# select email ,count(*) from person
group by email;
```
圖中表示有304位person是沒有email的
### 將group by 結果做having
裡會發現304個null email，而duplicate email則是rhallagan0@amazonaws.com跟 rchaster1@wiley.com ，分別是3位跟2位
```javascript
danny=# select email,count(*) from person group by email having count(*)>1;
email | count
--------------------------+-------
| 304
rhallagan0@amazonaws.com | 3
rchaster1@wiley.com | 2
(3 rows)
```
這時候將duplicate的email list出來，你會發現當我要發送rchaster1@wiley.com 的mail，你會不知道到底發給誰，這時候我們就要做unique fileds
```javascript
danny=# select * from person where email ='rchaster1@wiley.com';
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
------+------------+-----------+---------------------+--------+---------------+------------------
2 | Rich | Chaster | rchaster1@wiley.com | Male | 2022-08-25 | Germany
1003 | Danny | Wu | rchaster1@wiley.com | Male | 2022-11-06 | Germany
(2 rows)
```
先移除duplicate的data
```javascript=
danny=# select * from person where email ='rchaster1@wiley.com';
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
------+------------+-----------+---------------------+--------+---------------+------------------
2 | Rich | Chaster | rchaster1@wiley.com | Male | 2022-08-25 | Germany
1003 | Rich | Chaster | rchaster1@wiley.com | Male | 2022-08-25 | Germany
(2 rows)
danny=# delete from person where id = 1003;
danny=# select * from person where email ='rhallagan0@amazonaws.com';
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
------+------------+-----------+--------------------------+--------+---------------+------------------
1001 | Rafa | Hallagan | rhallagan0@amazonaws.com | Female | 2022-12-20 | Japan
1002 | Rafa | Hallagan | rhallagan0@amazonaws.com | Female | 2022-12-20 | Japan
1 | Rafa | Hallagan | rhallagan0@amazonaws.com | Female | 2022-12-20 | Japan
(3 rows)
danny=# delete from person where id in ('1001','1002');
```
當使用constraint後的field，如果在insert duplicate的data就會出現警告，
constraint 用於約束table field用法會是[method] constraint [constraint_key] [限制條件]
```
danny=# alter table person add constraint unique_email_address UNIQUE(email);
danny=# insert into person (first_name, last_name, email, gender, date_of_birth, country_of_birth) values ('Rich', 'Chaster', 'rchaster1@wiley.com', 'Male', '8/25/2022', 'Germany');
ERROR: duplicate key value violates unique constraint "unique_email_address"
DETAIL: Key (email)=(rchaster1@wiley.com) already exists
```
移除constraint [key]
```javascript
danny=# alter table person drop constraint "unique_email_address";
ALTER TABLE
```
distinct常用於select field中資料的type
```javascript
danny=# select distinct gender from person;
gender
-------------
Genderqueer
Bigender
hello
Genderfluid
Male
Non-binary
Polygender
Female
Agender
(9 rows)
```
### 刪除不要的option資料
```javascript
danny=# select distinct gender from person;
gender
-------------
Male
Non-binary
Polygender
Female
hello
Agender
Genderfluid
(7 rows)
danny=# delete from person
danny-# where gender in ('Non-binary','Polygender','hello','Agender','Genderfluid');
```
之後新增constraint到table field這樣之後insert data時，gender必須是'Male','Female'否則會報錯
```javascript
danny=# alter table person add constraint gender_constraint check (gender in ('Male','Female'));
```
### delete table
```javascript
danny=# delete from person where id = '501';
DELETE 1
```
### update table
```javascript
danny=# UPDATE person SET email='hiunji64@gmail.com' WHERE id =13;
UPDATE 1
danny=# UPDATE person SET first_name='Danny' ,last_name='Wu' WHERE id =14;
```
### 檢查constraint欄位，如果value duplicate就不操作db
```javascript
danny=# insert into person (first_name, last_name, email, gender, date_of_birth, country_of_birth) values ('Rich', 'Chaster', 'hello@wiley.com', 'Male', '8/25/2022', 'Germany') ON CONFLICT(email) DO NOTHING;
INSERT 0 0
```
ON CONFLICT的參數只能是有constraint過的欄位如果沒有會報錯，例如ON CONFLICT(first_name) ，DO NOTHING代表一個action,如果inset的data沒有符合constraint的要求會就不做db操作
### conflict update
```javascript
danny=# select * from person where id=1006;
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
------+------------+-----------+--------------------+--------+---------------+------------------
1006 | Rich | Chaster | hello@wiley.com.uk | Male | 2022-08-25 | Germany
(1 row)
danny=# insert into person (first_name, last_name, email, gender, date_of_birth, country_of_birth) values ('Danny', 'Wu', 'Danny@wiley.com.uk', 'Male', '8/25/2022', 'Germany') ON CONFLICT(email) DO UPDATE SET email=EXCLUDED.email,first_name=EXCLUDED.first_name,last_name=EXCLUDED.last_name;
INSERT 0 1
danny=# select * from person where id=1006;
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
------+------------+-----------+--------------------+--------+---------------+------------------
1006 | Rich | Chaster | hello@wiley.com.uk | Male | 2022-08-25 | Germany
(1 row)
```
我們可以透過ON CONFLICT後重寫入db資料
### 主鍵外來鍵
```sql
create table person (
id bigserial NOT NULL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(50),
gender VARCHAR(50) NOT NULL,
date_of_birth DATE NOT NULL,
car_id BIGINT ,
country_of_birth VARCHAR(50),
CONSTRAINT "person_car_fkey" FOREIGN KEY ("car_id") REFERENCES "car" ("id") ON DELETE CASCADE ON UPDATE CASCADE
-- CONSTRAINT car_id BIGINT REFERENCES car (id),
-- UNIQUE(car_id)
);
```
將id為1的person設定car編號
```javascript
danny=# update person set car_id=2 where id=1
```
如果今天foreign key不存在sql會給你提示
```javascript
danny=# update person set car_id = 10000 where id = 10;
ERROR: insert or update on table "person" violates foreign key constraint "person_car_fkey"
DETAIL: Key (car_id)=(10000) is not present in table "car".
```
### 更改result select format
\x是讓你決定你的Expanded display ，他是一個toggle 指令，呼叫一次開啟在呼叫一次關閉
```javascript
danny=# \x
Expanded display is on.
danny=# select * from person;
-[ RECORD 1 ]----+-----------------------------------
id | 2
first_name | Win
last_name | Twede
email | wtwede2@so-net.ne.jp
gender | Male
date_of_birth | 2022-10-06
car_id |
country_of_birth | Colombia
-[ RECORD 2 ]----+-----------------------------------
id | 3
first_name | Obie
last_name | Fitter
email |
gender | Male
date_of_birth | 2023-02-02
car_id |
country_of_birth | Philippines
danny=# \x
Expanded display is off.
```
### inner join
將兩個table條件符合的結果顯示出來
inner join是table的fk 與reference的pk的聯合查詢，指令 join [fk_table] on [pk_table][field_id]=[fk_table][field_id] ;
```javascript
danny=# select * from person
danny-# join car on person.car_id = car.id;
-[ RECORD 1 ]----+-------------------------
id | 10
first_name | Shepard
last_name | Cana
email | scanaa@cloudflare.com
gender | Male
date_of_birth | 2022-07-23
car_id | 1000
country_of_birth | Ethiopia
id | 1000
make | Chevrolet
model | Express 1500
price | 52723.00
-[ RECORD 2 ]----+-------------------------
id | 1
first_name | Rafa
last_name | Hallagan
email | rhallagan0@amazonaws.com
gender | Female
date_of_birth | 2022-12-20
car_id | 1
country_of_birth | Japan
id | 1
make | MINI
model | Cooper Clubman
price | 30005.00
```
你會發現output顯示太多欄位，就可以在select時指定要顯示的內容如下：
```javascript
danny=# select person.first_name,person.email,car.make from person
join car on person.car_id=car.id;
-[ RECORD 1 ]------------------------
first_name | Shepard
email | scanaa@cloudflare.com
make | Chevrolet
-[ RECORD 2 ]------------------------
first_name | Rafa
email | rhallagan0@amazonaws.com
make | MINI
```
### left join
將table1的結果與符合table2的合集顯示出來
這裡就列出person有car跟person沒有car的結果
```javascript
danny=# select * from person left join car on person.car_id = car.id;
-[ RECORD 1 ]----+-----------------------------------
id | 2
first_name | Win
last_name | Twede
email | wtwede2@so-net.ne.jp
gender | Male
date_of_birth | 2022-10-06
car_id |
country_of_birth | Colombia
id |
make |
model |
price |
-[ RECORD 2 ]----+-----------------------------------
id | 3
first_name | Obie
last_name | Fitter
email |
gender | Male
date_of_birth | 2023-02-02
car_id |
country_of_birth | Philippines
id |
make |
model |
price |
```
但如果我們只想顯示person沒有car的資料可以使用left outer join，結果如下
```javascript
danny=# select * from person
danny-# left join car on person.car_id=car.id
danny-# where car.* is null;
-[ RECORD 1 ]----+-----------------------------------
id | 2
first_name | Win
last_name | Twede
email | wtwede2@so-net.ne.jp
gender | Male
date_of_birth | 2022-10-06
car_id |
country_of_birth | Colombia
id |
make |
model |
price |
-[ RECORD 2 ]----+-----------------------------------
id | 3
first_name | Obie
last_name | Fitter
email |
gender | Male
date_of_birth | 2023-02-02
car_id |
country_of_birth | Philippines
id |
make |
model |
price |
```
如果只用join不用left join就會是(0 rows)
```javascript
danny=# select * from person
join car on person.car_id = car.id
where car.* is null;
(0 rows)
```
### delete from foregin keys
當我們要刪除fk的資料時，sql會跟你說pk fk的關係所以不能刪除
```javascript
danny=# delete from car where id = 1100;
ERROR: update or delete on table "car" violates foreign key constraint "person_car_fkey" on table "person"
DETAIL: Key (id)=(1100) is still referenced from table "person".
```
這是因為我們在宣告person的constraint時沒有同時宣告on update跟on delete時要做什麼action
```javascript
danny=# \d person
Table "public.person"
Column | Type | Collation | Nullable | Default
------------------+-----------------------+-----------+----------+------------------------------------
id | bigint | | not null | nextval('person_id_seq'::regclass)
first_name | character varying(50) | | not null |
last_name | character varying(50) | | not null |
email | character varying(50) | | |
gender | character varying(50) | | not null |
date_of_birth | date | | not null |
car_id | bigint | | |
country_of_birth | character varying(50) | | |
Indexes:
"person_pkey" PRIMARY KEY, btree (id)
"unique_email_address" UNIQUE CONSTRAINT, btree (email)
Check constraints:
"gender_constraint" CHECK (gender::text = ANY (ARRAY['Male'::character varying, 'Female'::character varying]::text[]))
Foreign-key constraints:
"person_car_fkey" FOREIGN KEY (car_id) REFERENCES car(id)
```
### 修改constraint
constraint的更動因為沒有update方式，所以需要先drop後從新新增，如下圖：
```javascript
danny=# alter table person drop constraint person_car_fkey;
ALTER TABLE
danny=# alter table person add constraint person_car_fkey FOREIGN KEY(car_id) REFERENCES car(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE
```
在constraint中on update、on delete可以添加對應的action規則，告訴sql當你的pk或是fk資料發生異動時，需要做什麼事情。
**constraint 的 action 如下：**
* ```on update``` 和 ```on delete``` 后面可以跟的词语有四个
* ```no action``` 表示不做任何操作，
* ```set null``` 表示在外流件對應的字段為null
* ```set default``` 設置為默認值(restrict)
* ```cascade``` 聯集操作，如果主鍵的參考值更新，外來鍵的data也會更動，如果主鍵刪除，外來鍵的資料也會移除
之後查看修改結果，首先更改資料
```javascript
danny=# update person set car_id = 25 where id = 150;
UPDATE 1
danny=# select * from car where id = 25;
-[ RECORD 1 ]-----
id | 25
make | Mazda
model | Miata MX-5
price | 19424.00
danny=# select * from person where id = 150;
-[ RECORD 1 ]----+-----------
id | 150
first_name | Gabriel
last_name | Roseburgh
email |
gender | Female
date_of_birth | 2022-11-15
car_id | 25
country_of_birth | China
```
這時car資料一但移除person也同步刪除了
```javascript
danny=# delete from car where id = 25;
DELETE 1
danny=# select * from person where id = 150;
(0 rows)
danny=# select * from car where id = 25;
(0 rows)
```
修改後的constraint如下，person_car_fkey(FOREIGN KEY)設成ON UPDATE CASCADE ON DELETE CASCADE這樣主見跟外流間刪除時就會同步移除
```javascript
Table "public.person"
Column | Type | Collation | Nullable | Default
------------------+-----------------------+-----------+----------+------------------------------------
id | bigint | | not null | nextval('person_id_seq'::regclass)
first_name | character varying(50) | | not null |
last_name | character varying(50) | | not null |
email | character varying(50) | | |
gender | character varying(50) | | not null |
date_of_birth | date | | not null |
car_id | bigint | | |
country_of_birth | character varying(50) | | |
Indexes:
"person_pkey" PRIMARY KEY, btree (id)
"unique_email_address" UNIQUE CONSTRAINT, btree (email)
Check constraints:
"gender_constraint" CHECK (gender::text = ANY (ARRAY['Male'::character varying, 'Female'::character varying]::text[]))
Foreign-key constraints:
"person_car_fkey" FOREIGN KEY (car_id) REFERENCES car(id) ON UPDATE CASCADE ON DELETE CASCADE
```
所以在sql宣告table時，我們需要確認當主鍵鍵或外來鍵新增或刪除時需要做什麼action
```javascript
create table person (
id bigserial NOT NULL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(50),
gender VARCHAR(50) NOT NULL,
date_of_birth DATE NOT NULL,
car_id BIGINT ,
country_of_birth VARCHAR(50),
CONSTRAINT "person_car_fkey" FOREIGN KEY ("car_id") REFERENCES "car" ("id") ON DELETE CASCADE ON UPDATE CASCADE
-- CONSTRAINT car_id BIGINT REFERENCES car (id),
-- UNIQUE(car_id)
);
```
### Copy SQL To CSV
**hint**: copy ([SQL_COMMAND]) TO [YOUR_PATH] DELIMITER ',' CSV HEADER
;
```DELIMITER``` : 分隔
```CSV``` 檔案格式
```HEADER``` 保留HEADER
```javascript
danny=# copy (select * from person left join car on car.id = person.car_id) TO '/Users/danny/develpoment/learn/DB/sql/result.csv' DELIMITER ',' CSV HEADER
;
COPY 878
```
### sequence
在 `psql` 中可以透過sequence資料結構去定義table id，sequence的宣告來自於底下table
```javascript
create table person (
id bigserial NOT NULL PRIMARY KEY,
)
```
宣告id是bigserial ，就有sequence效果，sequence主要作用就是讓id在每次table新增資料時自動累加id count，例如底下資料的id就是sequence累加的結果
```javascript
-[ RECORD 1 ]----+-----------------------------------
id | 2
first_name | Win
last_name | Twede
email | wtwede2@so-net.ne.jp
gender | Male
date_of_birth | 2022-10-06
car_id |
country_of_birth | Colombia
-[ RECORD 2 ]----+-----------------------------------
id | 3
first_name | Obie
last_name | Fitter
email |
gender | Male
date_of_birth | 2023-02-02
car_id |
country_of_birth | Philippines
-[ RECORD 3 ]----+-----------------------------------
id | 4
first_name | Valina
last_name | Philot
email |
gender | Female
date_of_birth | 2022-04-28
car_id |
country_of_birth | France
```
另外宣告有sequence的table會多一個sequence_id，我們可以透過\d person_id_seq，去查看person_id_seq內容
```javascript
danny=# \d
List of relations
Schema | Name | Type | Owner
--------+---------------+----------+-------
public | car | table | danny
public | car_id_seq | sequence | danny
public | person | table | danny
public | person_id_seq | sequence | danny
(4 rows)
```
底下的output會可以看到該sequence的起始只與最大值跟每次id會累加多少
```javascript
danny=# \d person_id_seq
Sequence "public.person_id_seq"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1
Owned by: public.person.id
```
以及透過select查看Sequence的會後一個數是多少，下一次insert data後last_value就會變成1000
```javascript
danny=# select * from person_id_seq;
last_value | log_cnt | is_called
------------+---------+-----------
999 | 24 | t
(1 row)
```
實際上id之所以可以一次一次累積是因為呼叫了 nextval('person_id_seq'::regclass)這個function在底下的id 欄位中可以看到
```
danny=# \d person;
Table "public.person"
Column | Type | Collation | Nullable | Default
------------------+-----------------------+-----------+----------+------------------------------------
id | bigint | | not null | nextval('person_id_seq'::regclass)
first_name | character varying(50) | | not null |
last_name | character varying(50) | | not null |
email | character varying(50) | | |
gender | character varying(50) | | not null |
date_of_birth | date | | not null |
car_id | bigint | | |
country_of_birth | character varying(50) | | |
Indexes:
"person_pkey" PRIMARY KEY, btree (id)
"unique_email_address" UNIQUE CONSTRAINT, btree (email)
Check constraints:
"gender_constraint" CHECK (gender::text = ANY (ARRAY['Male'::character varying, 'Female'::character varying]::text[]))
Foreign-key constraints:
"person_car_fkey" FOREIGN KEY (car_id) REFERENCES car(id)
```
很有趣的事每當使用nextval('person_id_seq'::regclass)，nextval就會加１，他會影響到之後insert 的data會是重哪一個數開始計算，等之後我新增一筆資料時他的id就會是1003
```javascript
danny=# select * from nextval('person_id_seq'::regclass) ;
nextval
---------
1001
(1 row)
danny=# select * from nextval('person_id_seq'::regclass);
nextval
---------
1002
(1 row)
```
```javascript
danny=# insert into person (first_name, last_name, email, gender, date_of_birth, country_of_birth) values ('Rich', 'Chaster', 'hellos@wiley.com', 'Female', '8/25/2022', 'Germany');
INSERT 0 1
danny=# select * from person where first_name='Rich';
id | first_name | last_name | email | gender | date_of_birth | car_id | country_of_birth
------+------------+-----------+------------------+--------+---------------+--------+------------------
1003 | Rich | Chaster | hellos@wiley.com | Female | 2022-08-25 | | Germany
(1 row)
```
所以每當我們呼叫nextval('person_id_seq'::regclass)或是insert ，sequence都會累積，但如果們想改變sequence位置的話可以使用以下指令
```javascript
danny=# alter sequence person_id_seq restart with 10;
ALTER SEQUENCE
```
這時我們的位置就重10開始了
```javascript
danny=# select * from person_id_seq;
last_value | log_cnt | is_called
------------+---------+-----------
10 | 0 | f
(1 row)
```
### extensions
```typescript
danny=# select * from pg_available_extensions;
```
```
name | default_version | installed_version | comment
------------------------------+-----------------+-------------------+---------------------------------------------------------------------------------------------------------------------
refint | 1.0 | | functions for implementing referential integrity (obsolete)
postgis | 3.3.2 | | PostGIS geometry and geography spatial types and functions
unaccent | 1.1 | | text search dictionary that removes accents
btree_gin | 1.3 | | support for indexing common datatypes in GIN
plpython3u | 1.0 | | PL/Python3U untrusted procedural language
ltree | 1.2 | | data type for hierarchical tree-like structures
tsm_system_rows | 1.0 | | TABLESAMPLE method which accepts number of rows as a limit
adminpack | 2.1 | | administrative functions for PostgreSQL
dict_xsyn | 1.0 | | text search dictionary template for extended synonym processing
address_standardizer | 3.3.2 | | Used to parse an address into constituent elements. Generally u
```
### like
like 可以匹配欄位相同或相似結果，如果要匹配字首用'A%'，只要是字母開頭是A的結果都會計算進去，反之搭配字尾就是'%D'
```typescript
danny=# select * from person where first_name like 'D%'
;
```
### Full-text search
全文搜索的優點如下：
1. 可以進行更精確的搜尋：全文搜索可以針對文本內容進行搜尋，而不僅僅是針對關鍵字進行搜尋。這意味著可以找到包含相似內容的文本，即使這些文本不包含完全相同的關鍵字。
2. 可以提高搜尋速度：全文搜索通常使用特殊的索引技術，例如倒排索引、字典樹、N-gram 等，可以大大提高搜尋速度和精確度。
3. 可以提高搜索的效率：全文搜索可以針對大量的文本進行快速和高效的搜尋，這對於需要對文本進行搜尋的應用非常有用，例如搜尋引擎、社交媒體等。
4. 可以提高搜尋的可定制性：全文搜索通常提供了豐富的搜索選項和定制化選項，可以根據不同的應用需求進行定制。
全文搜索的缺點如下：
1. 資源消耗較高：全文搜索需要建立特殊的索引和分析文本內容，這需要消耗較多的資源和計算能力，特別是對於大量文本的搜尋。
2. 結果不一定精確：全文搜索通常是基於文本內容進行搜尋的，因此結果不一定是完全精確的，有可能包含一些誤差或者相似的結果。
3. 可能需要進行多次搜尋：由於全文搜索的結果不一定是完全精確的，有時候可能需要進行多次搜尋來獲取更精確的結果，這可能會增加搜尋的時間和資源消耗。
總之，全文搜索具有提高搜索精度、效率和可定制性等優點，但也存在資源消耗高、結果不一定精確和需要進行多次搜尋等缺點。
### RETURNING
RETURNING 用來減少 sql 不必要得查詢，可以用來看 insert 、 update 、 delete 更改的內容如下：
#### create
```typescript
danny=# INSERT INTO users (firstname,lastname) VALUES ('Danny','Wu');
INSERT 0 1
danny=# INSERT INTO users (firstname,lastname) VALUES ('Danny','Wu') RETURNING * ;
firstname | lastname | id
-----------+----------+----
Danny | Wu | 4
(1 row)
INSERT 0 1
danny=# INSERT INTO users (firstname,lastname) VALUES ('Danny','Wu') RETURNING firstname ;
firstname
-----------
Danny
(1 row)
INSERT 0 1
```
#### update
```typescript
danny=# update users set firstname = 'dannys' where firstname='dannys' RETURNING *;
firstname | lastname | id
-----------+----------+----
dannys | wu | 1
dannys | wu | 2
(2 rows)
UPDATE 2
danny=# update users set firstname = 'dannys' where firstname='dannys' RETURNING firstname;
firstname
-----------
dannys
dannys
(2 rows)
UPDATE 2
```
#### delete
```typescript
danny=# delete from users where firstname='dannyss';
DELETE 0
danny=# delete from users where firstname='dannyss' RETURNING * ;
firstname | lastname | id
-----------+----------+----
(0 rows)
DELETE 0
danny=# delete from users where firstname='dannys' RETURNING * ;
firstname | lastname | id
-----------+----------+----
dannys | wu | 1
dannys | wu | 2
(2 rows)
DELETE 2
```
### JSON
postgreSQL 有提供 json format ，有別於已關連式資料庫需要過 json 去提取而外訊息外， json 提供更單性的處裏，由於 JSON 的 type 是 string，如果你單單只做訊息記錄不需要跟其他 table 做關聯的話，或許 json data format 很適合你，同時 postgresql 提供很多 function 用來處理 json 資料。
#### 宣告有 json 格式的 tabel
```typescript
danny=#
CREATE TABLE orders_json(
id serial NOT NULL PRIMARY KEY,
info json NOT NULL)
;
```
#### insert
```typescript
danny=# INSERT INTO orders_json (info)
danny-# VALUES('{ "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}'),
('{ "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}'),
('{ "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}');
INSERT 0 3
```
#### query
如果想要查詢 json 資料可以用以往 select 方式查詢，或是用 -> 跟 -->返回對應的 json 欄位
##### 查詢全部 json 資酪
```typescript
danny=# SELECT info from orders_json;
info
-------------------------------------------------------------------------
{ "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}
{ "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}
{ "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}
(3 rows)
```
`備註：` info 的格式是 `string`
##### 回傳josn中特定欄位（ text ）
```typescript
danny=# SELECT info ->> 'customer' as customers from orders_json;
customers
--------------
Lily Bush
Josh William
Mary Clark
(3 rows)
```
##### 回傳josn中特定欄位 (key)
```typescript
danny=# SELECT info -> 'customer' as customers from orders_json;
customers
----------------
"Lily Bush"
"Josh William"
"Mary Clark"
(3 rows)
```
* -> 回傳的 key 用來取得深層的 json 資料
* ->> 回傳的是 string 結果
##### json key select
假設我們要取得 items 中的 product 資料用 --> 是不行的，因為對 postgresql 來說如果要做 next query 的話必須要用 key 的形式才能往下做查詢。
```typescript
info
-------------------------------------------------------------------------
{ "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}
{ "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}
{ "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}
(3 rows)
```
這樣就能查詢 product 資料了
```typescript
danny=# SELECT info -> 'items'->> 'product' as product from orders_json;
product
-----------
Diaper
Toy Car
Toy Train
(3 rows)
```
#### json operator in WHERE
一樣的道理我們可以用 -> ->> 去查詢你的json 結果，例如找出 info 欄位中 有購買 diaper 得顧客名稱
```typescript
danny=# SELECT info ->> 'customer' as customer from orders_json WHERE info -> 'items' ->> 'product' = 'Diaper';
customer
-----------
Lily Bush
(1 row)
```
#### aggregate in json
你也可以用 aggregate 去計算想要的結果
```typescript
danny=# SELECT
MIN (CAST (info -> 'items' ->> 'qty' AS INTEGER)),
MAX (CAST (info -> 'items' ->> 'qty' AS INTEGER)),
SUM (CAST (info -> 'items' ->> 'qty' AS INTEGER)),
AVG (CAST (info -> 'items' ->> 'qty' AS INTEGER))
FROM orders_json;
min | max | sum | avg
-----+-----+-----+--------------------
1 | 24 | 27 | 9.0000000000000000
(1 row)
```
`CAST` 用來轉換結果的型別，因為 josn 原本就是 text 的資料，如果你希望 query result 是 整數的話就可以用 CAST 幫忙轉換用法就是 **CAST(field AS YOUR_TYPE )**
```typescript
danny=# SELECT CAST('100' as INTEGER);
int4
------
100
(1 row)
```
這樣 100 就是 number 結果了
#### JSON functions
postgresql 中也有提供很多 function 來處理 json 資料，例如查詢你 json 中有哪些 key 這些key 對應到的 type是什麼等等
[更多內容](https://www.postgresql.org/docs/9.5/functions-json.html)
```typescript!
// 遞迴每個 key
danny=# SELECT json_each(info) from orders_json;
json_each
---------------------------------------------------
(customer,"""Lily Bush""")
(items,"{""product"": ""Diaper"",""qty"": 24}")
(customer,"""Josh William""")
(items,"{""product"": ""Toy Car"",""qty"": 1}")
(customer,"""Mary Clark""")
(items,"{""product"": ""Toy Train"",""qty"": 2}")
(6 rows)
// 回傳 json key 有哪些
danny=# SELECT json_object_keys(info ->'items') from orders_json;
json_object_keys
------------------
product
qty
product
qty
product
qty
(6 rows)
```
### JSON vs JSONB
| 特性 | JSON | JSONB |
|----------------|---------------------|---------------------------|
| 資料儲存格式 | 原始文字 | 自訂二進位格式 |
| 寫入效能 | 較快，僅需最小的驗證 | 較慢，需轉換成二進位 |
| 讀取效能 | 較慢，需要重新解析 | 較快 |
| 空格處理 | 保留空格 | 移除空格 |
| 重複鍵處理 | 保留所有重複鍵 | 只保留最後一個鍵值 |
| 鍵的順序 | 保留鍵的順序 | 可能會被修改 |
| 索引支援 | 否 | 是 |
| JSON項目編輯支援 | 否 | 是 |
## connection info
psql 對於連線限制有兩種控制
1. `max_connections` : db 最大連線數。
2. `superuser_reserved_connections` ： 數據庫預留給超級用戶的連線數。
可以用以下 demo 檢查連線資訊：
`max_connections` : 最大連線數。
` select count(*) from pg_stat_activity` : 目前連線總用戶。
```typescript
root=# show max_connections;
max_connections
-----------------
100
(1 row)
root=# select count(*) from pg_stat_activity;
count
-------
6
(1 row)
```
### 修改 max_connections 並重新啟動 db
```typescript
root=# ALTER SYSTEM SET max_connections=200;
ALTER SYSTEM
root=# SELECT pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
```
## schema
在 `postgres` 中，一個類似於 `workspace` 的概念，就是 `schema` 他可以區隔不同的 `index` 不同的 `table` 等等，其目的是讓你好管理整個 `DB` 的 `data flow` 。
使先我們先進去 `SQL` 中
```typescript
>psql
psql (14.10 (Homebrew), server 15.2)
WARNING: psql major version 14, server major version 15.
Some psql features might not work.
Type "help" for help.
```
`\dn` 開茶看當前有哪些 `schema` 預設情況會有一個 `public` ，所以在不使用我們自己定的 `schema` 前提，我們都適用 `public` 當作當前的 `schema` 。
```typescript
> danny=# \dn;
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
```
所以以下的寫法是一樣的意思。
```typescript
> select * from posts;
```
```typescript
> select * from public.posts;
```
然後我們先 `create` 一個 `schema`
```typescript
>danny=# CREATE SCHEMA hr;
CREATE SCHEMA
```
在打一次 `dn` 你會看到成功新增一個 `schema` 了。
```typescript
>danny=# \dn;
List of schemas
Name | Owner
--------+----------
hr | danny
public | postgres
(2 rows)
```
那要怎麼看到當前的 `schema` 是什麼呢？可以打以下的指令去看，你會看到我們當前就是 `public`。
```typescript
>danny=# select CURRENT_SCHEMA;
current_schema
----------------
public
(1 row)
```
如果要切換 `schema` 可以打 `SET SEARCH_PATH=YOUR_SCHEMA_NAME` 去切換。
```typescript
>danny=# SET SEARCH_PATH=hr;
SET
```
你會看到成功切換 `schmea`
```typescript
>danny=# select CURRENT_SCHEMA;
current_schema
----------------
hr
(1 row)
```
這時候你打 `\dt` 去看當前的 `table` ，你會發現因為你在新的 `schema` 中所以你完全是一個新的 `workspace` 自然也不會有任何的 `table` 紀錄。
```
>danny=# \dt;
Did not find any relations.
```
之後我們先 `create` 一個 `table`。
```typescript
CREATE table test100(name varchar(20),id int);
```
你會看到現在的 `schema` 就成功紀錄一個新 `table` 了。
```typescript
>danny=# \dt;
List of relations
Schema | Name | Type | Owner
--------+---------+-------+-------
hr | test100 | table | danny
(1 row)
```
那上面說道 `schema` 是切換不同工作區，所以 `table` 這些都是獨立分開的，那要怎麼驗證這件事，很簡單就先切換成 `public` 的 `schema` 看看。
```typescript
danny=# SET SEARCH_PATH=public;
SET
```
你會發現根本沒有 `test100` 這個 `table`
```typescript
>danny=# \dt;
List of relations
Schema | Name | Type | Owner
--------+---------------------+-------+----------
public | Account | table | danny
public | CategoriesOnPosts | table | danny
public | Category | table | danny
public | Conversation | table | danny
public | Message | table | danny
public | Post | table | danny
public | Session | table | danny
public | User | table | danny
public | VerificationToken | table | danny
public | _ConversationToUser | table | danny
public | _prisma_migrations | table | postgres
public | _seeners | table | danny
public | timestampTest | table | danny
(13 rows)
```
所以自然也不能 `select`。
```typescript
>danny=# select * from test100;
ERROR: relation "test100" does not exist
LINE 1: select * from test100;
```
最後當你要 `delete` 一個 `schema` 可以直接打 `drop`。
```typescript
>danny=# drop schema hr;
```
但你會發現你完全不能 `drop` 原因是 `schema` 已經有 ` depend on` 特定的 `table` 了，所以如果要 `drop` 已經使用過的 `schema` 顯然是不行的。
```typescript
>danny=# drop schema hr;
ERROR: cannot drop schema hr because other objects depend on it
DETAIL: table hr.test100 depends on schema hr
HINT: Use DROP ... CASCADE to drop the dependent objects too.
```
如果要刪除已經使用過的 `schema` 你必須要加上 `cascades` 這樣才能成功刪除。
```typescript
>danny=# drop schema hr cascade;
NOTICE: drop cascades to table hr.test100
DROP SCHEMA
```
Sign in with Wallet
Connect another wallet