PostgreSQL

スニペット

作成日: 2021/06/27 (更新日: 2023/01/11)

PostgreSQLのスニペット集

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

注意

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

スニペット

文字列

フォーマット

SELECT FORMAT('a = %s, b = %s', 1, 2); -- a = 1, b = 2

パディング

左0埋め

SELECT TO_CHAR(1, 'FM0000'); -- 0001
SELECT LPAD(1::text, 4, '0'); -- 0001

右0埋め

SELECT RPAD(1::text, 4, '0'); -- 1000

日時

リテラル

日付

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

9.9.5. Current Date/Time

計算

加算

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

9.8. Data Type Formatting Functions

配列

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 * FROM unnest(ARRAY[1,2,3]);

--  unnest
-- --------
--       1
--       2
--       3

カンマ区切りの文字列を集合に変換

SELECT * FROM unnest(string_to_array('1,2,3', ',')::int[]);

JSON配列を集合に変換

SELECT * FROM jsonb_path_query('[1,2,3]'::jsonb, '$[*]');

JSONのオブジェクト配列を集合に変換 (レコードにASで定義指定)

SELECT * FROM json_to_recordset('[{"name":"taro","age":20},{"name":"jiro","age":18}]') AS t(name text, age int)

--  name | age
-- ------+-----
--  taro |  20
--  jiro |  18

連番の生成

1～100の表示 (generate_series())

SELECT generate_series(1, 100);

1～100の表示 (generate_series()。別形式)

SELECT i FROM generate_series(1, 100) AS i;

9.25. Set Returning Functions

行番号の付与

WITH ORDINALITYで行番号付与 (1から。ASで列名付与。例の場合「i」)

SELECT * FROM unnest(ARRAY['a','b','c']) WITH ORDINALITY AS t(column1, i);

--  column1 | i
-- ---------+---
--  a       | 1
--  b       | 2
--  c       | 3

9.25. Set Returning Functions

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

固定値をセット

SELECT setval(シーケンス名, 100);

1をセット (次に 1 を返す)

SELECT setval(シーケンス名, 1, false);

テーブルの値から値をセット

SELECT setval(シーケンス名, (SELECT MAX(列名) FROM テーブル名));

9.16. Sequence Manipulation Functions

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

実行時パラメータの設定 (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

定義情報

値の型の確認

pg_typeof

SELECT pg_typeof(int '1234');

--  int4
-- ------
--  1234


SELECT pg_typeof(column1) FROM jsonb_path_query('[1,2,3]'::jsonb, '$[*]') AS t(column1);

--  pg_typeof
-- -----------
--  jsonb
--  jsonb
--  jsonb

9.26. System Information Functions and Operators

テーブルの存在確認

information_schema

SELECT * FROM information_schema.tables
WHERE table_schema = 'スキーマ名' AND table_name = 'テーブル名'

pg_tables

SELECT * FROM pg_tables
WHERE schemaname = 'スキーマ名' AND tablename = 'テーブル名'

54.30. pg_tables

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'

53.11. pg_class

テーブル列の表示

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 = 'public' 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 = 'public' 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

53.7. pg_attribute

列名 (カンマ区切り)

SELECT ARRAY_TO_STRING(ARRAY(
    SELECT column_name FROM information_schema.columns
    WHERE table_catalog='DB名' AND table_schema='public' AND table_name='テーブル名'
    ORDER BY ordinal_position
), ',');

インデックスの存在確認

pg_indexes

SELECT * FROM pg_indexes
WHERE schemaname = 'スキーマ名' AND tablename = 'テーブル名' AND indexname = 'インデックス名'

54.11. pg_indexes

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;

37.17. Packaging Related Objects into an Extension

メッセージの抑制

SET client_min_messages TO WARNING;

19.8. Error Reporting and Logging

関数定義 (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);

4.3. Calling Functions

デフォルト引数

(「引数名型名 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`

psql

CSV インポート

UTF-8

\copy テーブル名 from 'ファイル名' with encoding 'utf8' csv;

UTF-8 + ヘッダーあり

\copy テーブル名 from 'ファイル名' with encoding 'utf8' csv header;

psql (\copy)
COPY

PostgreSQLのスニペット集

目次

注意

スニペット

文字列

フォーマット

パディング

日時

リテラル

文字列からのキャスト

現在日時の取得

計算

フォーマット

配列

ANY

値の連結 (array_to_string())

集合

別の型からの変換

連番の生成

行番号の付与

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

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

定義情報

値の型の確認

テーブルの存在確認

テーブル列の表示

インデックスの存在確認

拡張機能の追加

メッセージの抑制

関数定義 (PL/pgSQL)

レコードの取得

レコードを返す

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

名前付き引数

デフォルト引数

無名コードブロック

PSQL

CSV インポート