3,728
回編集
差分
Tep3
,→データベース移行
...
</pre>
<pre>Í
インポート
\COPY others(user_id, note_id, client_id, name, yomi, nusi, line, generation, created_at, updated_at) FROM '/Users/jq/3tep_gomi/others_1.csv' WITH CSV DELIMITER ',';
...
</pre>
==== おまけ ====
* エラー
:- Othersのclient_idがClientsのidにないとき
<pre>
ERROR: insert or update on table "others" violates foreign key constraint "fk_rails_10377da6e2"
DETAIL: Key (client_id)=(26) is not present in table "clients".
</pre>
:- others_1.csvから不要なclient_idを含む行の削除方法
<pre>
作業用データベースを作る
% psql postgres
postgres=# create database x_0226;
作業用テーブルを作る
% psql x_0226
x_0226=# CREATE TABLE clients(id serial, client_id int);
x_0226=# CREATE TABLE others(id serial, client_id int);
エクスポートしたcsvから不要な列を削除してそれぞれにインポートする
x_0226=# \COPY clients(client_id) FROM '/Users/jq/3tep_gomi/clients_1.csv' WITH CSV DELIMITER ',';
x_0226=# \COPY others(client_id) FROM '/Users/jq/3tep_gomi/others_1_c.csv' WITH CSV DELIMITER ',';
EXCEPTを使って
x_0226=# SELECT client_id FROM others EXCEPT SELECT client_id FROM clients order by client_id asc;
x_0226=# create table x_others(id serial, user_id int, note_id int, client_id int, name varchar, yomi varchar, nusi boolean, line int, generation varchar, created_at timestamp, updated_at timestamp);
x_0226=# \COPY x_others(user_id, note_id, client_id, name, yomi, nusi, line, generation, created_at, updated_at) FROM '/Users/jq/3tep_gomi/others_1.csv' WITH CSV DELIMITER ',';
x_0226=# delete from x_others where client_id in (SELECT client_id FROM others EXCEPT SELECT client_id FROM clients order by client_id asc);
x_0226=# \COPY (select user_id, note_id, client_id, name, yomi, nusi, line, generation, created_at, updated_at from x_others order by client_id asc) TO '/Users/jq/3tep_gomi/others_1.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER;
</pre>
<pre>
</pre>
<pre>
</pre>
<pre>
</pre>
