postgresqlでUTF-8でDBを作成するときのおまじない。

最近のpostgresだと　createdb -E UTF8 だとうまくいかなかったり
したのだかがあって、最近使っているおまじない。

createdb -E UTF8 -T template0 --lc-collate=ja_JP.utf8 --lc-ctype=ja_JP.utf8 DB名

postgres timestampをunixtimeへ変換

全部SQLで済んでしまえば問題ないのですが、
DBの負荷を除くためにソートをプログラム側で処理したいとか
ないですか？

まぁ、僕はよくそういう風に思ったりしていたので
何度かやっているのですが、いつも忘れてしまうのでメモ

■SQL例
select extract(epoch from timestamp '2009-11-13 15:00:00');
↓
1221631200

参考URL
http://u2k772.blog95.fc2.com/blog-entry-13.html

上記はtimestampでキャストした「2009-11-13 15:00:00」のデータを返しています。
実際に利用される場合は

select extract(epoch from '変換したいtimesatamp型のカラム名') from 'テーブル名';
となります。


-----
extract関数について少し書いてみます。

extract 関数は日付/時刻の値から年とか時刻などの部分フィールドを抽出します。

先のtimestamp以外にも下記のような例もイケるようです。

・曜日を取得してみる。
何曜日だっけ？というのが取得できます。カレンダーとか作るのに役立つかも。

select extract(dow from timestamp '2001-02-16 20:38:40');
Result: 5
曜日（0～6、日曜日が 0、timestamp の値のみで使用可）

■年間日数を取得してみる。
365日の何日目だっけ？というのが取得できます。

select extract(doy from timestamp '2001-02-16 20:38:40');
Result: 47

他にもいろいろあるので、参考サイトのURLをみてみてください。

参考URL
http://www.postgresql.jp/document/pg721doc/user/functions-datetime.html

to_char で 月 日 の0を取り除く方法

timestamp型のデータとかを　to_charでフォーマットかけるとかは　よくやりますよね。

例えばこんなかんじ

select to_char(now(),'YYYY/mm/dd');

こういうのね
これの結果ってこうなる

to_char
------------
2009/07/03

月と日の前に0がいるんですよ　0。こいつ邪魔臭くないですか？
と今更気づいたんですが、postgresのto_charのフォーマット表をみてみると・・・

MM 月の数 (01-12)
DD 月の日にち (01-31)

しかなくて、なんだこいつ不便だな！とか思っちゃうわけです。
しかし、ページを読み進めるとこんなのがあった。

FM 接頭辞 字詰めモード (スペース/ゼロを挿入するのを無効にする)

これや！キタ━━━━(゜∀゜)━━━━!!

select to_char(now(),'YYYY/FMmm/FMdd');
to_char
----------
2009/7/3

はい、うまくいきました。
お勉強になりました。



参考URL：http://www.sraoss.co.jp/PostgreSQL/Manual/PostgreSQL-7.1-ja/functions-formatting.html

テーブル一覧、シーケンス一覧 取得

なんとなく利用したかったので調べて見た結果

・テーブルのみ一覧で取得
select * from pg_tables where not tablename like 'pg%' order by tablename;

pg_tables でテーブルの一覧が取得できるけど、pg_～とかいう　たぶんシステムで利用していると思われるテーブルが出てくるので　not likeで除外しているようでした。
参考：http://homepage2.nifty.com/sak/w_sak3/doc/sysbrd/psql_k17.htm

・シーケンスのみ一覧で取得
SELECT c.relname FROM pg_class c LEFT join pg_user u ON c.relowner = u.usesysid WHERE c.relkind = 'S' order by c.relname;

postgresの知識が浅はかなのでよく理解できてないんですが　relkind = 'S' がシーケンスを指定しているようでした。関連付けの部分は、おそらく誤りを減らす対応なのかなと思います。
参考：http://d.hatena.ne.jp/phantasmist/20080327

SELECT c.relname FROM pg_class c where c.relkind = 'S' order by c.relname;
でも、一応内容は同じでした。

異なるケースもあるのでしょうね。
なんにしても、少しお勉強になりました。

sequenceがずれてしまった場合に直す方法

めったにないとおもうのですけど、たまに移行とかしたときに
sequenceがずれてしまたりするんですね。
で、いつも直す方法忘れてしまうのでメモ。


select setval('シーケンス',(select max(シーケンスを利用するカラム) from テーブル名));

例えば、商品テーブル(item)のID(id)をシーケンス利用していた場合
select setval('item_id_seq',(select max(id) from item));
とすれば　itemテーブルの最大のIDをシーケンスのmax値に設定してくれます。

シーケンスは　serial型で指定したのみであれば　大体　テーブル名_カラム名_seqという形になるかと思います。

item_id_seq は
item(テーブル名)_id(利用するカラム名)_seq ですね


ほんと、こんなことを書いてる時点でへっぽこなのがわかりまくりんぐなのですよね　とーほほー！＞＜

日時検索

今は日付データは timestamp型（またはdatetime型）にしてますが、昔のDBは　date型とtime型を分けていました。

