2011年5月31日火曜日

PostgreSQLのコマンドをメモ

// サービスの起動
$ /etc/init.d/postgresql start

// データベース一覧(postgresユーザー)
-bash-3.2$ psql -l

// データベースの作成(オプション:文字コードの指定)
$ createdb -U postgres -O postgres -E utf8 データベース名 

// データベースの作成
$ dropdb -U postgres -W (データベース名)
// データベースへ接続
$ psql -U (ユーザー名) -d (データベース名)

// DB情報を全てバックアップ
$ pg_dump (データベース名)> (バックアップファイル名.dmp)
$ pg_dump -U postgres (データベース名)> (バックアップファイル名.dmp)

※こんなエラーが出たら、
pg_dump: server version: SAGES/pg_dump.mo; pg_dump version: 7.2.3
pg_dump: aborting because of version mismatch (Use the -i option to proceed anyway.)
pg_dumpをフルパスで指定すればいい。
$ /usr/bin/pg_dump (データベース名)> (バックアップファイル名.dmp)

// バックアップをリストアする
$ psql (データベース名) < (バックアップファイル名.dmp)
$ psql -U postgres (データベース名) < (バックアップファイル名.dmp)


※postgresユーザーだとサービスの起動ができません。

PostgreSQLコマンド

# \l        データベース一覧
# \d        テーブル一覧
# \di        インデックス一覧
# \d テーブル名   指定したテーブルの項目一覧
# \dT        テーブルの型の一覧
# \dS        システム表の一覧
# \?        psqlコマンド一覧
# \q        psqlの終了
# \h        SQLコマンド一覧
# \h SQLコマンド 指定したSQLコマンドの使い方
# \i ファイル名 指定したファイル内のSQLを実行する
# \! OSコマンド OSのコマンドを実行する ]
# \timing      タイミングモード起動。SQLの実測値がわかる。

# CREATE user (ユーザー名) (パスワード); ユーザー追加(パスワードは未入力でも可能)
# DROP user ユーザー名; ユーザーの削除
# SELECT usename FROM pg_user; ユーザー一覧

※システムユーザー「nobody」とDBユーザー「nobody」は別人扱い。DBユーザー「nobody」はシステムユーザー「nobody」と違って初期状態では存在しません。

PostgreSQL文

// テーブル作成
CREATE TABLE test_tbl(
test_id int4 NOT NULL DEFAULT NEXTVAL('test_tbl_seq'::TEXT) primary key,
test_body varchar,
test_flg int NOT NULL
);

// シーケンス作成 ※1
CREATE SEQUENCE "test_tbl_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 2147483647
START 1
CACHE 1
;

// インデックス作成
CREATE INDEX IDX_test_tbl ON test_id(test_tbl);

// 権限を与える
GRANT ALL ON "test_tbl" TO "webmaseter";

// 全てのユーザーから権限を取り上げる
REVOKE ALL ON test_tbl FROM PUBLIC;

//既存テーブルのカラムにプライマリキーを付与する
ALTER TABLE テーブル名 ADD PRIMARY KEY (カラム名);

//シーケンス名の変更(ALTER SEQUENCE?)
ALTER TABLE シーケンス名 RENAME TO 新シーケンス名;

※1 テーブル作成時に serial型 を設定することで省略できる。

// テーブル作成(シーケンスを同時に作成する)
create table test_tbl(
test_id serial NOT NULL primary key,
test_body varchar,
test_flg int NOT NULL
);

// カラムの型を変更する
ALTER TABLE (テーブル名) ALTER (カラム名) TYPE (新規の型);