PostgreSQL で to_dec

to_hex() の逆関数は標準では提供されていません。
PL/pgSQL が使える環境であれば「Conversion between hex and dec numbers(16進数と10進数の変換)」(SQL Tricks)のページで定義している to_dec() 関数を使うのが便利です。

=# select to_hex(11);
    to_hex
   ——–
    b
   (1 row)
postgres=# create or replace function to_dec(text)
           returns integer as $$
           declare r int;
           begin
             execute E’select ”||$1|| E’\’::integer’ into r;
             return r;
           end
           $$ language plpgsql;
CREATE FUNCTION
postgres=# select to_dec(‘ff’);
 to_dec


—-
    255
(1 row)

 
別の方法として、16進数を1度ビット型に変換することでも10進数に変換できます(桁数が分かっている場合のみ)。
たとえば、2桁の16進数文字列 の hextext というカラムを10進数に変換した居場合は次のようになります。

SELECT (‘x’ || hextext)::bit(8)::integer — bit(桁数の4倍)

PostgreSQL テーブル LOCK MODE 関係図

テーブルロックモードの関係を把握しやすいように図で表してみました。
PostgreSQL LOCK MODE 関係図
() 内はそのロックを自動的に獲得するクエリです。
 
■ロックとトランザクション
SQL における排他制御の方法として、トランザクションとロックが挙げられます。
複数のクエリをトランザクションでひとつにまとめることで、(デフォルトのトランザクションレベル READ COMMITTED の場合)コミット済みの結果のみ参照されるようになり、トランザクション中の内容を外部からは見えなくなるため一見アトミック性が守られるように見えます。
 
しかし、複数の接続で同時に既存の同じレコードに対して参照と更新を分けて実行する場合などはトランザクションだけでは限界があるのも事実です。
 
たとえば以下のテーブルを考えてみましょう。

CREATE TABLE foo (
  id int NOT NULL, — 商品の識別番号
  price int NOT NULL — 値段
);
INSERT INTO foo (id, price) VALUES(1, 100); — id:1 は 100 円
INSERT INTO foo (id, price) VALUES(2, 500); — id:2 は 500 円

このテーブルに対して、以下の処理を行うものとします。

BEGIN;
SELECT id FROM foo WHERE price < 500; -- 500円未満の id:1 を得る
UPDATE foo SET price = price + 500 WHERE id = 1; — 500円未満だった id:1 を 500円増し
COMMIT;

READ COMMITED レベルで同時に同じのクエリが実行されると、id:1 が 1100 円になる可能性があります。
 
トランザクションのみでこれを防ぐには衝突する可能性のある全てのクエリのトランザクションレベルを最も厳密な SERIALIZABLE にする必要があります。

— 衝突回避版
BEGIN;
SET TRANSACTION SERIALIZABLE; — この接続のトランザクションレベルを SERIALIZABLE に。
SELECT id FROM foo WHERE price < 500; -- 500円未満の id:1 を得る
UPDATE foo SET price = price + 500 WHERE id = 1; — 500円未満だった id:1 を 500円増し
COMMIT;

ただし、 SERIALIZABLE 隔離レベルのトランザクションの実行中に他の接続で COMMIT された場合、直列性を守るためにエラーになり処理が続行できなくなります。このため、衝突した場合は ROLLBACK の後再処理を行う必要があるなど, 欠点もあります。トランザクションのみでは、参照を完全にブロックすることができないのが原因のひとつです。
 
そこで、読み込みも制御できるロックの出番というわけです。
 
■ロックの獲得方法
多くのクエリは暗黙にクエリ単位で行レベルロックを獲得しています。
ロックの種類と影響する範囲については図を参照してください。
 
複数のクエリでロックを獲得するには LOCK 構文を用います。
LOCK 構文で取得できるロックはテーブルに対するロックのみです。

BEGIN;
LOCK foo_table IN EXCLUSIVE MODE; — EXCLUSIVE ロック獲得。
SELECT * FROM foo_table;
UPDATE foo_table SET …
— その他何か色々なクエリ…
COMMIT;

