PostgreSQL

日記

• 20250103
  • 有趣的特性 Row-Level Security
• 20241116
  • pgsql 跟 mysql 的 explain 資訊差異頗大
    • 參考
    • 參考2
  • 今天使用運用 pgsql 的 explain 改善後端的查詢
    • 改善前
    • 改善後
    • 不知道能不能這樣算 但還是來算一下 cost 降低了多少
    • 從 4320.16 => 57.47
    • 所以
      $1-\frac{57.47}{4320.16}\approx 0.986697$
    • (大概)可以說這次改善降低了 98.67% 的成本

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

他人 Psql 學習記錄