PostgreSQL 便利機能メモ

# PostgreSQL メモ

PostgreSQL を扱っていた時に、便利だと思った事などの覚書になります。

# 記事の中に出てくるテーブルなどの情報
```sql
CREATE DATABASE testdb;
CREATE SCHEMA testschema;
CREATE USER testuser;
ALTER ROLE testuser PASSWORD 'testpassword';
ALTER SCHEMA testschema OWNER TO testuser;

CREATE TABLE test_tbl1 (
  x001 TIMESTAMP,
  x002 TEXT,
  x003 INT,
  PRIMARY KEY (x001)
);

CREATE TABLE test_tbl2 (
  x001 TIMESTAMP,
  x002 TEXT,
  x003 INT,
  x004 JSONB,
  PRIMARY KEY (x001)
);

CREATE TABLE test_tbl3 (
  x001 TIMESTAMP,
  seq  INT,
  x002 TEXT,
  PRIMARY KEY (x001, seq)
);
```

# SELECT 関連

## キャスト
```sql
SELECT x002::NUMERIC, x003::TEXT FROM test_tbl1;
```
```sql
SELECT CAST(x002 AS NUMERIC), CAST(x003 AS TEXT) FROM test_tbl1;
```

## 取得件数指定
```sql
SELECT * FROM test_tbl1
LIMIT 1;
```

## NULLなら置き換え
```sql
SELECT COALESCE(x002, x003, 1) FROM test_tbl1
```
```sql
SELECT CASE WHEN x002 IS NULL THEN (CASE WHEN x003 IS NULL THEN 1 ELSE x003 END)
 ELSE x002 END FROM test_tbl1
```

## 大文字に置き換え
```sql
SELECT UPPER('abc')
```
```sql
SELECT UPPER(x002) FROM test_tbl1
```

## 小文字に置き換え
```sql
SELECT LOWER('ABC')
```
```sql
SELECT LOWER(x002) FROM test_tbl1
```

## 現在日時取得
```sql
SELECT CURRENT_DATE
, CURRENT_TIME
, CURRENT_TIMESTAMP
, NOW()
, TIMESTAMP 'NOW'
```
## 現在日時取得(DBのタイムゾーンがUTCの場合などにタイムゾーンを考慮)
- CURRENT_TIMESTAMP だけだと時差分の +09 とつくのでタイムゾーンを指定してから TO_CHAR する

```sql
SELECT TO_CHAR(CURRENT_TIMESTAMP AT TIME ZONE 'JAPAN', 'YYYY-MM-DD HH24:MI:SS');
```
```
      to_char
-------------------
 2020-01-01 02:03:04
```

## 過去の時間など
```sql
SELECT NOW()
, NOW() - '1 SEC'::INTERVAL
, NOW() - '1 MIN'::INTERVAL
, NOW() - '1 HOUR'::INTERVAL
, NOW() - '1 DAY'::INTERVAL
, NOW() - '1 MONTH'::INTERVAL
, NOW() - '1 YEAR'::INTERVAL
, NOW() - '01:02:03'::INTERVAL
, NOW() - '1 02:03:04'::INTERVAL
, NOW() - '01-02 3 04:05:06'::INTERVAL
, NOW() - '1 YEAR 2 MONTH 3 DAY 4 HOUR 5 MIN 6 SEC'::INTERVAL;
```

## 時間の切り捨て
```sql
SELECT NOW()
, DATA_TRUNC('SEC', NOW())
, DATA_TRUNC('MIN', NOW())
, DATA_TRUNC('HOUR', NOW())
, DATA_TRUNC('DAY', NOW())
, DATA_TRUNC('MONTH', NOW())
, DATA_TRUNC('YEAR', NOW());
```
## 時間を秒数で表す(INTERVALならそのままで、DATE型やTIMESTAMP型の場合は 1970-01-01 00:00:00 が基準)
```sql
SELECT EXTRACT(EPOCH FROM NOW());
```
```sql
SELECT EXTRACT(EPOCH FROM 01:02:03'::INTERVAL);
```

## 同じカラム名でJOINする場合は USING
```sql
SELECT * 
  FROM test_tbl1
  JOIN test_tbl2 USING(x001, x002);
```

## 一時的な VIEW は WITH 句
```sql
WITH 
q1 AS (
  SELECT * 
    FROM test_tbl1
   WHERE x002 = 'TEST1'
)
SELECT * FROM q1;
```

