はじめに
こんにちは、yk_itgです。
これまでいくつかPostgreSQLの記事を作成しましたが、今回は知っていると便利だと思うテーブル・DBの閲覧・コピーに関するtipsをまとめてみました。
私はテストを実施する時に結果を確認したり、データを用意する際によく使います。
PostgreSQLユーザなら必須のテクニックを紹介していきますので、どうぞお役立てください!
テーブルを閲覧する
テーブルの情報を確認したい:\d
\d {テーブル名}
をpsql上で実行するとテーブルの情報を表示することができます。
インデックスやシーケンスなどテーブル以外のリレーションを表示することも可能です。
postgres=# \d test テーブル "public.test" 列 | 型 | 照合順序 | Null 値を許容 | デフォルト ------+---------+----------+---------------+------------ id | integer | | not null | hoge | text | | | 'a'::text インデックス: "test_pkey" PRIMARY KEY, btree (id) "test_hoge_idx" btree (hoge)
postgres=# \d test_pkey インデックス "public.test_pkey" 列 | 型 | キー? | 定義 ----+---------+-------+------ id | integer | はい | id プライマリキー, btree, テーブル "public.test" 用
テーブルの一覧を確認したい:\dt
\dt
をpsql上で実行するとテーブルの一覧を表示することができます。
テーブル以外にも\di
、\ds
、\dv
を使えば、それぞれインデックス、シーケンス、ビューの一覧を表示することができます。
postgres=# \dt リレーション一覧 スキーマ | 名前 | 型 | 所有者 ----------+------+----------+---------- public | test | テーブル | postgres (1 行)
実行結果を見やすくしたい:\x
\x
をpsql上で実行するとSQLやメタコマンド等の実行結果を拡張表示(縦に表示)することができます。
例えば、カラムが多すぎてターミナルで折り返して表示されてしまう場合に便利です。
逆に見づらい場合には、もう一度\x
を実行すると元の表示に戻ります。
postgres=# SELECT * FROM test; id | hoge1 | hoge2 | hoge3 | hoge4 | hoge5 | hoge6 | hoge7 | hoge8 | hoge9 | hoge10 | hoge11 | hoge12 | hoge13 | hoge14 | hoge15 | hoge16 | hoge17 | hoge18 | hoge19 | hoge20 ----+------------+-------+-------+-------+-------+-------+-------+-------+-------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+-------- 1 | ABCDEFGHIJ | | | | | | | | | | | | | | | | | | | (1 行)
postgres=# \x 拡張表示は on です。 postgres=# SELECT * FROM test; -[ RECORD 1 ]------ id | 6 hoge1 | ABCDEFGHIJ hoge2 | hoge3 | hoge4 | hoge5 | hoge6 | hoge7 | hoge8 | hoge9 | hoge10 | hoge11 | hoge12 | hoge13 | hoge14 | hoge15 | hoge16 | hoge17 | hoge18 | hoge19 | hoge20 |
実行結果をファイルに出力して確認したい:\o
\o {ファイルパス}
をpsql上で実行すると実行結果を指定したファイルに出力することができます。
ターミナル以外で実行結果を確認したい場合や保存したい場合に便利です。
SQL実行後に\o
を実行すると出力先を標準出力に戻すことができます。
postgres=# \o result.txt postgres=# SELECT * FROM test; postgres=# \o postgres=# SELECT * FROM test; id | hoge ----+------------ 8 | ABCDEFGHIJ (1 行)
result.txt
id | hoge ----+------------ 8 | ABCDEFGHIJ (1 行)
DBを閲覧する
DBの一覧を確認したい:\l
\l
をpsql*1上で実行するとDBの一覧を表示することができます。
または psql -l
でターミナルを起動せずに表示することもできます。
postgres=# \l データベース一覧 名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限 -----------------------------------+----------+------------------+----------+-------------------+----------------------- postgres | postgres | UTF8 | C | C | template0 | postgres | UTF8 | C | C | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | C | C | =c/postgres + | | | | | postgres=CTc/postgres (3 行)
テーブルをコピーする
続いてはコピー作業のテクニック、まずはテーブル編の紹介です。
なお、これからご紹介する内容は、本番環境での利用は想定していません。
誤って重要なデータの書き替えが発生してしまう可能性もありますので、ご利用にはくれぐれもご注意ください。
テーブルの構造をコピーしたい:CREATE TABLE (LIKE)
CREATE TABLE {コピー先} (LIKE {コピー元} )
*2のSQLを実行するとコピー元と同じ構造のテーブルを作成することができます。
LIKEの中でINCLUDING ALL
を指定すると構造だけでなく、制約やデフォルト値、付与されているインデックス等もコピーすることができます。
postgres=# CREATE TABLE test2 (LIKE test); CREATE TABLE postgres=# \d test2 テーブル "public.test2" 列 | 型 | 照合順序 | Null 値を許容 | デフォルト ------+---------+----------+---------------+------------ id | integer | | not null | hoge | text | | |
postgres=# CREATE TABLE test3 (LIKE test INCLUDING ALL); CREATE TABLE postgres=# \d test3 テーブル "public.test3" 列 | 型 | 照合順序 | Null 値を許容 | デフォルト ------+---------+----------+---------------+------------ id | integer | | not null | hoge | text | | | 'a'::text インデックス: "test3_pkey" PRIMARY KEY, btree (id) "test3_hoge_idx" btree (hoge)
テーブルのレコードをコピーしたい:INSERT INTO SELECT
INSERT INTO {テーブル名} SELECT * FROM {テーブル名}
のSQLを実行するとSELECTの結果をそのままコピーすることができます。
そのままコピーすると主キー制約に引っかかる場合には、CREATE TABLE {コピー先} (LIKE {コピー元} )
を使ってtempテーブルを作成し、変更したいカラムのみをUPDATEしてからINSERTすると主キーのカラム以外を考えずにコピーすることができます。
postgres=# SELECT * FROM test2; id | hoge ----+------------ 10 | ABCDEFGHIJ (1 行) postgres=# INSERT INTO test2 SELECT * FROM test2; INSERT 0 1 postgres=# SELECT * FROM test2; id | hoge ----+------------ 10 | ABCDEFGHIJ 10 | ABCDEFGHIJ (2 行)
postgres=# SELECT * FROM test; id | hoge ----+------------ 1 | ABCDEFGHIJ (1 行) postgres=# CREATE TEMPORARY TABLE temp_test (LIKE test); CREATE TABLE postgres=# INSERT INTO temp_test SELECT * FROM test WHERE id = 1; INSERT 0 1 postgres=# UPDATE temp_test SET id = 2; UPDATE 1 postgres=# INSERT INTO test SELECT * FROM temp_test; INSERT 0 1 postgres=# SELECT * FROM test; id | hoge ----+------------ 1 | ABCDEFGHIJ 2 | ABCDEFGHIJ (2 行)
DBをコピーする
テーブルの次はDB編のご紹介です。
こちらもテーブル編と同様、本番環境での利用は想定したものではありません。
環境へ重大な影響を及ぼす内容となりますため、ご注意の上での利用をお願いします。
DBをコピーしたい:createdb -T
createdb -T {コピー元} {コピー先}
*3のコマンドを実行するとコピー元DBの内容でコピー先DBを作成することができます。
後述のpg_dumpと比較すると実行速度が速い印象です。
>psql -U postgres -d postgres postgres=# \dt リレーション一覧 スキーマ | 名前 | 型 | 所有者 ----------+-------+----------+---------- public | test | テーブル | postgres public | test2 | テーブル | postgres public | test3 | テーブル | postgres (3 行) >createdb -T postgres -U postgres postgres2 >psql -U postgres -d postgres2 postgres2=# \dt リレーション一覧 スキーマ | 名前 | 型 | 所有者 ----------+-------+----------+---------- public | test | テーブル | postgres public | test2 | テーブル | postgres public | test3 | テーブル | postgres (3 行)
DBをコピーしたい:pg_dump
pg_dump {コピー元DB} > {dumpファイル}
*4のコマンドを利用するとデータベースをバックアップするdumpファイルを作成することができます。
作成したdumpファイルをpsql {コピー先DB} < {dumpファイル}
でリストアすることによってコピー先のDBにコピー元のDBをデータを再現することができます。
前述のcreatedb -T
と比較すると、dumpファイルがあれば同じサーバにコピー元のDBがなくてもコピーできる点で汎用的です。
また、-n
や-t
のオプションを指定すると一致するスキーマやテーブル単位でコピーすることもできます。
> pg_dump -U postgres postgres > postgres_dump > ls -1 | grep postgres_dump postgres_dump > createdb -U postgres -T template0 postgres3 > psql -U postgres -d postgres3 < postgres_dump > psql -U postgres -d postgres3 postgres3=# \dt リレーション一覧 スキーマ | 名前 | 型 | 所有者 ----------+-------+----------+---------- public | test | テーブル | postgres public | test2 | テーブル | postgres public | test3 | テーブル | postgres (3 行)
最後に
PostgreSQLでのテーブル・DBの閲覧・コピーに関してご紹介してみましたが、いかがでしたでしょうか。
効率的な作業のお役に立てば幸いです。
関連記事
エンジニア中途採用サイト
ラクスでは、エンジニア・デザイナーの中途採用を積極的に行っております!
ご興味ありましたら是非ご確認をお願いします。
https://career-recruit.rakus.co.jp/career_engineer/カジュアル面談お申込みフォーム
どの職種に応募すれば良いかわからないという方は、カジュアル面談も随時行っております。
以下フォームよりお申込みください。
rakus.hubspotpagebuilder.comラクスDevelopers登録フォーム
https://career-recruit.rakus.co.jp/career_engineer/form_rakusdev/イベント情報
会社の雰囲気を知りたい方は、毎週開催しているイベントにご参加ください!
◆TECH PLAY
techplay.jp
◆connpass
rakus.connpass.com