PostgreSQL リハビリ

7.3以降、久しぶりにPostgreSQL案件に携わることになったので、リハビリのメモ。

  • PostgreSQL本体は、Windows版のバイナリパッケージでお気軽インストール。

    • Windowsサービスとしてインストールされます。
    • インストール中にデータベース・クラスタも作成されます。
    • ロケールは「C」にしておきます。
  • クライアントのpsqlはソースからCygwinでビルドしてみました。

    • 依存パッケージが複数あります。私は特に「readline」で突っ掛かりました。→ 「readline-devel」で解決。
    • psql.exe が生成されるので「alias psql='/usr/local/pgsql/bin/psql.exe'」とします。
$ tar zxvf ./postgresql-9.3.5.tar.gz
$ cd ./postgresql-9.3.5/

# 国際化対応(日本語)とマルチスレッド対応オプションを付けてコンパイル
$ ./configure --enable-nls --enable-thread-safety
$ cd ./src/bin
$ make

# crypt.c エラー: 不明な型名 ‘__int64’ です
→ 「#define B64 __int64」を「#define B64 long long」に変更しました。

$ make install
$ cd ../interfaces/libpq/
$ make install

接続確認

$ psql -d postgres -h localhost -p 5432 -U postgres
ユーザ postgres のパスワード:
postgres=# select version();
                           version
-------------------------------------------------------------
 PostgreSQL 9.3.5, compiled by Visual C++ build 1600, 64-bit
(1 行)

プロンプトを変更したり、Nullの表示を変更したり。

\set PROMPT1 '%n@%/%# '
\pset null '(NULL)'
  • psql終了 → メタコマンド「\q」
  • データベース一覧 → メタコマンド「\l」 : 初期状態では、postgres, template0, template1 のみ
  • ロール一覧 → メタコマンド「\du」 : 初期状態では、postgres のみ
  • テーブルスペース → メタコマンド「\db+」 : 初期状態では、pg_default, pg_global のみ
  • テーブル等の一覧 → メタコマンド「\dt」
  • テーブル定義を確認 → メタコマンド「\d テーブル名;」
  • スキーマ一覧 → メタコマンド「\dn」 : 初期状態では、public のみ

ロール管理

SQL# CREATE ROLE app_user WITH LOGIN PASSWORD 'xxxxx';
CREATE ROLE

SQL# DROP ROLE app_user;
DROP ROLE

SQL# CREATE USER app_user WITH PASSWORD 'xxxxx';
CREATE ROLE

テーブルスペースを作成

  • 作成したテーブルスペースにテーブルを作成 → CREATE TABLE ... TABLESPACE ...
-- Windowsでもパス区切りは「/」
SQL# CREATE TABLESPACE table_space_1 LOCATION 'c:/PostgreSQL/9.3/data';
CREATE TABLESPACE

SQL# \db+
                         テーブルスペース一覧
     名前      |  所有者  |          場所          | アクセス権 | 説明
---------------+----------+------------------------+------------+------
...
 table_space_1 | postgres | c:\PostgreSQL\9.3\data |            |

データベース作成

SQL# CREATE DATABASE example_db WITH OWNER app_user TABLESPACE table_space_1;
CREATE DATABASE

スキーマ作成

SQL> CREATE SCHEMA app;
CREATE SCHEMA

スキーマについて

SQL> SHOW search_path;
  search_path
----------------
 "$user",public
(1 行)

SQL> SET search_path TO app,public;
SET
  • スキーマ検索パス
    • 「SHOW search_path;」で確認できます
    • デフォルトでは、user名と同じ名前のスキーマ("$user")が最優先されます。
    • 「SET search_path TO xxxx,yyyy」で設定できます。(postgresql.confでも可)
    • カレントスキーマは「select current_schema();」で確認できます。CREATE TABLE でスキーマ名を指定しない場合はカレントスキーマにテーブルが作成されます。