OS X Mountaion Lion で MacPorts を使って開発環境を整える(postgres編)

まずは、

$ sudo port install postgresql90

とすると終わり際に、

To use the postgresql server, install the postgresql90-server port

と出るので、引き続き、

$ sudo port install postgresql90-server

を実行。終わり際に、

###########################################################
# A startup item has been generated that will aid in
# starting postgresql90-server with launchd. It is disabled
# by default. Execute the following command to start it,
# and to cause it to launch at startup:
#
# sudo port load postgresql90-server
###########################################################

と、

To create a database instance, after install do
 sudo mkdir -p /opt/local/var/db/postgresql90/defaultdb
 sudo chown postgres:postgres /opt/local/var/db/postgresql90/defaultdb
 sudo su postgres -c '/opt/local/lib/postgresql90/bin/initdb -D /opt/local/var/db/postgresql90/defaultdb'

To tweak your DBMS, consider increasing kern.sysv.shmmax by adding an increased kern.sysv.shmmax .. to /etc/sysctl.conf

が出る。
順番にやっていく。

$ sudo port load postgresql90-server
$ sudo mkdir -p /opt/local/var/db/postgresql90/defaultdb
$ sudo chown postgres:postgres /opt/local/var/db/postgresql90/defaultdb
$ sudo su postgres -c '/opt/local/lib/postgresql90/bin/initdb -D /opt/local/var/db/postgresql90/defaultdb'

ここで、だーっと出る。

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale ja_JP.UTF-8.
The default database encoding has accordingly been set to UTF8.
initdb: could not find suitable text search configuration for locale ja_JP.UTF-8
The default text search configuration will be set to "simple".

fixing permissions on existing directory /opt/local/var/db/postgresql90/defaultdb ... ok
creating subdirectories ... ok
selecting default max_connections ... 20
selecting default shared_buffers ... 2400kB
creating configuration files ... ok
creating template1 database in /opt/local/var/db/postgresql90/defaultdb/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

Success. You can now start the database server using:

    /opt/local/lib/postgresql90/bin/postgres -D /opt/local/var/db/postgresql90/defaultdb
or
    /opt/local/lib/postgresql90/bin/pg_ctl -D /opt/local/var/db/postgresql90/defaultdb -l logfile start

最後のやつをやってみる。

$ /opt/local/lib/postgresql90/bin/postgres -D /opt/local/var/db/postgresql90/defaultdb

エラーになる。

postgres cannot access the server configuration file "/opt/local/var/db/postgresql90/defaultdb/postgresql.conf": Permission denied

パーミッションの問題か。
sudo su postgres して再トライ。

$ sudo su postgres
$ /opt/local/lib/postgresql90/bin/postgres -D /opt/local/var/db/postgresql90/defaultdb
LOG:  database system was shut down at 2012-08-11 04:10:00 JST
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections

動いたっぽい。

$ /opt/local/lib/postgresql90/bin/psql -l -U postgres                              List of databases
   Name    |  Owner   | Encoding | Collation | Ctype |   Access privileges  
-----------+----------+----------+-----------+-------+-----------------------
 postgres  | postgres | UTF8     | C         | C     |
 template0 | postgres | UTF8     | C         | C     | =c/postgres          +
           |          |          |           |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C         | C     | =c/postgres          +
           |          |          |           |       | postgres=CTc/postgres
(3 rows)

よさそうだ。
パスを通しておく。

$ vim .profile

export PATH=/opt/local/lib/postgresql90/bin:$PATH

テスト用DBを作っておく。

$ createdb sample_db -U postgres -E UTF-8

psqlして、

$ psql sample_db -U postgres

テーブルを作る。