## SELECT 結果を JSONにする場合は JSON_AGG
```sql
WITH 
q1 AS (
  SELECT * 
    FROM test_tbl1
   WHERE x002 = 'TEST1'
)
SELECT JSON_AGG(q1 ORDER BY x001) FROM q1;
```

## 親子テーブルの SELECT 結果を親子テーブルの形で JSON にする場合は JSON_AGG + JSON_BUILD_OBJECT
```sql
WITH 
q1 AS (
  SELECT t1.x001
       , t1.x002
       , JSON_AGG(
         JSON_BUILD_OBJECT(
           t3.seq
           t3.x002)
         ORDER BY t3.seq) AS child
    FROM test_tbl1 t1
    JOIN test_tbl3 t3 USING(x001)
   GROUP BY
         t1.x001
       , t1.x002
)
SELECT JSON_AGG(q1 ORDER BY x001) FROM q1;
```


## 前後のデータ は LAG LEAD
- 2レコード前から2レコード後のデータも１レコードにする
 
```sql
SELECT 
       x001,
       LAG(x003 , 2, null) OVER (ORDER BY x002) AS x003_lag2, -- 2レコード前
       LAG(x003 , 1, null) OVER (ORDER BY x002) AS x003_lag1, -- 1レコード前
       x003,
       LEAD(x003 , 1, null) OVER (ORDER BY x001) AS x003_lead1, -- 1レコード後
       LEAD(x003 , 2, null) OVER (ORDER BY x001) AS x003_lead2 -- 2レコード後
  FROM test_tbl1;
```

## 前後のデータ OVER の引数に WINDOW 句も使用
- 同じ OVER 句がある場合は WINDOW 句を使うと見やすくなる

```sql
SELECT 
       x001,
       LAG(x003 , 2, null) OVER wd AS x003_lag2, -- 2レコード前
       LAG(x003 , 1, null) OVER wd AS x003_lag1, -- 1レコード前
       x003,
       LEAD(x003 , 1, null) OVER wd AS x003_lead1, -- 1レコード後
       LEAD(x003 , 2, null) OVER wd AS x003_lead2 -- 2レコード後
  FROM test_tbl1
  WINDOW wd AS (ORDER BY x001);
```

## 行番号を取得
```sql
SELECT ROW_NUMBER() OVER(ORDER BY x001)
  , x001
  FROM test_tbl1;
```
## グループ内での順番を付加する
```sql
SELECT ROW_NUMBER() OVER(PARTITION BY x002 ORDER BY x001)
  , x001
  , x002
  FROM test_tbl1;
```

## グループ内での順位を付加する
```sql
SELECT RANK() OVER(PARTITION BY x002 ORDER BY x001)
  , x001
  , x002
  FROM test_tbl1;
```

## グループ内での順位を付加する(同一順位があれば順位は加算しない)
```sql
SELECT DENSE_RANK() OVER(PARTITION BY x002 ORDER BY x001)
  , x001
  , x002
  FROM test_tbl1;
```

## 出現頻度が一番多い値を取得
```sql
SELECT x002
  FROM test_tbl1
 GROUP BY x002
 ORDER BY COUNT('X') DESC
 LIMIT 1;
```

## 出現頻度が一番多い値を取得(同じ件数だった場合も考慮)
x002が同じ頻度の場合に、x001順にするなら

```sql
SELECT x002
  FROM test_tbl1
 GROUP BY x002
 ORDER BY COUNT('X') DESC, MIN(x001)
 LIMIT 1;
```

## グループ内で出現頻度が一番多い値を取得
```sql
SELECT x003 
     , (SELECT x002
          FROM test_tbl1 b
         WHERE b.x003 = a.x003
         GROUP BY x002
         ORDER BY COUNT(x002) DESC
         LIMIT 1)
  FROM test_tbl1 a
 GROUP BYx003;
```

## 配列の存在チェック
```sql
SELECT '["a","b","c"]'::JSONB ? 'a';
```
```sql
SELECT ARRAY['a','b','c'] @> ARRAY['a'];
```
## 配列の存在チェック(何れかが含まれる場合)
```sql
SELECT '["a","b","c"]'::JSONB ?| ARRAY['c', 'd'];
```
```sql
SELECT ARRAY['a','b','c'] && ARRAY['c', 'd'];
```

