【PostgreSQL】psql で 変数 / 定数を使う

PostgreSQL

■ はじめに

例えば、開発などでマスターデータをDELETE&INSERTするのに
IDやコードを変数に置き換えて管理したい場合がある。
====
delete from sample_table where code = 【コード】;

-- 同じ値じゃないとダメ
insert into sample_table (code, name) VALUES (【コード】, 'Mike');
====

で、意外とはまったので、メモ。

■ psql の 変数

定義 

\set 変数名 値

【例】\set NAME Mike

参照 

:変数名

【例】:NAME

表示 

\echo :変数名

【例】\echo :NAME

■ 使用上の注意

以下のように書いて、ハマった
===
NG \set NAME 'Mike';
OK \set NAME Mike
===

セミコロン（;）もいらないし、
シングルクォーテーションマーク（'）もいらない

■ サンプル

準備

DDL 

DROP TABLE IF EXISTS position;
CREATE TABLE position
(
  position_code character(3) NOT NULL,
  name text,
  CONSTRAINT position_code_pkey PRIMARY KEY (position_code)
);

DROP TABLE IF EXISTS person;
CREATE TABLE person
(
  id character(8) NOT NULL,
  name text,
  position_code character(3),
  CONSTRAINT person_pkey PRIMARY KEY (id)
);

sample.bat (Windows用起動バッチ) 

# psql -f 【SQL文】 -U 【ユーザ】 -d 【DB名】 -h 【ホスト名】
psql -f ./demo.sql -U postgres -d sample_db -h localhost
pause

【任意】%APPDATA%\postgresql\pgpass.conf (Windows用パスワード省略設定) 

以下の関連記事を参照。

https://dk521123.hatenablog.com/entry/2020/03/06/000000

SQL文 ★今回のテーマ★

demo.sql 

-- \set 変数名 値
\set CODE P01
-- \echo 変数名
\echo :CODE

-- position
DELETE FROM position WHERE position_code = :'CODE';
INSERT INTO position(
  position_code, name)
VALUES (
  :'CODE', 'admin');
SELECT * FROM position WHERE position_code = :'CODE';

-- person
DELETE FROM person WHERE position_code = :'CODE';
INSERT INTO person(
  id, name, position_code)
VALUES (
  'X0000000', 'admin', :'CODE'),
 ('X0000001', 'Mike', :'CODE'),
 ('X0000002', 'Tom', :'CODE');
SELECT * FROM person WHERE position_code = :'CODE';

出力結果

psql -f ./demo.sql -U postgres -d sample_db -h localhost 
P01
DELETE 1
INSERT 0 1
 position_code | name  
---------------+-------
 P01           | admin
(1 行)

DELETE 3
INSERT 0 3
    id    | name  | position_code 
----------+-------+---------------
 X0000000 | admin | P01
 X0000001 | Mike  | P01
 X0000002 | Tom   | P01
(3 行)


pause
続行するには何かキーを押してください . . .

参考文献

https://qiita.com/noborus/items/f2954c4767ebcb5e52a4

関連記事

よく使う pdqlコマンド
https://dk521123.hatenablog.com/entry/2020/01/10/224818
PostgreSQLのDBをエクスポート/インポートするスクリプト
https://dk521123.hatenablog.com/entry/2018/03/02/232623
ファイルからSQL文を一括で実行するには ～pdqlコマンドの利用～
https://dk521123.hatenablog.com/entry/2014/09/11/002003
psqlでパスワードを省略する
https://dk521123.hatenablog.com/entry/2020/03/06/000000
psql コマンドでファイル実行した際に引数を渡すには
https://dk521123.hatenablog.com/entry/2021/08/01/000000