「Tep3」を編集中
この編集を取り消せます。
下記の差分を確認して、本当に取り消していいか検証してください。よろしければ変更を保存して取り消しを完了してください。
最新版 | 編集中の文章 | ||
122行目: | 122行目: | ||
% psql postgres | % psql postgres | ||
postgres=# create database x_0226; | postgres=# create database x_0226; | ||
− | |||
作業用テーブルを作る | 作業用テーブルを作る | ||
% psql x_0226 | % psql x_0226 | ||
x_0226=# CREATE TABLE clients(id serial, client_id int); | x_0226=# CREATE TABLE clients(id serial, client_id int); | ||
x_0226=# CREATE TABLE others(id serial, client_id int); | x_0226=# CREATE TABLE others(id serial, client_id int); | ||
− | |||
エクスポートしたcsvから不要な列を削除してそれぞれにインポートする | エクスポートしたcsvから不要な列を削除してそれぞれにインポートする | ||
x_0226=# \COPY clients(client_id) FROM '/Users/jq/3tep_gomi/clients_1.csv' WITH CSV DELIMITER ','; | 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 ','; | 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=# 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=# 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=# \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=# 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; | 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> | ||