Archive for the 'PostgreSQL' Category

SQL:CASE WHEN

 
PostgreSQLだけというわけじゃなくて、いまのいままで(自分が)知らなかったのがびっくりというネタ。
それは、SQL文での CASE式。昔々、Accessで IIF(三項演算子)は使ったことがあったけど、SQLに IIFがないというのも、これを書こうと思って、ググって知ったくらい。

SELECT name, CASE sex WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '未記入' END as sex
FROM test_tbl

ほかの使い方はこちら「SQL の CASE 式って知ってますか?」参照。

SELECT count(*),
  CASE code WHEN 'M' THEN '男性' WHEN 'F' THEN '女性' ELSE 'オカマ?' END
FROM table1 GROUP BY code;

SELECT
 SUM( CASE WHEN code = 'M' THEN 1 ELSE 0 END ),  -- 男性の人数
 SUM( CASE WHEN code = 'F' THEN 1 ELSE 0 END )   -- 女性の人数
FROM table1;

CONSTRAINT check_salary  -- 平社員は給与が20万円以下という制約の例
 ( CASE WHEN code = '平社員' THEN 
   CASE WHEN salary = 100000 THEN 販売価格 * 0.9 ELSE 販売価格 END;

PostgreSQL:いろいろ Tips

 
INSERTで「nonstandard use of \\ in a string literal」というエラーが出たとき。
シングルコートを「E’」にすれば OK。「E’C:\\hogehoge.txt’」
 
PostgreSQLで INSERT直後の主キーの値をとりたいとき。

SELECT currval(pg_catalog.pg_get_serial_sequence('test_table', 'pkey'))

SELECT lastval()

下の方は同じセッション内でとるとき。次のやつは nextvalでとる。

SELECT nextval(pg_catalog.pg_get_serial_sequence('test_table', 'pkey'))

EC-CUBEでは関数が用意されているので、それを使うのが吉。(でないと、MySQLになったとき動かない)
 
ついで。PostgreSQLのユーザ設定。GRANTはテーブル単位しかかけられないって知ってました?
オートインクな主キーのあるテーブルは、シリアルキーのテーブルの方も grantしとかないとエラーになるので要注意。
 
も一つついでに。Ruby + DBI + Postgresでなにかやろうとしたときは、「Ruby DBI モジュールを使う」は必見。

Ruby で DBI で PostgreSQL

 
前回の続き。
違うユーザでコネクトできたから OKとしてましたが、実際テーブルの操作をしようとするとエラーになったので、対処。

# ruby test.rb
/usr/lib/ruby/gems/1.8/gems/dbd-pg-0.3.9/lib/dbd/pg/statement.rb:62:in `execute': ERROR:  permission denied for relation dtb_contact (DBI::ProgrammingError)

#irb
> require 'rubygems'
> require 'dbi'
> dbh = DBI.connect("dbi:Pg:test_db:localhost", "test_user", "test_pass")
> dbh.select_all("SELECT * FROM dtb_contact") {|row| p row }
DBI::ProgrammingError: ERROR:  permission denied for relation dtb_contact

DBへは接続できるけど、テーブルをリードするパーミッションがないよと。
ま、当然ですね。DBの ownerと違うユーザでコネクトしてるんですから。じゃあ、パーミッションを出しておきます。

# psql test_db -U test_db_owner
> grant all on dtb_contact to test_user;
GRANT

これで本当に OKです。

Rubyから PostgreSQLを使う

 
Rubyから PostgreSQLを使おうとして、ちょっとつまづいたので、後進の人のためにメモ。
入れようとした環境はこんな感じ。

  • Ubuntu 9.04(サーバ版)
  • ruby 1.8.7
  • Rails (2.1.0) + passenger (2.0.1)
  • psql (PostgreSQL) 8.3.9

つまり、少し古い Rubyと Railsが動いていて、かつ、PostgreSQLも動いていて、いま動いているものは変えたくないということ。
 
Read More

EC-CUBEから学ぶ PostgreSQLのリモート接続

なんかの雑誌みたいな見出しですが、EC-CUBEのインストールをしようとして、PostgreSQLのリモート接続ではじかれたので、対処法をメモ。
インストールのDB設定のところでこんなエラーが出たわけです。

DB Error:
[nativecode=pg_connect() [function.pg-connect]:
Unable to connect to PostgreSQL server: could not connect to server:
Connection refused Is the server running on host "192.168.1.xxx" and accepting TCP/IP connections on port 5432?

TCP/IP接続が許可されてねーんじゃないのということです。
EC-CUBEは、ひとつ前のバージョンをインストールしてるんですけど、どーやってインストールしたんですかね?
 
ググるとここ(質問 > その他 > PostgreSQLでUNIXドメインソケット接続での使用は可能か)にそれらしいことが書いてありました。
ここでは、EC-CUBEのインストーラの方にパッチをあててますけど、今回は真っ当に PostgreSQLの方をリモート接続できるようにしてみます。
 
さらにググって、postgresql.conf と pg_hba.conf に設定を書けばいいということがわかったので、こんな風にしてみた。

# e postgresql.conf
listen_addresses = '192.168.1.3'

# e pg_hba.conf
host  all  all  192.168.1.0/24  trust

 
で、PostgreSQLをリスタートしたら、エラーで起動しないでやんの。

 * The PostgreSQL server failed to start. Please check the log output:
2010-07-03 12:39:31 JST LOG:  could not load root certificate file "root.crt": no SSL error reported
2010-07-03 12:39:31 JST DETAIL:  Will not verify client certificates.
2010-07-03 12:39:31 JST LOG:  could not bind IPv4 socket: Cannot assign requested address
2010-07-03 12:39:31 JST HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2010-07-03 12:39:31 JST WARNING:  could not create listen socket for "192.168.1.3"
2010-07-03 12:39:31 JST FATAL:  could not create any TCP/IP sockets

 
結局、listen_addresses = ‘*’ じゃないといけなかったみたい。なにが問題なんだろうか。動くようになったからいいけど。

# e postgresql.conf
listen_addresses = '*'