PostgreSQLのデータを操作する

PostgreSQL		PostgreSQLのデータを操作する
		2022/10/24

PostgreSQLのデータを操作する

●psqlを使ってみる
PostgreSQLに標準でついているクライアントアプリケーション(データベース操作ツール)は、 psqlです。 mydbデータベースにdev1ユーザーで接続するコマンドは次のようになります。

% psql -U dev1 -d mydb

パスワード認証を設定してあればここで聞いてくるはずです。その後、対話型の入力画面になるので、適当にSQL文を打って、結果が出れば大成功です。
なお、psqlにオプションの付かない引数を指定すると、それはデータベース名と解釈されます。つまり「-d mydb」と「mydb」は全く同じ意味になります。
最初に基本的なpsqlコマンドを覚えておきましょう。

コマンド	意味
\q	終了する
\i {file}	SQL文を{file}から読み込む
\d {table}	テーブル{table}の定義を表示する
\l	データベースの一覧を表示
\dt	テーブルの一覧を表示
\dn	スキーマの一覧を表示
\df	ファンクションの一覧を表示
\dv	ビューの一覧を表示
\du	ロールの一覧を表示

一連のSQL文をファイルに書いておいて、psqlに一気に実行させるには、上記の\iコマンド以外に、リダイレクトで標準入力から流し込むこともできます。たとえばこんなファイルを作り、items.sql という名前でセーブしておいて

/*  items.sql */
create table dev1.items (
  item_id     int not null,
  category_id int,
  item_name   varchar,
  unit_price  numeric,
  udate       timestamp,
  constraint  items_pk primary key(item_id)
);
// end.

次のコマンドで実行できます。

% psql -U dev1 -d mydb < items.sql

SQL文のファイルのコメントにはC言語型、C++型、Oracle型(/* ～ */ 、 //、--)が使えます。データ型にはOracleでポピュラーな VARCHAR2 と NUMBERがありませんが、それぞれ VARCHAR か TEXT、INTEGER かその省略形のINT、DECIMAL、NUMERIC などが使えます。一点大きな注意として、DATEという型がPostgreSQLにもありますが、 OracleのDATEと異なり、PostgreSQLのDATEは本当に日付しか持てません。時刻以下の情報を格納するには、DATEではなくTIMESTAMPというデータ型を使う必要があります。

ではさっそく何かデータを入れてみます。上のテーブル items はムッチャ適当なサンプルですが、

insert into items(item_id, category_id, item_name, unit_price, udate)
values(123, 1, 'みかん', 100, CURRENT_TIMESTAMP);

select count(*) from items;

こんな感じで、SQL文の最後には忘れずにセミコロンをつける必要があります(OracleのSQL*Plusと同じですね)。
CURRENT_TIMESTAMPはOracleで言うSYSDATEです。

なお対話画面で日本語を打つのが難しい環境の場合は、データベース作成時に指定した日本語コード(環境変数PGCLIENTENCODINGがセットしてあれば、そのコード) で日本語テキストエディタでSQL文のファイルを作って実行すればOKです。

また、psqlはSQL*Plusと違い、デフォルトでオートコミットです。つまりDML文は1文発行するごとに確定され、COMMIT/ROLLBACK文は不要です。現在のモードは

\echo :AUTOCOMMIT
on

で確認できます。

\set AUTOCOMMIT off

で変更できます。

●パスワードの自動入力
バッチ処理などでパスワードを対話的に尋ねられずに実行したい場合は、環境変数PGPASSWORDに設定してからpsqlコマンドを実行すればOKです。

% export PGPASSWORD=temppass
% psql -U dev1 -d mydb

●スキーマの検索パス
itemsテーブルをdev1スキーマ内に作りたい場合は、CREATE TABLE文でdev1.itemsと指定します。
このテーブルに対してSELECT文やINSERT文を発行したいときも、スキーマのdev1.は毎回付ける必要があるのでしょうか？
いいえ、「検索パス」という仕組みがあり、スキーマ名が省略されたオブジェクトはデフォルトで (1) DBユーザーと同名のスキーマ ⇒(2) public スキーマの順に検索されます。
つまり、dev1ユーザーが単に SELECT * FROM items などとしたときは、dev1というスキーマにitemsというテーブルがあればそれが、なければpublicスキーマにitemsがあればそれが参照されます。どちらにもなければエラーです。
検索パスは明示的に変更もできます。詳細は追って。

●テキストファイルからデータを入力する
OracleのSQL*Loaderのように、テキストファイルからPostgreSQLのデータベースにデータをロードすることもできます。 PostgreSQLでのやり方は、コントロールファイルなどを用意しないといけないSQL*Loaderよりずっと簡単で、単に

234,1,りんご,200,2022-09-20
235,1,いちご,400,2022-09-20
236,1,メロン,1200,2022-09-20

このようにデータをカンマで区切ったテキストファイル(ここでは items.dat)を用意しておき、psqlの対話画面で

mydb=> \copy items FROM items.dat WITH CSV

このように \copy コマンドで取り込むことができます。
ちなみに「WITH」は省略できます。
また上の例のように、日付データの書式のデフォルトはYYYY-MM-DDです。
また、末尾にHEADERを付けると、データファイルの1行目を見出し行とみなして読み飛ばします。
また、区切り文字がタブの場合は、次のようにします。

mydb=> \copy items FROM items.dat WITH CSV DELIMITER E'\t'

また、PostgreSQLではOracleと異なり、NULLと空文字列は別物ですが、 CSVモードでデータをロードするときは、空文字列のフィールドはNULLとしてデータにロードされます。これを、例えばデータファイル中で値が「@」をNULLとみなす場合は、下の例のようにNULL AS句を使えばOKです。

mydb=> \copy items FROM items.dat WITH CSV NULL AS '@'

●pg_dumpによるバックアップと復元
バックアップ方法は何種類かあります。一般的なコールドバックアップ(つまりバックアップを実行したタイミングまで復旧できるバックアップ)は、 pg_dumpでデータベースごとに取得できます。

pg_dump mydb > mydb.dmp

このコマンドで出力されるダンプファイルは、復旧データを保持したテキストファイルです。従って復元処理はこのファイルをpsqlコマンドでデータベースに流し込めば可能です。

psql mydb < mydb.dmp

		もくじ
PostgreSQL		(C) 2002-2023 MISUMI URANO (2000/01/12 - 2022/10/24)