## 配列の存在チェック(全て含まれる場合)
```sql
SELECT '["a","b","c"]'::JSONB ?& ARRAY['b', 'c'];
```
```sql
SELECT ARRAY['a','b','c'] @> ARRAY['b', 'c'];
```

## IN句

```sql
SELECT *
  FROM test_tbl1
 WHERE x002 IN ('a','b')
```

- 上のIN句と同じ結果

```sql
SELECT *
  FROM test_tbl1
 WHERE x002 = ANY(STRING_TO_ARRAY('a,b', ','))
```

- 配列の値を一つの値として渡せるので、IN句でのバインドも可能

```sql
SELECT * 
  FROM test_tbl1
 WHERE x002 = ANY(STRING_TO_ARRAY($1, ','))
```

## JSONの指定キーの値を取り出す
```sql
SELECT '{"a":1}'::JSONB->'a';
```

## 配列を行データにする
```sql
SELECT UNNEST(ARRAY['a','b','c']);
```
## 配列を行データにし行番号も付加する
```sql
SELECT UNNEST(ARRAY['a','b','c']) WITH ORDINALITY AS t(val, rowno);
```

## 値を JSON キーとして扱う
```sql
SELECT JSON_BUILD_OBJECT(x002, x003) FROM test_tbl1;
```

## 値をJSON キーとして扱い配列に入れる
```sql
SELECT JSON_AGG(JSON_BUILD_OBJECT(x002, x003)) FROM test_tbl1;
```
```sql
SELECT JSON_BUILD_AGG(x002, x003) FROM test_tbl1;
```

## JSON を見やすく成形する
```sql
SELECT JSONB_PRETTY('[{"a":1,"b":2},3]');
```
```
      jsonb_pretty
-------------------
[
    {
        "a": 1,
        "b": 2
    },
    3
]
```

## テストデータ作成
GENERARE_SERIES を使えばテスト用データなどを生成することが出来ます。

- 1 から 100 までのデータ作成 (1, 2, 3, 4, …)

```sql
SELECT GENERATE_SERIES(1, 100);
```

- 1 から 100 までのデータで1つ飛ばし (1, 3, 5, 7, …)

```sql
SELECT GENERATE_SERIES(1, 100, 2);
```

- 2021-01-01 から 2021-01-31までの1分ごとのデータ ('2021-01-01 00:00:00', '2021-01-01 00:01:00', '2021-01-01 00:02:00', '2021-01-01 00:03:00', …)

```sql
SELECT GENERATE_SERIES('2021-01-01 00:00:00'::TIMESTAMP, '2021-01-31 23:59:59'::TIMESTAMP, '1 MINUTE');
```

## テーブル一覧
```sql
SELECT schemaname, tablename, tableowner FROM pg_tables;
```


## 置換
```sql
SELECT REPLACE('TEST1', 'TEST', 'テスト');
```
```
replace
---------
テスト1
```
## 挿入
```sql
SELECT OVERLAY('TEST1' PLACING '_' FROM 5);
```
```
overlay
---------
TEST_1
```

## 位置指定の置換
- 2文字目から3文字分を置換する

```sql
SELECT OVERLAY('TEST1' PLACING '_' FROM 2 FOR 3);
```
```
overlay
---------
T_1
```

## 繰り返し
- a を 10個

```sql
SELECT REPEAT('a', 10)
```

## 実行計画
```sql
EXPLAIN ANALYZE SELECT * FROM test_tbl1;
```
ANALYZE を付けると実際にSQLも実行されるので、INSERT や UPDATE 文のコストだけを見たい場合はEXPLAIN だけで実行する。

```sql
EXPLAIN INSERT INTO test_tbl1 (x001, x002, x003)  VALUES ('2021-01-01 00:00:00', 'TEST1', 111);
```

# DDL 関連

## スキーマ追加
```sql
CREATE SCHEMA xxxxx;
ALTER SCHEMA xxxxx OWNER TO testuser;
```

## ユーザ追加
```sql
CREATE USER testuser;
ALTER ROLE testuser PASSWORD 'yyyyy'
```

## 特定スキーマへの権限追加
```sql
GRANT USER ON SCHEMA testschema TO testuser
```

## 特権権限の追加
```sql
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA testschema TO testuser
```

