MySQLユーザーがPostgreSQLを触ってみたメモ

最近なぜか MySQL を使う Ruby アプリを PostgreSQL に対応する羽目になっているのですが、今までほとんど MySQL 以外の RDBMS を触ってなかったので、色々ハマったりしたのでメモっときます。

なお PostgreSQL 歴が浅いので間違ってること書いてるかもしれません。

API

プログラムから MySQL にアクセスするには Ruby/MySQL を使っていたのですが、PostgreSQL 用の API を新たに覚えるのは面倒だったので、Sequel を使って書き直しました。

mysql.query("select col1, col2 from table where col3='xxx'")
↓
db[:table].where(col3: 'xxx').select(:col1, :col2)

…みたいな感じです。

今までプログラム中に突然 SQL が現れていて読みにくかったのが、Ruby プログラムとして読みやすくなるという効果もありました。

Sequel については前に記事を書いたので興味があれば見てください。

ユーザーとデータベース

MySQL はデータベースを指定せずにサーバーに接続することができます。その場合データベース未選択状態になります。接続後に use を使用して自由にデータベースを切り替えることができます。また、カレントのデータベースとは異なるデータベースのテーブルを指定することもできます。

PostgreSQL の場合はデータベースを指定しないで接続することはできません。psql コマンドはデータベースを省略すると、ユーザー名と同じ名前のデータベースに接続しようとします。また、接続後はデータベースを変更することはできません。psql の \c を使えばデータベースを変更できますが、これは内部的には新しいデータベースに接続して元のデータベースとの接続を切断しています。

Ubuntu の場合は postgres という名前のデータベースが最初から用意されています。 誰でも使用できるので MySQL の test データベースのようなものでしょうか。

ユーザー名と認証

MySQL では UNIX ドメインソケット経由での接続であってもパスワードは必要です。パスワードを設定しないこともできますが、その場合は誰でもそのユーザーでログインできてしまいます。また OS のユーザー名と MySQL 上のユーザー名はほとんど関係ありません。mysql コマンドでユーザー名省略時に OS のユーザー名が使用される程度です。

PostgreSQL の場合は UNIX ドメインソケット経由の場合は、OS のユーザー名と PostgreSQL 上のユーザー名が一致していればパスワードを聞かれません。また OS のユーザー名と異なるユーザー名を指定しても接続できません。ネットワーク経由の場合はユーザー名とパスワードが必要です。

Ubuntu の場合はあらかじめ postgres というユーザーがスーパーユーザーとして用意されています。MySQL の root と同じようなもんだと思います。

認証まわりの設定は pg_hba.conf ファイルで行います。どのクライアントからの接続で、どの認証方式を使用するかを指定することができます。

UNIX ドメインソケットの場合にパスワードを聞かれないのは、pg_hba.conf ファイル中に次の行があるためです。

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     peer

ユーザー作成

PostgreSQL に接続するユーザーを作るには、PostgreSQL のスーパーユーザーで createuser コマンドを実行します。

Ubuntu の場合は postgres ユーザーがスーパーユーザーです。

% sudo -u postgres -i
[sudo] password for tommy: 
postgres$ createuser -P hoge
Enter password for new role: 
Enter it again: 

この例では hoge ユーザーをパスワードつきで作成しています。 localhost からしか使用しない場合はパスワードをつけなくてもいいと思います。

データベース作成

PostgreSQL のスーパーユーザーで createdb コマンドを実行します。

postgres$ createdb hoge

接続

クライアントからの接続は MySQL に比べると PostgreSQL は重いです。

MySQL でコネクションプールを使わずに接続切断を繰り返してもなんとかなっていた場合でも、PostgreSQL ではコネクションプールを使わないといけなくなるかもしれません。

MySQL は1プロセスで動作し接続毎にスレッドを生成するのに対し、PostgreSQL は接続毎にプロセスを生成するためだと思います。

調べてませんが、もしかすると認証プロトコル自体の処理も関係あるのかもしれません。

自動変換等

MySQL は型が一致しなくてもテキトーに変換して処理してくれるのですが（かなり余計なお世話）、PostgreSQL は厳密に型をチェックするので、MySQL でエラーにならなかったクエリがエラーになることがあります。

以下は MySQL ではエラーになりませんが、PostgreSQL でエラーになる例です。

• 数値カラムへの文字列の登録

hoge=> insert into t (i) values ('hoge');
ERROR:  invalid input syntax for integer: "hoge"
LINE 1: insert into t (i) values ('hoge');
                                  ^

• 数値カラムと文字列の比較

hoge=> select * from t where i='hoge';
ERROR:  invalid input syntax for integer: "hoge"
LINE 1: select * from t where i='hoge';
                                ^

• 文字列カラムと数値の比較

hoge=> select * from t where s=123;
ERROR:  operator does not exist: character = integer
LINE 1: select * from t where s=123;
                               ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

• INSERT 時にデフォルト値を持たない NOT NULL カラムを省略

hoge=> insert into t (col1) values (123);
ERROR:  null value in column "col2" violates not-null constraint
DETAIL:  Failing row contains (123, null).

• SELECT に指定していないカラムを GROUP BY に指定

