PostgreSQL のスニペット集

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

目次

注意

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

スニペット

日時

現在日時の取得

UTC。timestamp without time zone (YYYY-MM-DD HH:MM:SS)
SELECT CURRENT_TIMESTAMP(0) AT TIME ZONE 'UTC'

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

固定値をセット
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

関数定義

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

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

-- 破棄
DROP FUNCTION F1;

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

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;

テーブル列の表示

information_schema
SELECT
  table_name, -- テーブル名
  column_name, -- テーブル列名
  data_type, -- 型名
  character_maximum_length, -- 長さ
  column_default -- 既定値
FROM information_schema.columns
WHERE table_schema = 'スキーマ名' AND table_name = 'テーブル名'
ORDER BY ordinal_position;
pg_attribute
SELECT
  attrelid::regclass, -- テーブル名
  attname, -- テーブル列名
  atttypid::regtype AS datatype, -- 型名
  information_schema._pg_char_max_length(atttypid, atttypmod) AS max_length, -- 長さ
  atthasdef
FROM
  pg_attribute
WHERE
  attrelid = 'テーブル名'::regclass
  AND attnum > 0 -- システム列以外
  AND NOT attisdropped -- 削除されていない列
  ORDER BY attnum

拡張機能の追加

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

メッセージの抑制

SET client_min_messages TO WARNING;

PSQL

SQL ファイルの実行

\i ファイル名
  • psql (\i or \include)

CSV インポート

CSV インポート (CSV が UTF-8 の場合)
\copy テーブル名 from 'ファイル名' with encoding 'utf8' csv;

クライアントエンコーディングの指定

\encoding UTF8;