postgrsqlのCopyコマンド

質問

postgrsqlでバックアップしたプレーンデータがリストアできないトラブルが発生しています。

それはそれで一旦置いておいて、
プレーンデータの中身であるcopyコマンドについて調べています。

copyコマンドの検証の為に簡単なsqlコマンドをpgAdminIIIのSQLエディタで叩いてみました。

create table tb1 (
item1 smallint
,item2 character varying
);

copy tb1 (item1, item2) FROM stdin;
1【TAB】aaa
2【TAB】bbb
3【TAB】ccc
\.

※ここに投稿するとタブを消されるので、【TAB】と表現しています。
結果は以下です。

ERROR: "1"またはその近辺で構文エラー
LINE 10: 1 aaa
^

********** エラー **********

ERROR: "1"またはその近辺で構文エラー
SQLステート:42601
文字:127

これの何がいけないのでしょうか？

回答No.1

root139 · ベストアンサー

> これの何がいけないのでしょうか？

----------------------------------------
1【TAB】aaa
2【TAB】bbb
3【TAB】ccc
\.
----------------------------------------

の部分がCOPYコマンド向けのデータそのものであり、psqlの入力として正しくない為、エラーとなっています。

リストアの際には下記の様なコマンドを実行するかと思います。

　psql リストア先のDB < pg_dumpで出力したファイル

これは、pg_dump で出力した内容をpsqlが標準入力から受け取って実行する事になりますが、
　copy tb1 (item1, item2) FROM stdin;
の行を受け取って実行すると標準入力からのデータをCOPYコマンドの入力として受取る状態になります。したがって、その後に続く行はCOPYコマンド自体の入力として扱われ、SQL文としては処理されません。\.に到達するとCOPYコマンドはデータが終了したと判断し、標準入力の受け取りを止め、それまでのデータをテーブルに挿入して終了します。
\.の次の行からは、再びpsqlが標準入力の内容を直接処理する事になります。

http://www.postgresql.jp/document/9.3/html/sql-copy.html

お礼

有難う御座います。

COPY コマンドも　CREATE TABLE も　SELECT も同列のコマンドのように紹介されているので、pdAdminIIIのSqlコマンドにコピペすれば実行可能だと思いました。

inssert into tb1 (item1, item2) values (1,'aaa');
inssert into tb1 (item1, item2) values (2,'bbb');
inssert into tb1 (item1, item2) values (3,'ccc');

を実行するのと同じような感覚で

copy tb1 (item1, item2) FROM stdin;
1【TAB】aaa
2【TAB】bbb
3【TAB】ccc
\.

を実行していました。

そういうモノではないという事でしょうか？

回答No.2

mitoneko

最近は、ユーティリティーもみんなＧＵＩになってしまって・・・こういう感覚ってわかりにくいのかもしれませんね。
　Postgreにも、コマンドラインのクライアントとして、psqlというユーティリティーがついています。
　コマンドラインから、psql.exeを実行して、あなたの書いたＳＱＬを一行ずつ、手打ちして（カット＆ペーストはしないこと）試してみてください。
　create table・・・を打つ時には、画面にpostgre#=というプロンプトが出てますよね。
　create table文の２行目を打つ時には、プロンプトが少し変わっているはずです。継続行の認識をしているんです。
　最後の;を打つと、テーブルを作った旨の表示が出ます。
　続いて、次のcopy文を打ちますが・・・
　copy文の行を打ち終わってリターンすると・・・
　プロンプトがおかしくないですか？
　お構いなしでデータをうち、\.の行を打って、リターンを押すと、コマンドを実行した旨の表示が出て、またプロンプトが復活しましたね。

　このプロンプトが無くなったのが、あなたの混乱の原因なんです。
　プロンプトが無くなったと言うことは、この間は、キーボードの入力も表示も、qsql.exeとは別のものが支配していると言うことです。具体的には、copyコマンドが支配しています。
　この間のキーボードからの入力は、qsqlへの入力では無く、copyコマンドへの入力として扱われていると言うことです。
　「\.」を入力すると、copyコマンドは、入力の終わりを認識して、キーボードの入力を元のqsqlへ返します。

　さて、psadminなんですが・・・
　guiのＳＱＬコマンドの実行ラインが、これと同じ動きをしてくれれば良いんですけど・・・残念ながら、stdinの解釈がCUIの時と違い、copyコマンドの次の行をcopyコマンドの支配下に置いていません。copyコマンドには、「stdinからの入力は無いよ」と言い放ち、次の行は、ちゃんとＳＱＬ文として送信しているのです。本来は、copyコマンドに渡すべき入力を渡さないのですから、挙動はおかしくなります。

　対処は、いくつか・・・
　・ｓｑｌをファイルに保存してファイルから実行してみる。
　・copy文の　from stdinの所のstdinを代わりにファイル名にしてみる。（もちろん、データの入ったテキストファイルです。）
　・こういう、stdinがらみの実験は、psqlでする。

お礼

有難う御座います。

概念上のお話はともかく、

具体的に
「どうすれば出来る」
あるいは、
「そのやり方を突き詰めても出来ないので諦めた方が良い」
といった、答えが見つかりません。

幾つか試してみましたが、いまだ成功していません。

・ｓｑｌをファイルに保存してファイルから実行してみる。

バックアップからPLAIN、データのみで実行して保存すると、ｓｑｌをファイルが出来ます。
それを実行出来ていません。

・copy文の　from stdinの所のstdinを代わりにファイル名にしてみる。（もちろん、データの入ったテキストファイルです。）

COPY tb1 (item1, item2) FROM E'c:\\temp\\test.csv';
のようにファイルを指定しても成功していません。