たとえば EXCLUSIVE モードでロックを獲得すると、他の接続では SELECT, ANALYZE 文以外の全てのクエリ、および ACCESS_SHARE 以外の全てのロックを明示的に獲得しようとするとロック待ちになります。
※具体的には UPDATE, DELETE, INSERT 文および SELECT … FROM table FOR …, CREATE INDEX, ALTER TABLE, DROP TABLE, REINDEX, CLUSTER, VACUUM, VACUUM FULL 文の実行を防ぎます。
また、ACCESS EXCLUSIVE, EXCLUSIVE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, SHARE の明示ロック獲得を防ぎます。
 
■ロックの解放方法
ロックはトランザクションの終了時(COMMIT, ROLLBACK実行時)に自動で解放されます。
トランザクションの途中でロックだけを解放する方法はありません。
 
参考:
トランザクションの隔離(PostgreSQL 8.3.1 マニュアル)
LOCK 構文(同上)
トランザクションと隔離レベルとロック(ファイヤープロジェクト)

NULL は「値がない」のではなく「値が不明」

SQL では NULL を含む演算は IS NULL, IS NOT NULL と一部の論理演算を除き常に戻り値が NULL になります。
この結果として、NULL を値に持つレコードを IS NULL 以外で抽出できなくなります。
たとえば極端な例ですが

SELECT NULL != NULL

が true にならず NULL になったりします(つまり (NULL != NULL) IS NULL が真になる)。
 
この仕様は Java や PHP などのプログラミング言語から考えると不可解なものにうつりますが、 NULL は値が存在しないのではなくまだわかっていない(unknown)だけなのだ、と考えるとスッキリします。
 
値段のカラムに null を持つ商品について考えるとき、その商品は「無料」ではなく値段がまだ「決まっていない」だけ。携帯のメールアドレスが null のユーザがいたなら、その人は携帯電話を「持っていない」という意味ではなく、その人が携帯電話を持っているか「わからない」だけです。
 
指定した部署(department_id)以外にいる社員(employee)の名前を探す下記のようなクエリを実行しても、部署が null の社員の名前は含まれません。その社員が 1 の部署に配属されるかもしれないからです。

SELECT name FROM employee WHERE department_id != 1

null 値も結果に含めたい場合は下記のようにしなければなりません。

SELECT name FROM employee WHERE department_id != 1 AND department_id IS NULL

 
0 や に包括的なからっぽ(empty)という意味を持たせたるのも伝統的でいいですが、SQL では 0, と NULL を区別して有効に使うほうがよさそうです。
 
参考:
3値論理とNULL(CodeZine)

SQL の基礎 – JOIN (表結合)の意味の違い

SQL 標準では JOIN 句による結合構文は次のような種類があります。

INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
CROSS JOIN

LEFT JOIN, RIGHT JOIN など、よく使われる構文は上記の省略形です。

・ただの JOIN は INNER JOIN の省略形。
・LEFT JOIN は LEFT OUTER JOIN の省略形。
・RIGHT JOIN は RIGHT OUTER JOIN の省略形。

それぞれの JOIN 構文の意味は次のとおりです。

 

INNER JOIN: 内部結合

指定したカラムについて同じ値を持つレコード同士を結びつける。
内積なので指定したカラムの値がどちらかにしかないレコードについては結果に含まれません。

SELECT * FROM table1 INNER JOIN table2 USING(id)

は、次の SQL 文と同じ結果になります。

SELECT * FROM table1, table2 WHERE table1.id = table2.id

 

LEFT OUTER JOIN: 外部結合

左のテーブルを基準にして、指定したカラムについて同じ値を持つレコード同士を結びつける。
値が右のテーブルにあり左のテーブルにない場合は INNER JOIN 同様結果に含まれませんが、
値が左のテーブルにあり右のテーブルにない場合は INNER JOIN と異なり 右のテーブルのカラムには全て NULL がパディングされ、結果に含まれます。
 
このため、

SELECT * FROM table1 LEFT OUTER JOIN table2 USING(id) WHERE table2.id IS NOT NULL

は、

SELECT * FROM table1 INNER JOIN table2 USING(id)

と同じ結果になります。
 

RIGHT OUTER JOIN: 外部結合

LEFT OUTER JOIN の左右の意味を入れ換えたもの。

SELECT * FROM table1 RIGHT OUTER JOIN table2 USING(id)

SELECT * FROM table2 LEFT OUTER JOIN table1 USING(id)

と同じ結果になります。
 

CROSS JOIN: 交差結合

