くれすのFreeBSD日記 このページをアンテナに追加 RSSフィード

只今建設中。。。

CressUnix.org

2008年11月06日(木)わりと実務っぽい

[] 一覧表から一括アップデートするSQL  一覧表から一括アップデートするSQL - くれすのFreeBSD日記 を含むブックマーク はてなブックマーク -  一覧表から一括アップデートするSQL - くれすのFreeBSD日記

電話番号とメールアドレスの一覧を送るので、古いやつと差し替えてください」みたいなSQLで書いてみた。

CREATE TEMPORARY TABLE import_m (
  id    INTEGER,
  tel   TEXT,
  email TEXT
);

COPY import_m FROM STDIN;
4	03-0000-0000	foo@example.com
\N	098-000-0000	bar@example.com
125	043-000-0000	baz@example.com
\.

UPDATE ONLY test_m
  SET
    tel = im.tel,
    email = im.email
  FROM import_m AS im
  WHERE test_m.id = im.id;

ここで、idがNULL(\N)のやつは更新されないので、idがないやつだけ新規登録するなんてのも可能。

参考

トラックバック - http://freebsd.g.hatena.ne.jp/Cress/20081106

2008年08月05日(火)意外とはまる人がいそうな問題

[] invalid input syntax for integer: "" 19:24  invalid input syntax for integer: "" - くれすのFreeBSD日記 を含むブックマーク はてなブックマーク -  invalid input syntax for integer: "" - くれすのFreeBSD日記

7系から8系に移行したときにはまる問題。第二弾。

このエラーはINSERT時に出ている。原因は、8系になってから空文字とNULLの区別が厳しくなったためらしい。


こんなSQL書いて実験してみた。7系だと問題なく動く。

CREATE SEQUENCE test_insert_id_seq;
CREATE TABLE test_insert (
  id    INTEGER NOT NULL DEFAULT nextval('test_insert_id_seq'),
  pref  INTEGER,
  city  TEXT,
  addr  TEXT,
  PRIMARY KEY ( id )
);

INSERT INTO test_insert ( pref, city, addr )
  VALUES ( '12', '船橋市', '湊町2-10-25' );
/* ===> OK */

INSERT INTO test_insert ( pref, city, addr )
  VALUES ( NULL, NULL, NULL );
/* ===> OK */

INSERT INTO test_insert ( pref, city, addr )
  VALUES ( '', '', '' );
/* ===> NG; invalid input syntax for integer: "" */

DROP TABLE test_insert;
DROP SEQUENCE test_insert_id_seq;

これどうしたらいいものやら。

入力チェックの段階で、空文字だったらNULLと置き換えてやる処理を加えるとかかな。


PerlDBI使っているので、渡された値の処理としてはこんな感じ?

my $query = {
    pref => '',
    city => '船橋市',
    addr => '湊町2-10-25',
};

# ↓prefが空文字だったらundef(=NULL)に変換(ひ、ひどい・・・)
$query->{pref} = undef if ($query->{pref} eq '');

新しい物好きで、何でもかんでもすぐ最新版を入れようとするのも考え物。

リニューアルなら話は別だが。

トラックバック - http://freebsd.g.hatena.ne.jp/Cress/20080805

2008年07月19日(土)バージョン違いではまる

[] TEXT型をINTEGER型に変更する 16:12  TEXT型をINTEGER型に変更する - くれすのFreeBSD日記 を含むブックマーク はてなブックマーク -  TEXT型をINTEGER型に変更する - くれすのFreeBSD日記

PostgreSQLは7系と8系だと、型の扱い方がちょっと違う。7系の方が縛りが緩く、明示的にキャストしたりしなくても関数が動いてしまったり、空文字なのにINTEGER型のカラムに書き込めたりしてしまう。

なので、7系での動作を想定していたプログラムを8系に移行したりすると動かなくなってしまうことがよくある。


ということで、TEXT型のカラムをINTEGER型に変換する方法。条件は以下の通り。

BEGIN;
UPDATE cress_demo SET item_type = NULL WHERE item_type = '';
ALTER TABLE cress_demo ALTER COLUMN item_type TYPE INTEGER USING ( item_type::INTEGER );
COMMIT;

厄介なのは空文字。NULL値ならそのまま整数型のカラムで使えるが、空文字だとキャストすることが出来ない。ここは一括UPDATEしてやればOKだが、レコード数が非常に多い場合は時間かかるかも。NOT NULL制約があったら・・・とりあえず0でも入れとけ。

ということで簡単でした。

*1:ALTER TABLE ... TYPE ... が使えないバージョンってあったっけ?7系で未実装だったのは確認済み。

トラックバック - http://freebsd.g.hatena.ne.jp/Cress/20080719