PostgreSQL のスニペット集

このページは、PostgreSQL の SQL のスニペットをまとめる予定のページです。

目次

注意

  • このページを作ったひとが備忘のためにまとめているため網羅性はありません。
  • RDBMS の SQL の比較 にも PostgreSQL の SQL について記載があります。

スニペット

日時

リテラル

日付
SELECT date '2021-01-01';
時間
SELECT time '01:02:03';
日時
SELECT timestamp '2021-01-01 00:00:00';
日時 (タイムゾーンあり)
SELECT timestamp with time zone '2021-01-01 00:00:00+09:00';
間隔
SELECT interval '1 day';

文字列からのキャスト

::型名
SELECT '2021-01-01'::date;
SELECT '01:02:03'::time;
SELECT '2021-01-01 00:00:00'::timestamp;
SELECT '2021-01-01 00:00:00+09:00'::timestamptz;
SELECT '1 day'::interval;
CAST()
SELECT CAST('2021-01-01' AS date);
SELECT CAST('01:02:03' AS time);
SELECT CAST('2021-01-01 00:00:00' AS timestamp);
SELECT CAST('2021-01-01 00:00:00+09:00' AS timestamp with time zone);
SELECT CAST('1 day' AS interval);

現在日時の取得

timestamp with time zone
SELECT CURRENT_TIMESTAMP;
UTC。timestamp without time zone (YYYY-MM-DD HH:MM:SS)
SELECT CURRENT_TIMESTAMP(0) AT TIME ZONE 'UTC';
JST。timestamp without time zone (YYYY-MM-DD HH:MM:SS)
SELECT CURRENT_TIMESTAMP(0) AT TIME ZONE 'Asia/Tokyo';
ローカル日時。timestamp without time zone (YYYY-MM-DD HH:MM:SS)
SELECT LOCALTIMESTAMP(0);

計算

加算
SELECT timestamp '2021-01-01 00:00:00' + interval '1 year'; -- → 2022-01-01 00:00:00
SELECT timestamp '2021-01-01 00:00:00' + interval '1 month'; -- → 2021-02-01 00:00:00
SELECT timestamp '2021-01-01 00:00:00' + interval '1 day'; -- → 2021-01-02 00:00:00
SELECT timestamp '2021-01-01 00:00:00' + interval '1 hour'; -- → 2021-01-01 01:00:00
SELECT timestamp '2021-01-01 00:00:00' + interval '1 minute'; -- → 2021-01-01 00:01:00
SELECT timestamp '2021-01-01 00:00:00' + interval '1 second'; -- → 2021-01-01 00:00:01

フォーマット

YYYY/MM/DD
SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY/MM/DD');
HH:MM:SS
SELECT TO_CHAR(CURRENT_TIMESTAMP, 'HH24:MI:SS');
YYYY/MM/DD HH:MM:SS
SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS');
数値(分) → HH:MM
SELECT TO_CHAR((1500 || ' minutes')::interval, 'HH24:MI'); -- → 25:00

配列

ANY

IN の代わりに配列を使用する
SELECT * FROM emp WHERE id = ANY(array[1, 2, 3]);
変数で使用する例
DO $$
DECLARE
	arr1 int[] := array[1, 2, 3];
	rec RECORD;
BEGIN
	FOR rec IN SELECT * FROM emp WHERE id = ANY(arr1) LOOP
		RAISE NOTICE '%', rec.id;
	END LOOP;
END $$

値の連結 (array_to_string())

カンマ区切りで連結
SELECT array_to_string(array[1, 2, 3], ','); -- '1,2,3'
array_agg() で集約した配列を連結
SELECT array_to_string(array_agg(name), ',') FROM emp WHERE id IN (1, 2, 3);

シーケンスの値の手動セット

固定値をセット
SELECT setval(シーケンス名, 100);
1をセット (次に 1 を返す)
SELECT setval(シーケンス名, 1, false);
テーブルの値から値をセット
SELECT setval(シーケンス名, (SELECT MAX(列名) FROM テーブル名));

generate_series()

1~100の表示
SELECT generate_series(1, 100);
1~100の表示 (別形式)
SELECT i FROM generate_series(1, 100) AS i;

実行時パラメータの設定・取得