## SELECT,UPDATE,INSERT,DELETE 特権の追加
```sql
GRANT SELECT,UPDATE,INSERT,DELETE ON ALL TABLES IN SCHEMA testschema TO testuser
```

## 特定テーブルのみ SELECT 特権の追加
```sql
GRANT SELECT ON sc.test_tbl1 TO testuser
```

## CREATE 特権の追加
```sql
GRANT CREATE ON SCHEMA testschema TO testuser
```

## カラム追加
```sql
ALTER TABLE test_tbl1 ADD COLUMN x004 NUMBER;
```

## パーティショニング
```sql
CREATE TABLE test_tbl1 (
  x001 TIMESTAMP,
  x002 TEXT,
  x003 INT,
  PRIMARY KEY (x001)
) PARTITION BY RENGE (x001);

CREATE TABLE test_tbl1_2021_01 OF test_tbl1
  FOR VALUES FROM ('2021-01-01') TO ('2021-02-01');
CREATE TABLE test_tbl1_2021_02 OF test_tbl1
  FOR VALUES FROM ('2021-02-01') TO ('2021-03-01');
```

# ロック確認
## プロセスIDの確認
```sql
SELECT l.pid, db.datname, c.relname, l.locktype, l.mode
  FROM pg_locks l
  LEFT JOIN pg_class c ON l.relation=c.relfilenode
  LEFT JOIN pg_database db ON l.database = db.oid
ORDER BY l.pid;
```
## プロセスの停止
```sql
SELECT pg_cancel_backend(プロセスID);
```

# psql 関連
## 接続
```
psql -h xx.xx.xx.xx -d testdb -U testuser
```

```
psql -h xx.xx.xx.xx -p 5432 -d testdb -U testuser
```

## SSL接続
内部ネットワーク以外にDBがある等、SSL接続で暗号化を行いたい場合。

```
psql "sslmode=verify-ca sslrootcert=client-cert.pem hostadd=xx.xx.xx.xx port=5432 dbname=testdb user=testuser" 
```

## DB一覧
```
\l
```
## オジェクト一覧 (あまり使わないかも)
```
\d
```
## テーブル一覧
```
\dt
```
## ビュー一覧
```
\dv
```
## ユーザ一覧
```
\du
```
## 権限確認
```
\z <TABLE_NAME>
```

## CSV 出力
```
\COPY (SELECT * FROM test_tbl1) TO '~/xxxx.csv' WITH CSV DELIMITER ',' HEADER FORCE QUOTE * 
```

### CSV 取り込み
```sql
\COPY test_tbl1 FROM 'xxxx.csv' WITH CSV
```

## 接続終了
```
\q
```

## SQL ファイルの実行
```
psql -h xx.xx.xx.xx -p 5432 -d testdb -U testuser -f ~/xxxx.sql
```

# データ登録

## 元データが JSON の場合
### 元データ
```json
[
  {"x001": "2021-01-01 00:00:00", "x002": "TEST1", "x003": 111},
  {"x001": "2021-01-01 00:00:01", "x002": "TEST2", "x003": 222},
  {"x001": "2021-01-01 00:00:02", "x002": "TEST3", "x003": 333},
]
```
## JSON データを仮テーブル化
JSONをテーブルとして扱う場合は JSON_TO_RECORDSET を使用する

```sql
SELECT * JSON_TO_RECORDSET('
[
  {"x001": "2021-01-01 00:00:00", "x002": "TEST1", "x003": 111},
  {"x001": "2021-01-01 00:00:01", "x002": "TEST2", "x003": 222},
  {"x001": "2021-01-01 00:00:02", "x002": "TEST3", "x003": 333}
]
') AS x("x001" TIMESTAMP, "x002" TEXT, "x003" INT);
```

### 元データが JSON 時の INSERT 文
INSERT する場合は JSON_TO_RECORDSET で仮テーブルを作成し INSERT する

```sql
INSERT INTO test_tbl1 (x001, x002, x003)
SELECT * JSON_TO_RECORDSET('
[
  {"x001": "2021-01-01 00:00:00", "x002": "TEST1", "x003": 111},
  {"x001": "2021-01-01 00:00:01", "x002": "TEST2", "x003": 222},
  {"x001": "2021-01-01 00:00:02", "x002": "TEST3", "x003": 333}
]
') AS x("x001" TIMESTAMP, "x002" TEXT, "x003" INT);
```

