PostgreSQL

Computer 0 

（更新：2017/10）
（作成：2005/01）

PC U*IXで扱えるDBで、MySQLと並んでメジャーなのがこれ。話によるとMySQLより高機能だが、Windowsで使うにはCygwin経由でないとダメとか。
（2017/10追記）いつの間にかWindowsパッケージも出てたみたい。

設定

ネットワークアクセス

取り敢えず以下の設定を行なう。

#### /etc/postgresql/postgresql.conf

tcpip_socket = true
port = 5432

更にアクセスを許可したいIPとネットマスクを定義してやる。

#### /etc/postgresql/pg_hba.conf

local   all                                     ident sqmeuser
host    all     127.0.0.1       255.0.0.0       ident sameuser
# こんな感じで追加
host    all     192.168.1.0     255.255.255.0   password
host    all     0.0.0.0         0.0.0.0         reject

ここで指定した password は、下記にも書いてあるがPostgreSQL独自のパスワード管理を使う指定。 pam とかもあるが、どうもうまく動かないようなので。

初期化

ユーザレベルでDBを作成する前に、まずユーザにDB作成権限を与える必要がある。
権限を与える際、二つほど質問される。見れば判ると思うけど、一つ目はDB作成権限を与えるかどうか。二つ目は他にDB作成権限のあるユーザを作成する権限を与えるかどうか。
一つ目の項目は当然必要だが、二つ目の項目はつまるところ「自分自身が postgres ユーザと同等の権限を持つかどうか」という項目なので、あまりお勧めしない。

# su - postgres
$ createuser hoge
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER

データベース作成

DB作成権限を持ったら、次はDBを作成してみる。

$ createdb hoge
CREATE DATABASE

作りたての時点では当然内容は空。以下のようにして確認してみると良い。

$ psql diary
Welcome to psql x.x.x, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

diary=> \d
No relations found.

制限

パスワードとか。取り敢えずPostgreSQL独自のパスワード制限を掛けてみる方法。 DBにログインして、以下のようにすればそのDBにパスワード制限を掛けることができる。

db=> ALTER USER hoge WITH PASSWORD 'fugafuga';
ALTER USER
db=>

これでパスワードを設定できる。しかし、デフォルトだとパスワード制限を設定していないので、多分パスワードとか聞かれずにログインが出来ると思う。以下のように設定すると良い。

#### /etc/postgresql/pg_hba.conf

local   all                                     password
host    all     127.0.0.1       255.0.0.0       ident sameuser
host    all     0.0.0.0         0.0.0.0         reject

Tips

SELECT文WHERE節での検索について色々と。

ワイルドカードを用いる方法

  • 'ほげ' で始まる任意長の文字列にマッチさせる方法。
`SELECT * FROM hoge WHERE foo LIKE 'ほげ%';
  • 'ふが' で始まり任意一文字を持つ文字列にマッチさせる方法。
SELECT * FROM hoge WHERE foo LIKE 'ふが_';

正規表現を用いる方法

  • 正規表現で /^piyo.*/ を検索させる方法。
SELECT * FROM hoge WHERE foo ~ '^piyo.*';
  • 正規表現で /foo?/ 以外を検索させる方法。
SELECT * FROM hoge WHERE foo !~ 'foo?';
  • 正規表現で大小文字を区別せず /bar/ を検索させる方法。
SELECT * FROM hoge WHERE foo ~* 'bar';
  • 正規表現で大小文字を区別せず /?kee$/ 以外を検索させる方法。
SELECT * FROM hoge WHERE foo !~* '?kee$';

その他

  • Booleanフラグを比較する場合
SELECT * FROM hoge WHERE foo IS true
SELECT * FROM hoge WHERE foo IS NOT true