sample_db=# CREATE TABLE users (
sample_db(# id SERIAL PRIMARY KEY,
sample_db(# name varchar(255)
sample_db(# );
NOTICE:  CREATE TABLE will create implicit sequence "users_id_seq" for serial column "users.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users"
CREATE TABLE

確認。

sample_db=# \d
              List of relations
 Schema |     Name     |   Type   |  Owner  
--------+--------------+----------+----------
 public | users        | table    | postgres
 public | users_id_seq | sequence | postgres
(2 rows)

sample_db=# \d users
                                 Table "public.users"
 Column |          Type          |                     Modifiers                     
--------+------------------------+----------------------------------------------------
 id     | integer                | not null default nextval('users_id_seq'::regclass)
 name   | character varying(255) |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

データを入れておく。

sample_db=# INSERT INTO users (name) VALUES ('yamada');
INSERT 0 1

セレクトして確認。

sample_db=# select * from users;
 id |  name 
----+--------
  1 | yamada
(1 row)

再びportでインストール

$ sudo port install php5-postgresql

アパッチ再起動

$ sudo /opt/local/apache2/bin/apachectl restart

infophp()で確認。

簡単なphpを書いて接続テスト。

<?php
$conn = pg_pconnect("dbname=sample_db user=postgres");
if (!$conn) {
  echo "An error occured.\n";
  exit;
}

$result = pg_query($conn, "SELECT * FROM users");
if (!$result) {
  echo "An error occured.\n";
  exit;
}

while ($row = pg_fetch_row($result)) {
  echo "Id: $row[0]  Name: $row[1]";
  echo "<br />\n";
}

表示される。

Id: 1 Name: yamada

よさそうだ。

一つ目を選択したら二つ目のセレクトの値が変化するようにしたいんだが、ソースの文字コードがEUCでjqueryのload()を使って読み込むと文字化けしてしまう。

文字コードがEUCなのは昔のソースなのでしょうがない。
いまさら直しようがない。
jquery の load() だと化けるってのもしょうがないだろう。調べるだけ無駄だ。
ということで、php側で一旦データを全部読み込んで、それをJSON形式のデータにして、直接javascriptのスクリプト部分に埋め込むことにしよう。

データのイメージ：
一つ目のセレクトがこんな感じであって、選択すると、

<select name="category_id">
    <option value="0">爬虫類</option>
    <option value="1">鳥類</option>
    <option value="2">昆虫option>
</select>

こんなようなデータが読み込まれて二つ目のセレクトが生成される。（鳥類だったら"1"のデータがセレクトになる）

var per_array ='{
    "0":{
        "1":"トカゲ",
        "2":"ワニ",
        "3":"ザリガニ",
    },
    "1":{
        "4":"スズメ",
        "5":"ハト",
        "6":"トンボ"
    },
    "2":{
        "7":"コオロギ",
        "8":"バッタ"
    }
}';

データベースにはこんな感じで入っている。

SELECT * FROM ikimono;
 id | category_id |   name   
----+-------------+----------
  1 |           0 | トカゲ
  2 |           0 | ワニ
  3 |           0 | ザリガニ
  4 |           1 | スズメ
  5 |           1 | ハト
  6 |           1 | トンボ
  7 |           2 | コオロギ
  8 |           2 | バッタ
(8 rows)

まず、データベースから値を読み込んで、JSON形式のデータを作成する。

$query = "SELECT id, name, category_id FROM ikimono";
$result = pg_query($pg, $query);
$n = pg_numrows($result);
if ($n){
    for ($i=0;$i<$n;$i++){
        $id = pg_result($result, $i, 0);
        $name = pg_result($result, $i, 1);
        $category_id = pg_result($result, $i, 2);
        // ここで一個一個配列に入れておいて
        $select_array[$category_id] .= '"'.$id.'":"'.$name.'",';
    }
}
//　ここでカテゴリー別に {} で囲む
foreach($select_array as $k => $v) {
    $v = preg_replace('/,$/', '', $v);
    $ikimono_data .= '"' . $k . '":{' . $v . '},';
}
$ikimono_data = preg_replace('/,$/', '', $ikimono_data);// 最後のカンマを削除

で、ページ表示部分のjavascript部分をこんな感じで。
配列の定義のところに直接phpでデータを埋め込む。

<script>
$(document).ready(function(){
    $('select[name=category_id]').change(function(){
        var category_id = $(this).val();
        var ikimono_array ='{<?php echo $ikimono_data; ?>}';
        var obj = jQuery.parseJSON(ikimono_array)[category_id];
        var ikimono_select = '';
        for (key in obj) {
            ikimono_select += '<option value="'+key+'">' + obj[key] + "</option>";
        }
        $('select[name=ikimono_id]').empty().append(ikimono_select);
    }).change();
});
</script>

二個目のセレクトは空っぽで用意しておく。

<select name="ikimono_id">
</select>

もっと他にいい方法がありそうな予感が大いににするが、とりあえずこんなところで。

登録日と更新日のフィールドがあって、その中で一番新しい日付を一発でセレクトするクエリー（CASE文とMAX）

登録日が created で、更新日が modified で、データを新規登録した時にね、
created には insert するけど、modified にはしない。
本当に編集した時だけ modified に入れるようなプログラムになってたとするじゃないですか。

そうするとデータはこんな感じになる訳で。

          created           |          modified          
----------------------------+----------------------------
 2009-03-17 10:18:24.792449 | 2009-03-17 10:32:55.900294
 2009-03-25 19:32:18.205694 | 
 2009-03-26 14:44:18.787866 | 
 2009-03-30 16:56:56.128813 | 
 2012-06-22 18:26:11.540513 | 
 2012-06-23 12:18:09.649044 | 
 2012-06-22 18:26:37.815316 | 2012-06-26 15:07:45.34223
 2012-06-21 12:46:03.151537 | 2012-06-26 16:44:05.811683
 2012-06-26 19:05:44.473284 | 2012-06-26 20:15:18.196717
 

modified は NULL になってるデータがある訳です。

こういう状態で、更新があった一番新しい日付をセレクトせよ、ってことなんですが。。。

 SELECT MAX(CASE WHEN modified IS NULL THEN created ELSE modified END) AS dates FROM items;
 

これでこうなる。

           dates            
----------------------------
 2012-06-26 20:15:18.196717
(1 row)

まあ、最初っから両方入れとけよって話ですね。

OSX、terminal で postgres を操作していて、なぜか SELECT * FROM した時だけ文字化けする時の話

そんな馬鹿な、というような話だが。
postgres なので EUC_JP の文字コードのDBがまだ残っていたりする。
ということで今、terminal の環境設定の詳細の文字エンコーディングが

日本語（EUC）

になっていたとする。この状態で、psql に入り、DB操作を開始、「やべえ、このDBは UTF-8 だった」
と気づいて、terminal の環境設定の詳細の文字エンコーディングを

Unicode(UTF-8)

に変更したとする。
そうすると、

SELECT name FROM items;

というようにひとつのカラムをセレクトする場合は文字化けしないが、

SELECT * FROM items;

というように全部をセレクトすると文字化けする。
もちろん、一旦 terminal を終了してちゃんと

Unicode(UTF-8)

になってるのを確認してから psql に入れば文字化けしない。
まったくもって馬鹿馬鹿しい現象だが、やってる本人は一体何故なのかさっぱり分からない。
そして一日を棒にふる訳である。

postgreSQLで通し番号（シーケンス）をリセットする時に1から始めたい場合

postgreSQL で id とかのフィールドで自動的に番号が増えていく、auto_increment みたいな設定をテーブルにすると、シーケンスが作られる。
例えばこんなテーブルを作ると

CREATE TABLE users(
  id SERIAL PRIMARY KEY,
  name VARCHAR(255)
);

こんなんなる。

 Schema |     Name     |   Type   |  Owner
--------+--------------+----------+----------
 public | users        | table    | postgres
 public | users_id_seq | sequence | postgres

しばらく users にデータをインサートした後で、
このシーケンスに対してセレクトをかけると、

SELECT * FROM users_id_seq ;

こんな結果が返ってくる

 sequence_name | last_value | increment_by |      max_value      | ・・・
---------------+------------+--------------+---------------------+-------
 users_id_seq  |         34 |            1 | 9223372036854775807 | ・・・

この last_value というのが自動で増えていった id の最後の番号で、これが34になったまま、 users テーブルのデータを消してしまうと、次は id が34から始まってしまう。
1からにしたい、という時に、

select setval ('users_id_seq', 1);

とやるんだが、こうすると last_value が1になるので、次は2からになってしまう。
じゃあ、

select setval ('users_id_seq', 0);

としてみたらどうかと思うが、これはエラーになる。

ということでこうするとよい。

select setval ('users_id_seq', 1, false);

一回目は last_value は1のままで、is_called が true になり、二回目は last_value は2になる。

GROUP BYを使った表の数値の合計あって、その合計が10個以上とかいう条件で抽出したい時にHAVING句を使う

例えばメンバーIDがあって、それに紐付いたポイント数が複数保存してあるようなテーブルがあるとする。

 member_id | point
-----------+-------
         4 |     2
         4 |     2
         4 |     3
         4 |     3
         4 |     3
         4 |     1
         4 |     3
         4 |     2
         8 |     2
         8 |     0
         4 |     1
         8 |     3
         4 |     6
         8 |     0

これをGROUP BY を使って合計を出すと、

SELECT member_id,SUM(point) AS sum FROM points 
GROUP BY member_id;

 member_id | sum
-----------+-----
         4 |  43
         8 |   7

こんな結果になるわけですが、この結果表をさらに、合計が10個以上、とかそういう条件で絞り込みたい訳です。
もしかしてこんなのあり？

SELECT member_id, SUM(point) AS sum FROM points 
WHERE sum > 10 
GROUP BY member_id;

とかやってみましたが、これは、

ERROR:  column "sum" does not exist

になります。だめです。
ということで、HAVING句を使って、

SELECT member_id, SUM(point) AS sum FROM points 
GROUP BY member_id
HAVING SUM(point) > 10;

というように書きます。
結果。

 member_id | sum
-----------+-----
         4 |  43

DISTINCT と ORDER BY を一緒に使う時の、PostgresとMySQLの違い

今やってるやつで、出てきた現象なのでちょっとメモ。
例えば items というようなテーブルがあって、id と name がある。

 id |  name
----+--------
  1 | test
  2 | sample

で、この items には複数のカテゴリーとサブカテゴリーが紐づけられている。
紐づけ用のテーブルは items_categories というようなもので、こんな感じになっている。

 item_id | category_id | sub_category_id | orders
---------+-------------+-----------------+--------
       1 |           1 |               1 |      2
       1 |           1 |               2 |      2
       2 |           1 |               2 |      1

アイテム1はサブカテゴリー1と2に、アイテム2はサブカテゴリー2に紐付いていて、どちらもカテゴリーは1だが、
そのカテゴリー1の中での並ぶ順番が orders ということで設定されている。
アイテム2が順番は1、アイテム1が2番と。

で、こいつをまず順番関係なしに、join してみると

SELECT ic.item_id, i.name 
FROM items_categories ic 
INNER JOIN items i ON ic.item_id=i.id
WHERE ic.category_id=1

こうなる。

 item_id |  name
---------+--------
       1 | test
       1 | test
       2 | sample

orders 順にしたいので、

SELECT ic.item_id, i.name 
FROM items_categories ic 
INNER JOIN items i ON ic.item_id=i.id
WHERE ic.category_id=1
ORDER BY ic.orders

とすると、

 item_id |  name
---------+--------
       2 | sample
       1 | test
       1 | test

これは動く。item_id が重複してるので、distinct を使うと、

SELECT DISTINCT ic.item_id, i.name 
FROM items_categories ic 
INNER JOIN items i ON ic.item_id=i.id
WHERE ic.category_id=1
ORDER BY ic.orders

Postgresの場合、エラーになる。

ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list

MySQLの場合、

 item_id |  name
---------+--------
       2 | sample
       1 | test

出る。
Postgresでも

SELECT DISTINCT ic.item_id, i.name, ic.orders 
FROM items_categories ic 
INNER JOIN items i ON ic.item_id=i.id
WHERE ic.category_id=1
ORDER BY ic.orders

とすればちゃんと出る。

 item_id |  name  | orders
---------+--------+--------
       2 | sample |      1
       1 | test   |      2

そりゃ、おかしいのはMySQLでしょう。この場合。