最近、日時検索を依頼されたものの　日付と日時が別々なのでややこしいなぁと思っていたら　うまい策を見つけられたのでメモ的な。
今更感がただよいまくってるわけですが、僕は頭がわるいのですみません。(´Д⊂

■材料
to_char
接続詞「||」
::timestamp

■調理方法
1.日付（date）型と時間（time）型を接続詞で繋げる
　date || ' ' || time

2.１をtimestamp型にcastする
　(date || ' ' || time)::timestamp

3.to_charで日時データとする
to_char((date || ' ' || time)::timestamp,'YYYY-mm-dd HH24:mi:ss')

これで日時でSQLがかけられうようになった！
けど遅いです。型を作ってコピーしてあげたほうがいいんだろうなぁ。

やっべー これは便利だ。

無知な自分に乾杯★

生年月日いれるだけで、年齢でてくるなんて素敵すぎますよ！
便利な関数いっぱいあるんだろーなー

ってことで、postgresのage関数を見て　涙した次第でした(´ ▽｀)

age(datetime,datetime)
例： age('now','1957-06-13'::datetime)
::datetime がきかないときは　::timestamp で試して見ましょう！

↑は何歳何ヶ月・・・みたいにでるので　年齢だけとりたい場合は下記のようなかんじで。

date_part('year',age('now','1957-06-13'::datetime))

phppgadmin との格闘

ツールってどうもつかいこなせません。

Windowsのツールもそうですけど
phpの出来合いのソースとか
簡単に入れられるっていうのだけれど
どっかでつまづくんですよねぇ　(´Д⊂グスン


てなわけで　今回は　postgres 8.1以前のデータを　8.1以降にリストア
してみたら　なんだか　散々でしたよってかんじでした。

動かないことはないんですが、登録内容の　編集、削除なんかができないんですね
そんでなんでだ　なんでだー　って調べていたら　バックアップ／リストア関連部分が
浮上いたしました。

あまりよくわかってはいないんですが、ここらへんがキーだよみたいな
部分を自分へのメモ書き程度にまとめ。


１．ヒントの入手
http://phppgadmin.sourceforge.net/?page=faqより
===========================
Q: When browsing a table, the 'edit' and 'delete' links do not appear.

A: In order, phpPgAdmin will prefer the following as unique row identifiers:

Primary keys
Unique keys (cannot be parital or expressional indexes)
OID column (will be slow to update, unless you index the OID column)

Furthermore, any NULL values in the unique index will mean that that row will be uneditable. Also, since OIDs can become duplicated in a table, phpPgAdmin will alter the row, and then check to ensure that exactly one row has been modified - otherwise rollback will occur.
===================
Q.の部分が今回のケースにどんぴしゃです！(・∀・)キタコレ！
Aの部分、英語がよくわからんのですが、プライマリやユニークキー　OIDなカラムがあるとだめ？みたいなことをいっているようでした。


２．現場検証
次にリストア／バックアップまわりのことについて調べました。
バックアップデータを　8.1以前のDBにいれると問題ないのですが
8.1以降にいれると　serial型の　型（わかりにく汗）がかわっていました

具体的にはこうです。（phppgadmin で確認しました。）

8.1以前
nextval('public.mag_ad_2tier_list_id_seq'::text)

8.1以降
nextval('mag_ad_2tier_list_id_seq'::regclass)


これで　ふとおもったのが　regclassってあやしいなってこってす・・


３．真相へ近づきつつ・・・
ってことで　単刀直入にグーグルさんにきいてみました

検索ワード：postgres regclass
んででてきたのが
PostgreSQL 8.0.4から8.1の変更点です。

こんなことがかいてありました。

====
currval('seq1') 等の引数'seq1'がtext型でなくregclass型(内部用OID)の表現として扱われるようになっています。
====

OID!


これどっかでみましたよ
１．のAにありました
OID column
こいつっぽい


ってことで　真相解明！！(´▽｀)
は実はしてないんですが(汗　たぶん、regclassになってしまうのを　元のtextにしてしまえば　うまくうごいてくれるんじゃないかな？的な。


けつがあれですね　適当ですいません　起承転結は大の苦手　。・゜・(ノД`)・゜・。 うえええん


(・∀・)ノ~~~~　グッバーイ！

postgres ランダムで取得

　こんなのあったのシラナカッタヨ　。・゜・(ノД`)・゜・。 うえええん

　ってことでメモ

　postgresでランダムで取得したいときは　order by random()

無作為抽出に便利だにゃぁ　(´▽｀)

to_char － postgresql

使用するDBはほぼpostgres一本です。
うちの開発形態がそんなかんじなんですね。
MYSQLと僕は　なんだか相性が悪かったりします。

なので他のDBで使えるかはよくしらないんですが
timestamp型を使う時に　to_charは便利です。
一個前のでも書きましたが、年月日日時分秒と持つtimestamp型は
特定のものだけを出す時には整形してあげないと　いけないんですね。
そんなときに大活躍です。

いつも忘れてしまうので書いておきます。
to_char(timestamp,'YYYY-mm-dd HH24:MI:SS')

これで　年-月-日 時:分：秒

timestamp － postgresql

あたたかいですねぇ～(´▽｀)

SQLでtimestamp型でデータを格納してしまうと
YYYY-MM-DD HH:ii:ss.マイクロ秒 なフォーマットになってしまいます。

日付でgroup by したいなー　とか思っていたのですが
試しに　group by to_char(timestamp,'YYYY-mm-dd') とやってみたら
うまくいってスッキリ　(´▽｀)
やってみるものですねー