PostgreSQL 便利機能メモ

PostgreSQL メモ

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

記事の中に出てくるテーブルなどの情報

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 関連

キャスト

SELECT x002::NUMERIC, x003::TEXT FROM test_tbl1;

SELECT CAST(x002 AS NUMERIC), CAST(x003 AS TEXT) FROM test_tbl1;

取得件数指定

SELECT * FROM test_tbl1
LIMIT 1;

NULLなら置き換え

SELECT COALESCE(x002, x003, 1) FROM test_tbl1

SELECT CASE WHEN x002 IS NULL THEN (CASE WHEN x003 IS NULL THEN 1 ELSE x003 END)
 ELSE x002 END FROM test_tbl1

大文字に置き換え

SELECT UPPER('abc')

SELECT UPPER(x002) FROM test_tbl1

小文字に置き換え

SELECT LOWER('ABC')

SELECT LOWER(x002) FROM test_tbl1

現在日時取得

SELECT CURRENT_DATE
, CURRENT_TIME
, CURRENT_TIMESTAMP
, NOW()
, TIMESTAMP 'NOW'

現在日時取得(DBのタイムゾーンがUTCの場合などにタイムゾーンを考慮)

• CURRENT_TIMESTAMP だけだと時差分の +09 とつくのでタイムゾーンを指定してから TO_CHAR する

SELECT TO_CHAR(CURRENT_TIMESTAMP AT TIME ZONE 'JAPAN', 'YYYY-MM-DD HH24:MI:SS');

      to_char
-------------------
 2020-01-01 02:03:04

過去の時間など

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;

時間の切り捨て

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 が基準)

SELECT EXTRACT(EPOCH FROM NOW());

SELECT EXTRACT(EPOCH FROM 01:02:03'::INTERVAL);

同じカラム名でJOINする場合は USING

SELECT * 
  FROM test_tbl1
  JOIN test_tbl2 USING(x001, x002);

一時的な VIEW は WITH 句

WITH 
q1 AS (
  SELECT * 
    FROM test_tbl1
   WHERE x002 = 'TEST1'
)
SELECT * FROM q1;

SELECT 結果を JSONにする場合は JSON_AGG

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

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レコード後のデータも１レコードにする

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 句を使うと見やすくなる

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);

行番号を取得

SELECT ROW_NUMBER() OVER(ORDER BY x001)
  , x001
  FROM test_tbl1;

グループ内での順番を付加する

SELECT ROW_NUMBER() OVER(PARTITION BY x002 ORDER BY x001)
  , x001
  , x002
  FROM test_tbl1;

グループ内での順位を付加する

SELECT RANK() OVER(PARTITION BY x002 ORDER BY x001)
  , x001
  , x002
  FROM test_tbl1;

グループ内での順位を付加する(同一順位があれば順位は加算しない)

SELECT DENSE_RANK() OVER(PARTITION BY x002 ORDER BY x001)
  , x001
  , x002
  FROM test_tbl1;

出現頻度が一番多い値を取得

SELECT x002
  FROM test_tbl1
 GROUP BY x002
 ORDER BY COUNT('X') DESC
 LIMIT 1;

出現頻度が一番多い値を取得(同じ件数だった場合も考慮)

x002が同じ頻度の場合に、x001順にするなら

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

グループ内で出現頻度が一番多い値を取得

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;

配列の存在チェック

SELECT '["a","b","c"]'::JSONB ? 'a';

SELECT ARRAY['a','b','c'] @> ARRAY['a'];

配列の存在チェック(何れかが含まれる場合)

SELECT '["a","b","c"]'::JSONB ?| ARRAY['c', 'd'];

SELECT ARRAY['a','b','c'] && ARRAY['c', 'd'];

配列の存在チェック(全て含まれる場合)

SELECT '["a","b","c"]'::JSONB ?& ARRAY['b', 'c'];

SELECT ARRAY['a','b','c'] @> ARRAY['b', 'c'];

IN句

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

• 上のIN句と同じ結果

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

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

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

JSONの指定キーの値を取り出す

SELECT '{"a":1}'::JSONB->'a';

配列を行データにする

SELECT UNNEST(ARRAY['a','b','c']);

配列を行データにし行番号も付加する

SELECT UNNEST(ARRAY['a','b','c']) WITH ORDINALITY AS t(val, rowno);

値を JSON キーとして扱う

SELECT JSON_BUILD_OBJECT(x002, x003) FROM test_tbl1;

値をJSON キーとして扱い配列に入れる

SELECT JSON_AGG(JSON_BUILD_OBJECT(x002, x003)) FROM test_tbl1;

SELECT JSON_BUILD_AGG(x002, x003) FROM test_tbl1;

JSON を見やすく成形する

SELECT JSONB_PRETTY('[{"a":1,"b":2},3]');

      jsonb_pretty
-------------------
[
    {
        "a": 1,
        "b": 2
    },
    3
]

テストデータ作成

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

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

SELECT GENERATE_SERIES(1, 100);

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

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', …)

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

テーブル一覧

SELECT schemaname, tablename, tableowner FROM pg_tables;

置換

SELECT REPLACE('TEST1', 'TEST', 'テスト');

replace
---------
テスト1

挿入

SELECT OVERLAY('TEST1' PLACING '_' FROM 5);

overlay
---------
TEST_1

位置指定の置換

• 2文字目から3文字分を置換する

SELECT OVERLAY('TEST1' PLACING '_' FROM 2 FOR 3);

overlay
---------
T_1

繰り返し

• a を 10個

SELECT REPEAT('a', 10)

実行計画

EXPLAIN ANALYZE SELECT * FROM test_tbl1;

ANALYZE を付けると実際にSQLも実行されるので、INSERT や UPDATE 文のコストだけを見たい場合はEXPLAIN だけで実行する。

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

DDL 関連

スキーマ追加

CREATE SCHEMA xxxxx;
ALTER SCHEMA xxxxx OWNER TO testuser;

ユーザ追加

CREATE USER testuser;
ALTER ROLE testuser PASSWORD 'yyyyy'

特定スキーマへの権限追加

GRANT USER ON SCHEMA testschema TO testuser

特権権限の追加

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA testschema TO testuser

SELECT,UPDATE,INSERT,DELETE 特権の追加

GRANT SELECT,UPDATE,INSERT,DELETE ON ALL TABLES IN SCHEMA testschema TO testuser

特定テーブルのみ SELECT 特権の追加

GRANT SELECT ON sc.test_tbl1 TO testuser

CREATE 特権の追加

GRANT CREATE ON SCHEMA testschema TO testuser

カラム追加

ALTER TABLE test_tbl1 ADD COLUMN x004 NUMBER;

パーティショニング

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の確認

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;

プロセスの停止

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 取り込み

\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 の場合

元データ

[
  {"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 を使用する

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 する

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 の場合

元データ

"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レコードごとに () で囲み , で区切ります

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行目のデータに型を指定することもできます。

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 化をすると、扱いやすくなります。

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の場合

キーに一致する値があればアップデートなければインサート

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句によりインサート処理が行われません。
逆にアップデート出来なかった場合には、インサート処理が実行されます。

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