PostgreSQL関連めものバックアップ(No.24)

文字コードを指定してテーブル作成
ユーザー一覧の表示
パスワード設定
CSVデータのデータ入出力
テーブル構造を確認
ファイルからテーブル作成
バックアップと復元
権限確認
ユーザーのロールを確認・変更する
psqlコマンドのみでSELECT結果をファイルへ出力
PRIMARY KEYの変更
NOT NULL制約の追加と削除
\dコマンド

接続中ユーザーの表示
実行されたSQL文を自動的に記録
シーケンス値の変更
特定のテーブルのレコード数を知る
並び替え時のNULL行の制御
コマンドラインでの「続ける(more)」表示をやめたい
現在時刻をUNIXTIMESTAMPで得る
PostgreSQLでの複数キーワード検索の方法

文字コードを指定してテーブル作成 †

createdb -U user -E UTF-8 -T template0 dbname

↑

ユーザー一覧の表示 †

psql# select usename from pg_user;

↑

パスワード設定 †

=# ALTER USER username PASSWORD 'password';

↑

CSVデータのデータ入出力 †

入力

psql# \copy table from ./data.csv

出力

psql# \copy table to ./data.csv

↑

テーブル構造を確認 †

psql# \d TableName

↑

ファイルからテーブル作成 †

$ psql -e -f ./maketable.sql

↑

バックアップと復元 †

↑

データベースまるごとバックアップ †

COPY形式でdump

$ pg_dump データベース名 > 任意のファイル名

INSERT形式でdump

$ pg_dump --inserts データベース名 > 任意のファイル名

↑

データを復元 †

$ createdb データベース名
$ psql -e データベース名 < 保存したファイル名

↑

テーブルをバックアップ †

$ pg_dump -t テーブル名 データベース名 > 任意のファイル名

↑

データのみバックアップ †

$ pg_dump -t テーブル名 データベース名 -a > 任意のファイル名

↑

テーブルデータを復元(テーブルはdropされている必要あり) †

$ psql -e データベース名 < 保存したファイル名

↑

データベースをコピー †

$ createdb -T ORIGINAL_DBNAME COPY_DBNAME

↑

権限確認 †

=> \z

↑

ユーザーのロールを確認・変更する †

$ psql template1

権限確認

# \du

DB作成権限を付与

# ALTER ROLE user WITH CREATEDB;

DB作成権限をはく奪

# ALTER ROLE user WITH NOCREATEDB;

↑

psqlコマンドのみでSELECT結果をファイルへ出力 †

db=> \o output.txt　　　[出力先をoutput.txtへ変更]
db=> \a　　　[桁揃えをやめ]
db=> \pset fieldsep '\t'　　　[デリミタをタブに変更]
db=> select * from table;　　　[SELECT文発行]
db=> \a　　　[桁揃えを戻す]
db=> \o　　　[出力先を戻す]

↑

PRIMARY KEYの変更 †

PRIMARY KEYの削除

ALTER TABLE table DROP CONSTRAINT table_pkey;

PRIMARY KEYの作成

ALTER TABLE table ADD CONSTRAINT table_pkey PRIMARY KEY (field1, field2);

↑

NOT NULL制約の追加と削除 †

追加

ALTER TABLE table ALTER COLUMN column SET NOT NULL;

削除

ALTER TABLE table ALTER COLUMN column DROP NOT NULL;

↑

\dコマンド †

テーブルの確認

\dt

テーブルとSEQUENCEの確認

\dts

SCHEMAの確認

\dn

SCHEMAに関するテーブルとSEQUENCEの確認

\dts SCHEMA.

ユーザー一覧

\du

コメントも表示

\d+

↑

接続中ユーザーの表示 †

=# SELECT * FROM pg_stat_activity;

実行中問い合わせ内容を見るには設定変更が必要

stats_command_string true

↑

実行されたSQL文を自動的に記録 †

# vi /usr/local/pgsql/data/postgresql.conf
log_statement = 'mod'
# /etc/rc.d/init.d/postgresql reload

none
記録しない
ddl
CREATE、ALTER、DROPなどのデータ定義文
mod
ddl + INSERT、UPDATE、DELETなどデータに変更を加えるもの
all
すべて

↑

シーケンス値の変更 †

次は101からカウントしたい場合

SELECT SETVAL('usr_seq', 100);

確認

SELECT last_value FROM usr_seq;

リセットする場合

SELECT SETVAL('usr_seq', 1, false);

↑

特定のテーブルのレコード数を知る †

レコード数が少ない場合は、以下で事足りる。

SELECT count(id) FROM TABLE;

ただし、レコード数が多いと極端に時間がかかるので、以下で代用できそう。

SELECT n_live_tup FROM pg_stat_user_tables WHERE relname = 'TABLE' and schemaname = 'public';

countでのレコード数 12947413、n_live_tupでのレコード数 12947459。
微妙に数が異なるけどおおよそでいいのでこれで行こう。

↑

並び替え時のNULL行の制御 †

ORDER BY id

id
---
0
0
0
1
1
1
NULL
NULL

のようになる場合、

ORDER BY id NULLS FIRST

とすると、

id
---
NULL
NULL
0
0
0
1
1
1

のように表示できます。

最後に表示したい場合は

ORDER BY id NULLS LAST

↑

コマンドラインでの「続ける(more)」表示をやめたい †

1つのフィールドに大量の文字列が詰め込まれていたりする場合はページ送りがとても面倒。
一気に表示されるように切り替えることができます。

=> \pset pager off

ページ送りにする場合は

\pset pager on

↑

現在時刻をUNIXTIMESTAMPで得る †

SELECT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0));
date_part
------------
 1509106662
(1 row)

↑

PostgreSQLでの複数キーワード検索の方法 †

http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q13115026123

PostgreSQL関連めも のバックアップ(No.24)