ON, USING で条件を指定しない場合、左右のテーブルの直積を返します。
つまり、左右のテーブルにそれぞれ2行のレコード、3行のレコードが格納されている場合、結合結果は全てのレコードの組合せになり合計 2×3=6 行のレコードになります。
PostgreSQL や MySQL では ON, USING を指定すると INNER JOIN と等価になります。

SELECT * FROM table1 CROSS JOIN table2

は次の SQL 文と等価です。

SELECT * FROM table1, table2

また、PostgreSQL, MySQL では

SELECT * FROM table1 CROSS JOIN table2 USING(id)

は次の SQL 文と等価です。

SELECT * FROM table1 INNER JOIN table2 USING(id)

 

■ON, USING, NATURALによる結合条件指定

CROSS JOIN をのぞき、JOIN 句には ON, USING, NATURAL のいずれかを指定します。

INNER JOIN … ON 結合条件
INNER JOIN … USING(結合カラム, … )
NATURAL INNER JOIN …

 

ON:

WHERE 句と同じ書式で結合条件を絞り込む。
書式:

SELECT … FROM table1 JOIN table2 ON 条件式

table1, table2 のidが同じカラム同士を結合したい場合は次のようにします。

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id

AND, OR など複雑な条件で絞り込む事も可能です。

SELECT * FROM table1 AS t1
  LEFT JOIN table2 AS t2 ON (
    (t1.id = t2.id OR t1.name = t2.name)
    AND t1.salary >= t2.age * 1000
  )

ON に指定する結合条件には、 WHERE 同様どんな条件式でも指定することが可能ですが、テーブル結合に関係のない条件式はWHERE句に書くようにしましょう。
 

USING:

カラム名で結合条件を絞り込む。
書式:

SELECT … FROM table1 JOIN table2 USING(カラム1 , カラム2, …)

両方のテーブルに同じカラム名がある場合に有効です。

SELECT * FROM table1 LEFT JOIN table2 USING(id)

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id

と同等です。複数指定した場合は全てのカラムの値が一致するレコードを結合します。
 

NATURAL:

両方のテーブルを比較して名前が同じ全てのカラムを結合条件にして絞り込む。
書式:

SELECT … FROM table1 NATURAL JOIN table2

USING に両テーブルの共通カラムを全て指定したものと同じです。すなわち、

SELECT * FROM table1 NATURAL LEFT JOIN table2

は、両方のテーブルに id, name, age が存在する場合、

SELECT * FROM table1 LEFT JOIN table2 USING (id, name, age)

を指定したのと同等の結果になります。
 

■複数のJOIN句を組み合わせる

3つ以上のテーブルを複数のJOIN 句で結合する事もできます。
例として、table1, table2. table3 全てのテーブルに同じidが存在するレコードのみ抽出する場合は次のようになります。

SELECT * FROM table1
  INNER JOIN table2 USING(id)
  INNER JOIN table3 USING(id)

複数のJOIN を組み合わせる時の結合順序は左から順に評価されます。結合順序を制御したい場合、カッコが使用できます。

SELECT * FROM table1 AS t1
  LEFT JOIN ( table2 AS t2 INNER JOIN table3 USING(id)) ON t1.value = t2.tag
)

この例では table2, table3の同じidのレコードをINNER JOIN 句を使い結合し、その後 table1 と LEFT JOIN 句で結合されます。
 

参考:

SELECT(PostgreSQL 8.2.5 マニュアル)
MySQL 5.1 リファレンスマニュアル 12.2.7.1 JOIN 構文(MySQL AB)
特殊な結合演算子 SQL(TELCHSCORE by 4DD)
SQL92

テーブルのディスク使用量を取得する

SELECT relname, relfilenode, relpages FROM pg_class;

relname がテーブル名、relfilenode が実体ファイル名、 relpages が利用しているページ数概算。1ページは通常 8KB です。
 

SELECT c2.relname, c2.relpages
    FROM pg_class c, pg_class c2, pg_index i
    WHERE c.oid = i.indrelid
        AND c2.oid = i.indexrelid
    ORDER BY relpages DESC;

同様にインデックスの利用しているページ数を取得します。
 
参考:
ディスク使用量の監視(PostgreSQL 8.2.5 公式マニュアル)
pg_class(同上)

PostgreSQL で使える全文検索エンジン

