PostgreSQL・Tips集

　PostgreSQLデータベースを使う上で、便利な使い方をまとめてみました。

データベースの情報の参照方法

　psqlの機能や、システムテーブルを検索することで、いろいろなデータベースの情報を参照できます。

●表の一覧の表示

psql# \dt

●表の項目一覧の表示

psql# \d 表名

●インデックスの一覧の表示

psql# \di 表名

●ユーザ一覧の表示

psql# select usename from pg_user;

●データベース一覧と、データベースの文字コードの表示

psql# \l

検索開始位置と検索行数の指定方法

　PostgreSQLでは、複数行のデータを一括して検索できます。 PostgreSQL独自の機能ですが、簡単に使える上に、かなりの性能改善にもなります。

●カーソルを使わない場合

psql# select 項目名 from 表名 limit 検索行数 offset 検索開始位置;

●カーソルを使った場合

psql# declare カーソル名 cursor for select 項目名 from 表名;
psql# open カーソル名;
psql# move forward 検索開始位置 in カーソル名;
psql# fetch 検索行数 in カーソル名;
psql# close カーソル名;

日本語正規表現が使えるようにする方法と確認方法

　PostgreSQLは、日本語その他のマルチバイトコードに対応しています。データに日本語を使えるだけでなく、likeを使った条件指定による検索や、PostgreSQL特有の機能である正規表現を使った検索でも、日本語を使うことができます。
　ashのサーバにPostgreSQLをインストールしたところ、日本語のデータが登録でき、selectすると、ちゃんと日本語で表示されました。
　ところが、日本語正規表現を使った検索がうまくいきません。 substring()の結果も変です。日本語EUCコードでデータとしては登録できていますが、正しく日本語が扱えていないようでした。 （ashのサーバでは、日本語コードはEUC_JPです。）
　psqlコマンドで、データベースの一覧を表示させると、Encodingとして、データベースの文字コードが表示されます。

UNIX# psql -l

        List of databases
  Database  |  Owner   | Encoding
------------+----------+----------
 template1  | postgres | SQL_ASCII
 test       | postgres | SQL_ASCII
(2 rows)

　データベースの文字コードがEUC_JPになっていれば、SQL_ASCIIでなく、EUC_JPと表示されるはずです。これで、データベースで、日本語が使えるように設定されていていないことが確認されました。
　そこで、createdb時に文字コードを指定してみることにしました。

UNIX# createdb test2 -E EUC_JP

　こうやって作成したtest2は、"psql -l"で見ると、ちゃんとEncodingがEUC_JPと表示されます。
　pg_dumpで、testのバックアップを取り、test2に書き戻すことで、問題は解決しました。