実行時パラメータの設定 (set_config)
SELECT set_config('test.value', 'abc', false);
実行時パラメータの設定 (SET)
SET test.value = 'abc';
実行時パラメータの表示 (current_setting)
SELECT current_setting('test.value');
実行時パラメータの表示 (SHOW)
SHOW test.value;
実行時パラメータの表示 (すべて)
SHOW ALL

定義情報

テーブルの存在確認

information_schema
SELECT * FROM information_schema.tables
WHERE table_schema = 'スキーマ名' AND table_name = 'テーブル名'
pg_tables
SELECT * FROM pg_tables
WHERE schemaname = 'スキーマ名' AND tablename = 'テーブル名'
pg_class
SELECT * FROM pg_class AS c
INNER JOIN pg_namespace  AS n ON c.relnamespace = n.oid
WHERE n.nspname = 'スキーマ名' AND c.relname = 'テーブル名' AND c.relkind = 'r'

テーブル列の表示

information_schema
SELECT
  table_name AS "テーブル名",
  ordinal_position AS "No.",
  column_name AS "列名",
  data_type AS "型名",
  character_maximum_length AS "文字数",
  column_default AS "デフォルト値",
  column_comment AS "コメント"
FROM information_schema.columns
WHERE table_catalog = 'DB名' AND table_schema = 'スキーマ名' AND table_name = 'テーブル名'
ORDER BY ordinal_position;
information_schema (主キー込)
SELECT
  table_name AS "テーブル名",
  ordinal_position AS "No.",
  column_name AS "列名",
  data_type AS "型名",
  character_maximum_length AS "文字数",
  column_default AS "デフォルト値",
  CASE WHEN pk.column_name IS NOT NULL THEN true ELSE false END AS "主キー",
  column_comment AS "コメント"
FROM information_schema.columns AS c
LEFT JOIN (
  SELECT kc.table_catalog, kc.table_schema, kc.table_name, kc.column_name
  FROM information_schema.key_column_usage AS kc
  INNER JOIN information_schema.table_constraints AS tc ON tc.constraint_type = 'PRIMARY KEY' AND tc.constraint_name = kc.constraint_name
) AS pk USING (table_catalog, table_schema, table_name, column_name)
WHERE c.table_catalog = 'DB名' AND c.table_schema = 'スキーマ名' AND c.table_name = 'テーブル名'
ORDER BY c.ordinal_position;
pg_attribute
SELECT
  attrelid::regclass, -- テーブル名
  attname, -- テーブル列名
  atttypid::regtype AS datatype, -- 型名
  information_schema._pg_char_max_length(atttypid, atttypmod) AS max_length, -- 長さ
  (SELECT pg_get_expr(d.adbin, d.adrelid) FROM pg_attrdef AS d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum) AS default -- 既定値
FROM pg_attribute AS a
WHERE
  attrelid = 'テーブル名'::regclass
  AND attnum > 0 -- システム列以外
  AND NOT attisdropped -- 削除されていない列
ORDER BY attnum

インデックスの存在確認

pg_indexes
SELECT * FROM pg_indexes
WHERE schemaname = 'スキーマ名' AND tablename = 'テーブル名' AND indexname = 'インデックス名'
pg_class
SELECT * FROM pg_class AS c
INNER JOIN pg_namespace  AS n ON c.relnamespace = n.oid
WHERE n.nspname = 'スキーマ名' AND c.relname = 'インデックス名' AND c.relkind = 'i'

拡張機能の追加

pgcrypto を追加する例
CREATE EXTENSION IF NOT EXISTS pgcrypto;

メッセージの抑制

SET client_min_messages TO WARNING;

関数定義 (PL/pgSQL)

加算する関数定義の例
CREATE OR REPLACE FUNCTION F1(v1 int, v2 int)
RETURNS int AS $$ -- 戻り値
DECLARE
    ret int; -- 変数定義
BEGIN
    ret := v1 + v2;
    RETURN ret;
END;
$$  LANGUAGE plpgsql;

-- 使用
SELECT F1(1, 2); -- 3

-- 破棄
DROP FUNCTION F1;

レコードの取得

レコードの取得, 確認
CREATE OR REPLACE FUNCTION F1()
RETURNS void AS $$
DECLARE
    rec RECORD;
