memo-space

深い意味はありませんが、こっそりPostgreSQLグループ作りました。 今後PostgreSQL方面の話題は、 iakioの日記 - postgresqlグループ で。

SQLパズル：NULLの埋め立て - ミックのブログ

limitは使っていいのかな。

Continue reading

そういう時はdeleteではなくtruncateを使う方法もあります。 truncateはいきなりテーブルを(不要領域を残すことなく)空っぽにするSQLです。

• PostgreSQL VACUUM FULLせずに不要領域を削除する | Shin x blog

Continue reading

書こう書こうと思いながら3ヶ月くらい放置していたネタです。 巷ではMySQL Proxyが話題のようなので、 あえて今年のPostgreSQL ConferenceのJoshのプレゼンでもちょっと出ていたPL/Proxyをいじってみました。

• spiritlooseのはてなダイアリー - MySQL Proxyがおもしろそう
• livedoor Developers Blog:MySQL Proxy を試してみました - livedoor Blog（ブログ）

Continue reading

色々考えたけど、まあ許せるのはこのくらいか。

-- スタンダードに
select case
  when i%15=0 then 'FizzBuzz'
  when i%5=0 then 'Buzz'
  when i%3=0 then 'Fizz'
  else i::text end
  from generate_series(1,100)as s(i);


-- ちょっとアグレッシブに(8.2以降)
select coalesce(v15.t, v3.t, v5.t, s.i::text)
  from generate_series(1,100)as s(i)
  left join(
       values(0,'FizzBuzz')) as v15(i,t) on (s.i%15=v15.i)
  left join(
       values(0,'Fizz')) as v3(i,t) on (s.i%3=v3.i)
  left join(
       values(0,'Buzz')) as v5(i,t) on (s.i%5=v5.i)

あ、valuesじゃなくて普通にサブクエリでいいのか。まあvaluesって書いてみたかっただけです。

添削するほどエロくないですが。

元ネタ:タグを実現するテーブル設計を妄想する - よくきたblog

まず、

SELECT COUNT(*) FROM (SELECT user_id FROM item_tags WHERE item_id = 1 GROUP BY user_id) AS rows;

は、

SELECT COUNT(DISTINCT user_id) FROM item_tags WHERE item_id = 1;

でいいです。

Continue reading

ミックさんの問題に挑戦してみました。 case文を使う方が先に思いついたけど。こんな感じかな。 ヒラをどこで弾くかが微妙なところですが。

方法はいくつかありますが、代表的なものとしては、HAVINGを使ってUNIONする非効率的なものと、CASE式を使った効率的なものがあります。それぞれどんなクエリになるか、考えてみてください。

Yahoo!ジオシティーズ - ミックのブログ

Continue reading

PostgreSQLのinet型を使って、IPアドレスから携帯のキャリアを判定してみます。

とりあえずIPアドレスの情報源。

• 作ろうiモードコンテンツ：iモードセンタの各種情報 | サービス・機能 | NTTドコモ
• KDDI au: 技術情報 > IPアドレス帯域
• IPアドレス帯域について:ソフトバンク

Continue reading

PostgreSQLでは、SERIAL型とはsequenceをデフォルト値とするinteger型で、SERIAL型の列を持つテーブルを作成すると自動的にsequenceが作られます。 またこの時のsequenceの名前は、{表名}_{列名)_seqとなります。

なんだけど表や列をRENAMEした場合にsequenceの名前もあわせて変わるわけではないので、フレームワークなんかでこの命名規則に依存していると悲しい思いをすることがあります。

Continue reading

PostgreSQL日本語ドキュメントを見ていて、あれ？これって原文どうなってるのかな？とか思った時に、原文の同じページに移動するためのブックマークレットを作ってみた。

下のリンクをブックマークツールバーか何かにドラック&ドロップして下さい。

• pgdoc_j2e

• pgdoc_e2j

バージョンが上がった場合は直さなきゃだめだけど。

PostgreSQLでUnicodeの正規化ができればいいなぁと思ってたんだけど、調べてみると自分でCで書くのはかなり辛そう。

PerlにはUnicode::Normalizeという実装があるので、これをplperlから利用してみた。(正確にはplperlu)。