## 元データが CSV の場合
### 元データ

```csvs
"x001", "x002", "x003"
"2021-01-01 00:00:00", "TEST1", 111
"2021-01-01 00:00:01", "TEST2", 222
"2021-01-01 00:00:02", "TEST3", 333
```

### 元データが CSV 時の INSERT 文(BULK INSERT)
通常の INSERT 文はカンマで区切ると一括で登録することが可能です
" を ' に置き換えて 1レコードごとに () で囲み , で区切ります

```sql
INSERT INTO test_tbl1 (x001, x002, x003)
  VALUES
    ('2021-01-01 00:00:00', 'TEST1', 111),
    ('2021-01-01 00:00:01', 'TEST2', 222),
    ('2021-01-01 00:00:02', 'TEST3', 333);
```

## CSV データを仮テーブル化
VALUES を付けて、" を ' に置き換えて 1レコードごとに () で囲み , で区切ります。
また VALUES の1行目のデータに型を指定することもできます。

```sql
VALUES
  ('2021-01-01 00:00:00'::TIMESTAMP, 'TEST1', 111),
  ('2021-01-01 00:00:01', 'TEST2', 222),
  ('2021-01-01 00:00:02', 'TEST3', 333);
```

## CSV データを仮テーブル化2
上記の SQL では扱いずらいので一時的な VIEW 化をすると、扱いやすくなります。

```sql
WITH q1(x001, x002, x003) AS (
  VALUES
    ('2021-01-01 00:00:00'::TIMESTAMP, 'TEST1', 111),
    ('2021-01-01 00:00:01', 'TEST2', 222),
    ('2021-01-01 00:00:02', 'TEST3', 333)
)
SELECT * FROM q1;
```

## UPSERT文
### 1レコードのUPSERTの場合
キーに一致する値があればアップデートなければインサート

```sql
INSERT INTO test_tbl1
  VALUES
    ('2021-01-01 00:00:00', 'TEST1', 111）
  ON CONFLICT ON CONSTRAINT test_tbl1_keys
  DO UPDATE SET 
    x001 = '2021-01-01 00:00:00'
  , x002 = 'TEST1'
  , x003 = 111;
```

### 複数レコードのUPSERTの場合
q1 で一時的な VIEW を作成し up1 を実行します。
アップデートが成功すると RETURNING で tt1.x001 の値が帰ってくるので、NOT EXISTS句によりインサート処理が行われません。
逆にアップデート出来なかった場合には、インサート処理が実行されます。

```sql
WITH q1(x001, x002, x003) AS (
  VALUES
    ('2021-01-01 00:00:00', 'TEST1', 111),
    ('2021-01-01 00:00:01', 'TEST2', 222),
    ('2021-01-01 00:00:02', 'TEST3', 333)
),
up1 AS (
  UPDATE test_tbl1 tt1
     SET x002 = q1.x002
       , x003 = q1.x003
    FROM q1
   WHERE tt1.x001 = n.x001
  RETURNING tt1.x001
)
INSERT INTO test_tbl1 (x001, x002, x003)
SELECT q1.x001
     , q1.x002
     , q1.x003
  FROM q1
 WHERE NOT EXISTS (
   SELECT 'X' 
     FROM up1
    WHERE x001 = q1.x001
 )
```

# バックアップとリストア
## バックアップ
```
pg_dump -h xx.xx.xx.xx -U testuser testdb > xxxx
```
- -Fc オプションを付けなければSQL形式で出力されます。ただし処理は遅くなります。

## アーカイブ形式でバックアップ 
```
pg_dump -h xx.xx.xx.xx -U testuser -Fc testdb > xxxx
```
## スキーマ指定でバックアップ 
```
pg_dump -h xx.xx.xx.xx -U testuser -Fc -t testschema.* testdb > xxxx
```
## テーブル指定でバックアップ 
```
pg_dump -h xx.xx.xx.xx -U testuser -Fc -t testschema.test_tbl1 -t testschema.test_tbl2 testdb > xx0xx
```

## リストア
```
pg_restore -h xx.xx.xx.xx -U testuser testdb < xxxx
```
## オブジェクトを削除してからリストア
```
pg_restore -c -h xx.xx.xx.xx -U testuser testdb < xxxx
```