BEGIN
    SELECT * INTO rec FROM emp WHERE id = 1;
    IF FOUND THEN
        RAISE INFO 'found emp_name=%', rec.emp_name;
    ELSE
        RAISE INFO 'not found';
    END IF;
END;
$$ LANGUAGE plpgsql;

SELECT F1();
DROP FUNCTION F1;
複数レコードの取得 (FOR IN。IN の後ろに直接クエリを指定しても可)
CREATE OR REPLACE FUNCTION F1()
RETURNS void AS $$
DECLARE
    rec RECORD;
    cur CURSOR FOR SELECT * FROM emp WHERE gender = 1 ORDER BY id;
BEGIN
    FOR rec IN cur LOOP
        RAISE INFO 'emp_name=%', rec.emp_name;
    END LOOP;
END;
$$  LANGUAGE plpgsql;

SELECT F1();
DROP FUNCTION F1;
複数レコードの取得 (LOOP)
CREATE OR REPLACE FUNCTION F1()
RETURNS void AS $$
DECLARE
    rec RECORD;
    cur CURSOR FOR SELECT * FROM emp WHERE gender = 1 ORDER BY id;
BEGIN
    OPEN cur;
    LOOP
        FETCH cur INTO rec;
        EXIT WHEN NOT FOUND;
        RAISE INFO 'emp_name=%', rec.emp_name;
    END LOOP;
    CLOSE cur;
END;
$$ LANGUAGE plpgsql;

SELECT F1();
DROP FUNCTION F1;

レコードを返す

複数レコードを返す (RETURN QUERY)
CREATE OR REPLACE FUNCTION F1()
RETURNS TABLE(LIKE emp) AS $$
BEGIN
    RETURN QUERY SELECT * FROM emp;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM F1();
DROP FUNCTION F1;

※ 既存のテーブルと同じ列を返す場合、TABLE(LIKE テーブル名)SETOF テーブル名 で戻り値を指定します。

複数レコードを返す (一部の列)
CREATE OR REPLACE FUNCTION F1()
RETURNS TABLE(id bigint) AS $$
BEGIN
    RETURN QUERY SELECT emp.id FROM emp;
END;
$$  LANGUAGE plpgsql;

SELECT * FROM F1();
DROP FUNCTION F1;

SELECT の列名と TABLE() で返す列名を同じにするとエラーになるため、下記のいずれかをする必要があります。

  • TABLE() の列名を変える (例だと TABLE(_id bigint))
  • SELECT の列にテーブル名を追加する (例だと emp.id)
  • BEGIN の前に #variable_conflict use_column を指定する (43.11.1. Variable Substitution)

関数内でのメッセージ出力

CREATE OR REPLACE FUNCTION F1()
RETURNS void AS $$
BEGIN
    RAISE 'ERRORメッセージ';
    -- RAISE WARNING 'WARNINGメッセージ';
    -- RAISE INFO 'INFOメッセージ';
    -- RAISE DEBUG 'DEBUGメッセージ';
END;
$$  LANGUAGE plpgsql;

-- 使用・破棄
SELECT F1();
DROP FUNCTION F1;

名前付き引数

(「引数名 := 値」の形で指定)
SELECT F1(v1 := 1, v2 := 2);

デフォルト引数

(「引数名 型名 default デフォルト値」の形で指定)
CREATE OR REPLACE FUNCTION F1(v1 int, v2 int, v3 int default 3)
RETURNS int AS $$ -- 戻り値
DECLARE
    ret int; -- 変数定義
BEGIN
    ret := v1 + v2 + v3;
    RETURN ret;
END;
$$  LANGUAGE plpgsql;

SELECT F1(1, 2); -- 6
SELECT F1(1, 2, 0); -- 3
DROP FUNCTION F1;

無名コードブロック

DO $$
DECLARE
    ret int; -- 変数定義
BEGIN
    ret := 1 + 2;
    RAISE INFO 'value=%', ret; -- value=3
END $$

PSQL

コマンド
エンコーディング指定 \encoding UTF8
テーブル表示 \dt
DB 切替 \c DB 名
SQL ファイルの実行 \i ファイル名
拡張表示切り替え \x
終了 \q

CSV インポート

UTF-8
\copy テーブル名 from 'ファイル名' with encoding 'utf8' csv;
UTF-8 + ヘッダーあり
\copy テーブル名 from 'ファイル名' with encoding 'utf8' csv header;