Unicodeの正規化についてはこの辺を。

Unicode正規化

daily dayflower - Unicode::Normalize で遊ぶ

    utf8db=# create or replace function nfkc(text) returns text as 
        $$use Unicode::Normalize; return Unicode::Normalize::NFKC($_[0]);$$ language plperlu;
    CREATE FUNCTION

    utf8db=# select nfkc('ＡＢＣ');
     nfkc
    ------
     ABC
    (1 row)

    utf8db=# select nfkc('イ

思いがけず、GiSTって何のことなのかわかってしまった気がするのでメモしておく。

GiST(Generalized Search Tree)とは、PostgreSQL で使えるインデックスの種類のひとつなんだけど、それを理解するには R-Tree を知っておいたほうがいい。もちろん B-Tree も知ってた方がいい。 適当に検索してみてください。図を見ればなんとなくわかると思います。

B-Tree は値の大小でグループに分けて、ツリーを構成していて、R-Tree は平面上のある領域内に含まれるかどうかでグループ分けをして、ツリーを構成している。

つまり、何かしらグループ分けする基準さえあれば、何かしらのツリーが構成できる、というのが GiST らしい。

と考えると、ちんぷんかんぷんだった GiST の説明もちょっと見えてくる。

GiSTインデックス:実装

consistent というのは、そのグループ(ノード)に含まれるかどうかの検査で、union はノードの結合、みたいな話だと思う。

だいたい同じことが

GiST - Introduction

にも書いてあるのでコッチを見たほうがいいかもしれない。

contrib/ltree が面白そうなので今度書くかも。

Google Code Search でちょっと遊んでみた。psycopg とか PyGresql はもうちゃんと PQescapeStringConn を使ってるみたい。

PostgreSQL にしか無いような関数名を検索していたら、Berkeley 時代の Postgres のソースを発見した。Berkeley にあった(あたりまえ)。

University POSTGRES 4.2

db.cs.berkely.edu のマスコットもカメなのかな。

PostgreSQL で ./configure --help すると、

Some influential environment variables:
  CC          C compiler command
  CFLAGS      C compiler flags
  LDFLAGS     linker flags, e.g. -L<lib dir> if you have libraries in a
          nonstandard directory <lib dir>
  CPPFLAGS    C/C++ preprocessor flags, e.g. -I<include dir> if you have
          headers in a nonstandard directory <include dir>
  CPP         C preprocessor
  LDFLAGS_SL
  DOCBOOKSTYLE
          location of DocBook stylesheets

Use these variables to override the choices made by `configure' or to help
it to find libraries and programs with nonstandard names/locations.

と出てくるわけだけど、全てが上手く動作するわけではないらしい。

src/template/win32 なんかは LDFLAGS を思いっきり上書きしちゃってるじゃないか。

$ cat src/template/win32
# This is required to link pg_dump because it finds pg_toupper() in
# libpq and pgport
LDFLAGS="-Wl,--allow-multiple-definition"

で、-L を追加したい場合は、LDFLAGS ではなく --with-libs=DIRS を、-I を追加したいときは CPPFLAGS ではなく --with-includes=DIRS を使った方が安全。

ちなみに src/template/* が CFLAGS を上書きしていたとしても、configure の中でうまいこと処理してくれているみたいなので問題無い。

Gapless Sequences for Primary Keys: Varlena, LLC | PostgreSQL General Bits Newsletter

PostgreSQL の SERIAL は値が連続していることは保証しない。じゃあ連続する値を得るにはどうしたらいいかという話。なんだけどその中で出てくる以下の SQL が面白かった。

-- Initialize table with one row on creation --
INSERT INTO emp_pk_counter VALUES (0);

-- Disallow further insertions and deletions --
CREATE RULE noins_emp_pk AS ON INSERT TO emp_pk_counter
DO NOTHING;
CREATE RULE nodel_only_emp_pk AS ON DELETE TO emp_pk_counter
DO NOTHING;

emp_pk_counter というテーブルに対して 1 行だけ INSERT しておいて、以降 INSERT も DELETE も無視してしまうという RULE を定義している。これで emp_pk_counter テーブルは 1 行だけということが保証される。設定値を保持するテーブルなんかに使えそうだ。