Ludia
wiki: http://ludia.sourceforge.jp/moin.cgi/#id9
 
内部で Senna と MeCab が使われています。
 
PostgreSQL に Ludia をインストールすると、Senna の構文での文字列検索を行える @@ 演算子等、全文検索用の機能が追加されます。

SELECT content FROM fooTable WHERE content @@ ‘今日は+です’

結果:

| content |
| 今日はくもりです |
| 今日はいい天気ですね。|

タイムスタンプでマイクロ秒ではなく整数秒を利用する

PostgreSQL では

SELECT CURRENT_TIMESTAMP
SELECT now()

などとすると、

2006-08-02 22:14:42.642554

のように、小数点以下の秒数まで表示してくれます。
 
これはこれでいいのですが、小数点秒を扱う必要のないケースもあります。
 
その場合は、timestamp(0) 型として扱うことで小数点以下を四捨五入します。

SELECT CAST(CURRENT_TIMESTAMP AS timestamp(0))

のようにすると、先程の結果は次のようになります。

2006-08-02 22:14:43

 
テーブルの場合も、

CREATE TABLE foo (
  created_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

から

CREATE TABLE foo (
   created_on timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP
);

のようにすることで、整数秒として保存するようになります。

トランザクションでロールバックされない処理

BEGIN
— 任意の処理 ..(A)
ROLLBACK

とすれば、(A)で何をしてもロールバックしてくれるものだと思っていたらそれは勘違いのようです。
 
たとえばシーケンスなど

create sequence seq;
 
BEGIN;
SELECT nextval(‘seq’); — (B)
SELECT nextval(‘seq’); — (C)
ROLLBACK;
SELECT nextval(‘seq’); — (D)

としても (B) == (D) にはならず、BEGIN, ROLLBACK で囲まなかった場合と同じになります。
 
PGSQL-JP ML によると
http://www.drive.ne.jp/iMA/showmail.pl?pgsql-jp=3098

 

素人的な考えだと、transaction 内での処理なんだから、値を戻して
くれてもよさそうなものなのに、と思うのですが。。

#Oracle でも sequence は戻らないと聞いたので、出来ないのが普通
#なのかなあ?

 
他にも戻らないものとしては、
create table
create index
create sequence
があります(他にもあるかも)。商用DBでも多分同じです。

公式 PGSQL ML の引用

It’s normal behaviour.
 
Sequences are locked only for duration of nextval call, not for
transaction! So, one sequence may be used simultaneously
by many transactions. No way to rollback sequence changes.
 
意訳: シーケンスのロックは nextval 呼び出しについてのロックであり、トランザクションのためのロックではありません。一つのシーケンスが同時に複数のトランザクションで利用されうるため、シーケンスの変更をロールバックする方法は存在しません。

ということですが、単純に、一つのテーブルで serial (bigserial)として利用していて、他で nextval が呼ばれないことをシステム的に保証していれば、テーブルロックを使い、ROLLBACK 時にシーケンスを元に戻してやれば、一応実現は可能かなと思います。

最後に挿入したクエリの ID を取得する

PostgreSQL 8.1 になって、
MySQL における LAST_INSERT_ID() に似た機能をもつ、LastVal() がサポートされるようになりました。

MySQL:
CREATE TABLE employee (
  id int unsigned auto_increment not null primary key,
  name varchar(128) not null
);
 
INSERT INTO employee (name) VALUES (‘John’);
SELECT LAST_INSERT_ID();

と、次のものは全く同等の意味になります。

PostgreSQL:
CREATE TABLE employee (
  id serial not null primary key,
  name varchar(128) not null
);
 
INSERT INTO employee (name) VALUES (‘John’);
SELECT LASTVAL();

 
LASTVAL() はシーケンスのインクリメント関数である NEXTVAL() が一番最後に返した値を返すものです。
 
PostgreSQL の LASTVAL() は MySQL の LAST_INSERT_ID() と同様に、
他のセッションでの NEXTVAL()の影響は受けません。
 
一度も自分のセッションで NEXTVAL() を呼び出していない場合は、”ERROR: lastval is not yet defined in this session”というエラーが発生します。
 
参考:
PostgreSQL を MySQL 互換にする(Open Tech Press)
シーケンス操作関数(PostgreSQL 8.1.0 日本語ドキュメント)