文字コードを指定してテーブル作成 †
createdb -U user -E UTF-8 -T template0 dbname
ユーザー一覧の表示 †
psql# select usename from pg_user;
パスワード設定 †
=# ALTER USER username PASSWORD 'password';
CSVデータのデータ入出力 †
これが一番ラクかも
$ psql -U USER DATABASE -c "\COPY (SELECT * FROM table) TO '/table.csv' WITH CSV HEADER"
入力
psql# \copy table from ./data.csv
出力
psql# \copy table to ./data.csv
SQLを実行してファイルに出力
- c コマンド実行
- A 位置揃えをしない
- F 区切り文字
$ psql -U USER DATABASE -c "SELECT * FROM table" -A -F, > /table.csv
文字コードの変換は以下でできます。
UTF-8からShift_JISに変換する場合。
$ iconv -f UTF8 -t cp932 table.csv > table_sjis.csv
変換できない文字が含まれている場合は処理が止まってしまうので、止めずに最後まで走らせる場合は -c オプションを付けます。
$ iconv -c -f UTF8 -t cp932 table.csv > table_sjis.csv
改行コードは iconv では出来ないようなので、以下で行います。
LF => CRLF
$ unix2dos table_sjis.csv
CRLF => LF の場合は dos2unix を使用します。
入っていない場合は yum で入れます。
テーブル構造を確認 †
psql# \d TableName
ファイルからテーブル作成 †
$ psql -e -f ./maketable.sql
バックアップと復元 †
データベースまるごとバックアップ †
COPY形式でdump
$ pg_dump データベース名 > 任意のファイル名
INSERT形式でdump
$ pg_dump --inserts データベース名 > 任意のファイル名
データを復元 †
$ createdb データベース名 $ psql -v ON_ERROR_STOP=1 -h localhost -U DB_USER -d DB_NAME < dump.sql
テーブルをバックアップ †
$ 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 # vi /var/lib/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
GUIツール †
