Postgres カラム内の文字列を一括置換する

商品情報の文章の中で、ある番号が別の番号に変更になったので置換する必要があった。
データを入れているテーブルのカラムの文字列を置換する。

念のためテーブルをバックアップした上で、

update テーブル名 set 列名=replace(列名,’変換前文字列’,’返還後文字列’) where 条件;

500件ぐらいだったので一瞬でした。
スッキリ!

“Postgres カラム内の文字列を一括置換する” の続きを読む

postgreSQLからmysqlへの移行で気を付けたい点

postgreSQLからmysqlへの移行で気を付けたい点

postgresqlで、ある小さなシステムを構築準備してました。
現状あるシステムをそちらに載せ変えるという事で、
既存のデータをそのまま使いたい。

最初に確認しておけばすんだ事だけど、postgresのDBエンコードがEUCで、
変更できない事がデータの移行テストの段階でわかった。
新規DB作成も許可されていなかった。

元のシステムがUTF8なので、機種依存丸付き文字がばんばん使われている。
それをそのまま使えるようにしたいし、新しい入れ物はUTF8でないといけない。

mysqlなら新規DBも作成できるし、そもそもUTF8だったので、
急遽つくりかえることにした。

その際、種々の問題が発生したので覚え書きを残しておく。

■データ型
timestump型はdatetime型にする。
(複数カラムあるため)

■php
関数名を「pg_」から「mysql_」に置換

mysql_fetch_objectは行番号指定で取得できないので、
mysql_data_seekでポインタ位置を指定する。

■福問い合わせ
サブクエリがまず展開されるので、サブクエリに無用な結果が含まれると
総当たりとなり、膨大になる。(反応遅い)
サブクエリのSELECT文はできるだけ条件を入れた方が良い。

UPDATE a_table,(SELECT * FROM b_table)o_table SET a_table.name=o_table.oldname where a_table.code=o_table.code and o_table.num=1;

UPDATE a_table,(SELECT * FROM b_table where num=1)o_table SET a_table.name=o_table.oldname where a_table.code=o_table.code;

■mysqlでは正規表現による置換が出来ない
これは面倒だったし、今回はpostgresに持ってって処理した。
ある特定の文字に挟まれた数値データをとりだしたかっただけなので、
それでいけたけれど、文字コードに影響される様な文字列だったらそれはできないし、
ステップ踏むしかないかな。
phpでREGEXPとREPLACEを使って置換する方法をとるとか。

以上、私のメモ書き。
素人なので表現や方法が間違ってるかもしれません。

昔は丸付き文字なんか、利用者の方に制限させて、
文字化けしたら利用者のせいにするとか普通だったけど、
今は丸付き文字も機種依存文字も使うもんね。
私は今も使わないけど。
“postgreSQLからmysqlへの移行で気を付けたい点” の続きを読む

pg_dump でリストア時にエラー

別サーバで動いていたデータベースをdumpし、
不要な部分を排除してリストアしようとした時、
エラーとなりました。
ユーザーが無い場合等、出る事があるというのを見たので、
オーナーの部分を修正し、やり直すも、
エラー。
いろいろ調整してみても、エラー。
落ち着いてじっくり見ると、そもそも間違っていました。
psql database < dumpfile
とするところを、
pg_dump database < dumpfile
としていたのでした。…orz
(言い訳)
データベースのリストアなんて、
する機会も少ないし、
自分では滅多にしないからです。


“pg_dump でリストア時にエラー” の続きを読む

DBヘの接続

人に頼らず、なんとか自力で開発中、
いろいろ試すも、
Ident authentication failed for user
なるエラーがでて手こずった為、ここに記録。
ユーザを新しく作ってデータベースに権限を追加。
postgresql.confに
tcpip_socket = true
を追加。
pg_ctl restart
pg_hba.confを書き換える。
local databasename username password
host databasename username 127.0.0.1 255.255.255.255 password
これで良いのかどうか...。
service postgresql restart
TCP/IP ソケット経由の場合、下記のようにして接続テストを行う。

psql -h 127.0.0.1 -d databasename -U username

うまくいった様です。
ほぼ1日はまりました…orz


“DBヘの接続” の続きを読む