« gemとかPROXY経由で使えるんじゃン | トップページ | 役に立つ9.3以後の新機能 »

2014.11.19

新規追加カラムを任意の位置に挿入したい

定期的にこの話を聞くのだが、まず結論から。
コマンドレベルでは存在しない。以上

MySQLとかInformixだったら

ALTER TABLE hogehoge ADD new_col VARCHAR(10) AFTER col_x;
みたいな感じでできるんだけどね。。

でもまああきらめない人々には次の方法がある。

●列の追加された新規テーブルを作ってそこにデータINSERT
確実さという意味ではこれが推奨だと思う。

でも列の並び順って、たしかシステムカタログ内で制御されていたはず、ということで力技で対応できないかちょっと試してみた。

実施した内容は下記の通りだけども、結論から言うとこれは使っちゃいけません(^^;

●pg_attributeを書き換えちゃおうぜ

想定してた手順はこんな感じ。
1)ALTER対象テーブルの列数を調べる
2)ALTER TABLEで列を追加する
3)pg_attributeテーブルのattnum列を更新する

実際の例を書いてみます。

CREATE TABLE test (test_id SERIAL
, test_name varchar(10) default ' ' NOT NULL
, test_email varchar(80) default ' ' NOT NULL
, birth_day date NOT NULL
, created_at timestamp with time zone default now() NOT NULL
, updated_at timestamp with time zone
, CONSTRAINT test_pkey PRIMARY KEY (test_id)
);

Createしたテーブル状態
こんなテーブルに対して"sex"カラムを"birth_day"の前に入れたい。
まずはカラムを追加しましょう。
ALTER TABLE test ADD COLUMN sex varchar(10) default 'Unknown' NOT NULL;

こうなります。

"update_at"の後ろに"sex"カラムが増えてます。
これがPostgreSQLの普通の状態です。

で、この表示順は実はpg_attributeテーブルにて管理されてます。
このtestテーブルのpg_attributeの状態を見てみると

こんな感じで、"attnum"列を見てもらうと、テーブル定義の列の順番がこの列の整数値の順番と等しいことがわかります。

ここからが今回の暴挙。

UPDATE pg_attribute
SET attnum = 8
WHERE attname = 'sex'
AND attrelid = 124430;
UPDATE pg_attribute
SET attnum = 7
WHERE attname = 'updated_at'
AND attrelid = 124430;
UPDATE pg_attribute
SET attnum = 6
WHERE attname = 'created_at'
AND attrelid = 124430;
UPDATE pg_attribute
SET attnum = 5
WHERE attname = 'birth_day'
AND attrelid = 124430;
UPDATE pg_attribute
SET attnum = 4
WHERE attname = 'sex'
AND attrelid = 124430;
力技ですねー。
PL/pgSQLあたりを使ってちゃんとしたプログラムにすることは可能でしょうが、動作を理解するためにあえて力技にしました(^^;

ではこのテーブル定義を確認してみましょう。

無事位置の入替が完了できていることが確認できました。

ところが、該当テーブルにデータをインサートしたときに

2014-11-xx xx:xx: JST WARNING: unexpected attrdef record found for attr 5 of rel test at character 35
2014-11-xxxx:xx:xx JST WARNING: unexpected attrdef record found for attr 7 of rel test at character 35
2014-11-xx xx:xx:xx JST WARNING: 2 attrdef record(s) missing for rel test at character 35
2014-11-19 xx:xx:xx JST WARNING: unexpected attrdef record found for attr 5 of rel test at character 22
2014-11-19 xx:xx:xx JST WARNING: unexpected attrdef record found for attr 7 of rel test at character 22
2014-11-19 xx:xx:xx JST WARNING: 2 attrdef record(s) missing for rel test at character 22
2014-11-xx xx:xx:xx JST FATAL: pre-existing shared memory block is still in use
2014-11-xx xx:xx:xx JST HINT: Check if there are any old server processes still running, and terminate them.
こういうFATALエラーを出してサービスが停止しました(^^;
列の順番を無理に変えたことで、列の定義情報などがおかしくなっている様な雰囲気である。
というわけで、システムカタログは生半可に触ってはいけない、というよい証明となってしまった(^^;

|

« gemとかPROXY経由で使えるんじゃン | トップページ | 役に立つ9.3以後の新機能 »

コメント

コメントを書く



(ウェブ上には掲載しません)




トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/2022/60678969

この記事へのトラックバック一覧です: 新規追加カラムを任意の位置に挿入したい:

« gemとかPROXY経由で使えるんじゃン | トップページ | 役に立つ9.3以後の新機能 »