あるテーブルを元にして変更を加えた新テーブルを作成し、
元テーブルからデータをコピーします。
データコピーの際に、新テーブルで新たに追加した列へ
自動採番を行った値をinsertして行きたいのですが、方法がわかりません。
データは1件ずつではなく、一度の処理でコピーしたいです。
詳細手順は下記に示します。
CREATE TABLE hoge (hoge text);
上記のようなテーブルがあったとします。
このhogeテーブルを、hoge_idと言う列を持ったテーブルに変更します。
変更方法は下記の通りとします。
1.今あるhogeテーブル名をbk_hogeに変更
2.hoge_id列の定義を追加した新hogeテーブルを作成
CREATE TABLE hoge
(
hoge_id numeric(2),
hoge text,
CONSTRAINT hoge_pkey PRIMARY KEY (hoge_id)
);
3.bk_hogeに既にinsertされているデータを、新hogeテーブルにコピー
INSERT INTO hoge SELECT
★,
bk_hoge.hoge
FROM bk_hoge;
この★の部分で、データ1件ずつに対して自動採番を行った値を入れていきたいです。
よい方法がございましたらご教授願えれば幸いです。
よろしくお願い致します。
No.3
1314
393
50pt
使用しているDatabase は明記された方が良いと思います。
いろいろやり方はあると思いますが、PostgreSQL であればプロシージャを使用したやり方はどうでしょうか。
以下 SQL として実行します。
【コピー先テーブルの作成】
2
2
CREATE TABLE bk_hoge (
hoge_id NUMERIC(2) PRIMARY KEY,
hoge TEXT );
【プロシージャの作成】
CREATE OR REPLACE FUNCTION copy_table() RETURNS INTEGER AS $$
DECLARE
mv RECORD;
num INTEGER;
BEGIN
num := 0;
FOR mv IN SELECT * FROM hoge ORDER BY hoge LOOP
num := num + 1;
INSERT INTO bk_hoge ( hoge_id, hoge ) values ( num, ''||mv.hoge||'' );
END LOOP;
RETURN num;
END;
$$ LANGUAGE 'plpgsql'
【プロシージャの実行】
select copy_table();
copy_table は新規テーブルに対して実行するのが前提ですので、再実行する前にテーブルを再作成してください。
（プロシージャの中でやっても良いのですが、あえて外に出しました。）
http://www.postgresql.jp/document/pg824doc/html/plpgsql.html
http://www50.tok2.com/home/oppama/pgfuncdef.html
不明な点はコメントで対応します。
No.1
2124
11pt
http://homepage2.nifty.com/sak/w_sak3/doc/sysbrd/sq_kj01.htm
oracleであればsequence というのが使えますが対象は何になりますか？
記載漏れとなり申し訳ございません。
使用環境はPostgresです。
No.2
135391198
INSERT INTO hoge SELECT
ROWNUM,
bk_hoge.hoge
FROM bk_hoge;
上記を実行しましたが、環境がPostgresの為か
ERROR: column "rownum" does not exist
が発生してしまいました。
PostgresにはROWNUMが無いようですね。残念です。
No.3
1314393
ここでベストアンサー
50pt
使用しているDatabase は明記された方が良いと思います。
いろいろやり方はあると思いますが、PostgreSQL であればプロシージャを使用したやり方はどうでしょうか。
以下 SQL として実行します。
【コピー先テーブルの作成】
CREATE TABLE bk_hoge (
hoge_id NUMERIC(2) PRIMARY KEY,
hoge TEXT );
【プロシージャの作成】
CREATE OR REPLACE FUNCTION copy_table() RETURNS INTEGER AS $$
DECLARE
mv RECORD;
num INTEGER;
BEGIN
num := 0;
FOR mv IN SELECT * FROM hoge ORDER BY hoge LOOP
num := num + 1;
INSERT INTO bk_hoge ( hoge_id, hoge ) values ( num, ''||mv.hoge||'' );
END LOOP;
RETURN num;
END;
$$ LANGUAGE 'plpgsql'
【プロシージャの実行】
select copy_table();
copy_table は新規テーブルに対して実行するのが前提ですので、再実行する前にテーブルを再作成してください。
（プロシージャの中でやっても良いのですが、あえて外に出しました。）
http://www.postgresql.jp/document/pg824doc/html/plpgsql.html
http://www50.tok2.com/home/oppama/pgfuncdef.html
不明な点はコメントで対応します。
詳細な解説をありがとうございました。
「プロシージャ」というものを使用したことが無いので、
記載していただいた【プロシージャの作成】について解析させて頂きます。
見た事のない構文もあるので、質問させていただくこともあるかもしれませんが、
その際はどうぞよろしくお願い致します。
ありがとうございました。
No.4
3542140
11pt
http://homepage2.nifty.com/lightbox/VA003334/dbaccess05090717281...
http://www.itmedia.co.jp/enterprise/0307/18/epn20_6.html
Postgresでも、sequence使えるようですよ。
sequenceを作成するのが手堅いような。
自動採番について、色々なDB条件で記載されていて
今回以外にも参考にさせて頂けそうです。
ありがとうございました。
No.5
72254
17pt
PostgreSQLでの自動採番は、連番型(SERIAL型）を使います。
CREATE SEQUENCEも実装されており、SERIAL型は等価です。
１．テーブル名の変更
-- リネーム ALTER TABLE hoge RENAME TO bk_hoge;
２．新テーブルの作成
２．１ 新テーブルの定義
-- 新テーブル CREATE TABLE hoge (hoge_id SERIAL, -- PostgreSQLでの連番型。CREATE SEQUENCEも使用可能 hoge TEXT, CONSTRAINT hoge_pkey PRIMARY KEY (hoge_id) );
連番型を操作する場合、「シーケンスの名前」を指定する必要があります。CREATE SEQUENCEを使う場合は、名前はユーザ指定になりますが、SERIAL型で定義時は、「表名_列名_seq」になります。
上記の表定義なら、「hoge_hoge_id_seq」という名前で生成されます。
２．２ データのコピー
(1)連番の初期化
連番型を持つ表を定義した直後なら、初期化は自動的に行われます。
テスト等でリセットしたい場合は、次のような操作を行います。
TRUNCATE TABLE hoge;
SELECT SETVAL('hoge_hoge_id_seq',1,FALSE);
(2)データのコピー＜指定例１＞
連番型の列を除いてINSERTする。
INSERT INTO hoge(hoge) -- 連番型以外の列を指定 SELECT hoge FROM bk_hoge;
(3)データのコピー＜指定例２＞
NEXTVAL関数を使う。
INSERT INTO hoge
SELECT
NEXTVAL('hoge_hoge_id_seq'),
hoge
FROM bk_hoge;
詳細な解説をありがとうございました。
但し、今回の場合は列自体に自動採番の制約をかけたくなかったので
教えて頂いた方法は、別の機会に参考にさせて頂きます。
ひとつ気になったのですが、
(2)データのコピー＜指定例１＞
連番型の列を除いてINSERTする。
では、hoge_idがPkeyとなっているので
他の列の値を設定しようとするとエラーが出ないでしょうか・・・？
似たような方法を試したのですが、エラーが出てしまいました。
SERIAL制約ではないテーブルでしたし、
私の文法が違ったのかもしれませんが。。。
chuken_kenkou
2007/10/17 03:35:03
1
1
1
詳細な解説をありがとうございました。
「プロシージャ」というものを使用したことが無いので、
記載していただいた【プロシージャの作成】について解析させて頂きます。
見た事のない構文もあるので、質問させていただくこともあるかもしれませんが、
その際はどうぞよろしくお願い致します。
ありがとうございました。