hoge=> select col1 from t group by col2;
ERROR:  column "t.col1" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select col1 from t group by col2;
               ^

• CHAR カラムにサイズ超過文字列を登録

hoge=> insert into t (col1) values ('0123456789a');
ERROR:  value too long for type character(10)

• DATE カラムに 0000-00-00 を登録

hoge=> insert into t (d) values ('0000-00-00');
ERROR:  date/time field value out of range: "0000-00-00"
LINE 1: insert into t (d) values ('0000-00-00');
                                  ^

• DATE カラムに不正な日付を登録

hoge=> insert into t (d) values ('2014-10-32');
ERROR:  date/time field value out of range: "2014-10-32"
LINE 1: insert into t (d) values ('2014-10-32');
                                  ^

• 0 除算

hoge=> select 1/0;
ERROR:  division by zero

MySQL でも sql_mode を設定することで、これらのうちのいくつかをエラーにすることができます。

mysql> set sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY';

なお、ERROR_FOR_DIVISION_BY_ZERO を指定することで、UPDATE,INSERT 時のゼロ除算はエラーになりますが、SELECT の時はエラーではなくワーニングになります。

UNSIGNED

MySQL の整数型には UNSIGNED を指定することができますが、PostgreSQL には UNSIGNED 型はありません。

大文字小文字

MySQL の文字列型はデフォルトでは大文字小文字を区別しませんが、PostgreSQL は大文字小文字を区別します。

PostgreSQL で大文字小文字を区別しないようにするには、citext 拡張をデータベースに導入して、文字列型カラム作成時に char, varchar ではなく citext を使うのが良いようです。

hoge=> create table t (s varchar);
CREATE TABLE
hoge=> insert into t (s) values ('abc');
INSERT 0 1
hoge=> select * from t where s='ABC';
 s 
---
(0 rows)

citext 拡張を導入するにはスーパーユーザーでないとできません。

hoge=# create extension citext;
CREATE EXTENSION

hoge=> create table t (s citext);
CREATE TABLE
hoge=> insert into t (s) values ('abc');
INSERT 0 1
hoge=> select * from t where s='ABC';
  s  
-----
 abc
(1 row)

LIKE も大文字小文字を区別します。大文字小文字を区別したくない場合は ILIKE を使います。

hoge=> select 'abc' like 'ABC';
 ?column? 
----------
 f
(1 row)

hoge=> select 'abc' ilike 'ABC';
 ?column? 
----------
 t
(1 row)

スキーマ

MySQL には無いものですが、PostgreSQL にはデータベースとテーブルの間にスキーマというものがあります。 データベース内でテーブルの名前空間をわけられるようです。 特に指定しない場合はデフォルトのスキーマが使われるので意識しなくても問題ないです。

MySQL のデータベースは PostgreSQL のデータベースとスキーマの両方が混ざったものなのかもしれません。

トランザクション

PostgreSQL はトランザクション中で何かエラーが発生した場合は、以降のクエリはロールバックするまですべてエラーになります。

この状態でコミットするとエラーにはなりませんが、実際にはロールバックされます。これはちょっと罠っぽいです。

hoge=> begin;
BEGIN
hoge=> insert into t (i) values (123);
INSERT 0 1
hoge=> hoge;
ERROR:  syntax error at or near "hoge"
LINE 1: hoge;
        ^
hoge=> select * from t;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
hoge=> commit;
ROLLBACK
hoge=> select * from t;
 i 
---
(0 rows)

MySQL と違い、PostgreSQL はトランザクション中で CREATE TABLE や DROP TABLE しても、ロールバックすると無かったことになります。これは結構嬉しいです。MySQL はトランザクション中で CREATE TABLE, DROP TABLE すると、その時点で勝手にコミットされてしまうという罠があるので…。

その他の構文

MySQL は文字列リテラルの表記は「'」でも「"」でも良いですが、PostgreSQL では「'」だけです。「"」はテーブルやカラムの識別子リテラルを表します(MySQL での「`」に相当)。

MySQL の「||」は論理和を表しますが、PostgreSQL では「||」は文字列結合です。

これらも MySQL 側で sql_mode を指定することで PostgreSQL に合わせることができますが、Sequel を使ってれば構文の差異はある程度吸収してくれるので、あまり気にしませんでした。

おわりに

はじめは「MySQLユーザーのためのPostgreSQLガイド」というタイトルにしようと思ったのですが、そんな大層なことは書けなかったので、「MySQLユーザーがPostgreSQLを触ってみたメモ」にしました。

MySQL と PostgreSQL は色々違いがありますが、構文まわりは MySQL の方が特殊だと思いました。勝手に余計な変換はしない方がみんな幸せになれると思います。

MySQL から PostgreSQL に移行するには、まず sql_mode を設定して、その状態で MySQL でエラーにならないようにしてから、PostgreSQL に移行するのがいいと思います。

自分の場合は、数値と文字列の比較とか、数値カラムに数字文字列入れようとしてたりとか、SELECT で指定してないカラムを GROUP BY に指定してたりとか色々ありました。

あと、最近 MySQL も sql_mode のデフォルト値を厳し目にするようになってるので、PostgreSQL とは関係なく、可能であれば厳し目に設定しておいた方が何かといいと思います。