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 (新規の型);