# PostgreSQL

# 日記
* 20250103
* [有趣的特性 Row-Level Security](https://www.cnblogs.com/zhangfx01/p/14367567.html)
* 20241116
* pgsql 跟 mysql 的 explain 資訊差異頗大
* [參考](https://boilingfrog.github.io/2020/04/26/pgsql%E4%B8%ADexplain/)
* [參考2](https://chatgpt.com/c/673778e5-fca4-8005-8cc5-124ec7b69398)
* 今天使用運用 pgsql 的 explain 改善後端的查詢
* 改善前
* 
* 改善後
* 
* 不知道能不能這樣算 但還是來算一下 cost 降低了多少
* 從 4320.16 => 57.47
* 所以 $1 - \frac{57.47}{4320.16} \approx 0.986697$
* (大概)可以說這次改善降低了 98.67% 的成本

# docker 建立 postgres
* [參考](https://old-oomusou.goodjack.tw/docker/postgres/)

## 使用 docker

## 使用 docker-compose

# 指令

## 登入

psql -d databasename -U username

## Meta-Commands (斜線指令)

# 離開
\q

# 列出所有資料庫
\l

# 列出所有 Tables
\d

# 顯示表格資訊
\d table_name

# 列出 table schema
\d+ [tablename]

# 列出使用者
SELECT * FROM "pg_user";

# 列出所有角色 + 權限
\du

# 列出該 db schema 與角色的權限
\dn+

# 列出 Table/View 權限
\dp

# 查看 table 權限
\z

## 操作

# 修改密碼
alter user user_name with password 'new_password';

# 列出當前使用者
SELECT current_user;

# 授權
grant all on schema public to public;

# 移除 db (OSX)
rm /usr/local/var/postgres/postmaster.pid
dropdb rbase_test

# 在 SQL 中移除 db
drop database if exists [database name]

# 建立使用者
CREATE USER andy with password 'p@ssw0rd';
createuser test --interactive -P # 含建立密碼 + 權限

# 連線資料庫/切換資料庫
\c database

# 建立新資料庫
CREATE DATABASE mydb;

# 刪除 table
DROP TABLE IF EXISTS table;

# 建立 table 範例
CREATE TABLE films (
  code        char(5) CONSTRAINT firstkey PRIMARY KEY,
  title       varchar(40) NOT NULL,
  did integer NOT NULL,
  date_prod   date,
  kind        varchar(10),
  len         interval hour to minute
);

# 插入資料
INSERT INTO films VALUES
('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');

INSERT INTO films (code, title, did, date_prod, kind)
VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama'),
('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

# 備份 / 還原 使用 Dump
# 備份
pg_dump dbname > outfile
pg_dump dbname | gzip > filename.gz

# 還原
psql dbname < infile
createdb dbname && gunzip -c filename.gz | psql dbname

# 更新
update auth_user set is_superuser = 't' where username='abhiomkar';

# 顯示所有 roles 名稱(SQL)
select rolname from pg_roles;

# 移除 db (OSX)
rm /usr/local/var/postgres/postmaster.pid
dropdb rbase_test

# 實用文章

## [[PSQL] PostgreSQL CLI](https://pjchender.dev/database/psql-cli/)

## [他人 Psql 學習記錄](https://andyyou.github.io/2015/04/06/psql-notes/)