「Tep3」の版間の差分
提供: wikipokpok
(→ListsのidをClientsのlist_idに入れる) |
(→Categories → Lists) |
||
(同じ利用者による、間の30版が非表示) | |||
27行目: | 27行目: | ||
==== Users ==== | ==== Users ==== | ||
* はじめにtepnoteのユーザーをエクスポートし tep3にインポートする | * はじめにtepnoteのユーザーをエクスポートし tep3にインポートする | ||
− | ==== | + | ==== Notes ==== |
* tepnoteのjiinsをエクスポートし tep3のnotesにインポートする | * tepnoteのjiinsをエクスポートし tep3のnotesにインポートする | ||
<pre> | <pre> | ||
38行目: | 38行目: | ||
</pre> | </pre> | ||
:- このときJiinsのidとNotesのidを合わせる Notesのuser_idは1にする(1は淨久寺グループが代々引き継ぐアカウントのユーザーID) | :- このときJiinsのidとNotesのidを合わせる Notesのuser_idは1にする(1は淨久寺グループが代々引き継ぐアカウントのユーザーID) | ||
+ | <pre> | ||
+ | update notes set user_id = 1; | ||
+ | </pre> | ||
<pre> | <pre> | ||
tep3_development=# select * from notes; | tep3_development=# select * from notes; | ||
59行目: | 62行目: | ||
</pre> | </pre> | ||
− | ==== | + | ==== Clients ==== |
− | * | + | * 旧ClientsをClientsとOthersに分ける 寺ごと(ノートごと)に出し入れする |
− | :- | + | :- 新Clients(家テーブル) 旧Clientsから主を抜き出す ie_idを新Clientsのidにする そのidにOthersがぶらさがる 旧Clientsのmemoは別テーブルに移す |
<pre> | <pre> | ||
エクスポート | エクスポート | ||
− | \COPY (select ie_id, user_id, jiin_id, created_at, updated_at from clients where jiin_id = 1 and nusi = true order by ie_id asc, jiin_id asc) TO '/Users/jq/3tep_gomi/clients_1.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER; | + | \COPY (select ie_id, user_id, jiin_id, table_order, created_at, updated_at from clients where jiin_id = 1 and nusi = true order by ie_id asc, jiin_id asc) TO '/Users/jq/3tep_gomi/clients_1.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER; |
− | \COPY (select ie_id, user_id, jiin_id, created_at, updated_at from clients where jiin_id = 2 and nusi = true order by ie_id asc, jiin_id asc) TO '/Users/jq/3tep_gomi/clients_2.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER; | + | \COPY (select ie_id, user_id, jiin_id, table_order, created_at, updated_at from clients where jiin_id = 2 and nusi = true order by ie_id asc, jiin_id asc) TO '/Users/jq/3tep_gomi/clients_2.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER; |
... | ... | ||
72行目: | 75行目: | ||
<pre> | <pre> | ||
インポート | インポート | ||
− | \COPY clients(id, user_id, note_id, created_at, updated_at) FROM '/Users/jq/3tep_gomi/clients_1.csv' WITH CSV DELIMITER ','; | + | \COPY clients(id, user_id, note_id, line, created_at, updated_at) FROM '/Users/jq/3tep_gomi/clients_1.csv' WITH CSV DELIMITER ','; |
− | \COPY clients(id, user_id, note_id, created_at, updated_at) FROM '/Users/jq/3tep_gomi/clients_2.csv' WITH CSV DELIMITER ','; | + | \COPY clients(id, user_id, note_id, line, created_at, updated_at) FROM '/Users/jq/3tep_gomi/clients_2.csv' WITH CSV DELIMITER ','; |
... | ... | ||
</pre> | </pre> | ||
− | * | + | :- シーケンス編集 |
+ | <pre> | ||
+ | シーケンスの最大値確認 | ||
+ | select max(id) from clients; | ||
+ | |||
+ | シーケンスを最大値以上に設定 | ||
+ | select setval ('clients_id_seq', 5128); | ||
+ | </pre> | ||
+ | :- list_idにnullがあるとき | ||
+ | <pre> | ||
+ | リスト「未分類」を作りそのidを入れる | ||
+ | update clients set list_id = 102 where list_id is null; | ||
+ | </pre> | ||
+ | |||
+ | ==== Others ==== | ||
+ | * 旧ClientsをClientsとOthersに分ける 寺ごと(ノートごと)に出し入れする | ||
<pre> | <pre> | ||
エクスポート | エクスポート | ||
− | \COPY (select user_id, jiin_id, ie_id, name, yomi, nusi, each_order, | + | \COPY (select user_id, jiin_id, ie_id, name, yomi, nusi, each_order, generation, created_at, updated_at from clients where jiin_id = 1 order by ie_id asc) TO '/Users/jq/3tep_gomi/others_1.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER; |
... | ... | ||
87行目: | 105行目: | ||
<pre> | <pre> | ||
インポート | インポート | ||
− | \COPY others(user_id, note_id, client_id, name, yomi, nusi, | + | \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> | </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を使ってClientsにないOthersのclient_idを拾う | ||
+ | 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); | ||
+ | |||
+ | others_1.csvをインポートする | ||
+ | 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 ','; | ||
+ | |||
+ | ClientsにないOthersのclient_idを含む行を削除する | ||
+ | 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; | ||
+ | |||
+ | tep3のOthersにインポートする | ||
+ | tep3_development=# \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> | ||
+ | |||
+ | ==== Lists ==== | ||
* 旧Categoriesのset_id1を寺ごとに抜き出し Listsに入れる | * 旧Categoriesのset_id1を寺ごとに抜き出し Listsに入れる | ||
<pre> | <pre> | ||
106行目: | 165行目: | ||
... | ... | ||
</pre> | </pre> | ||
+ | :- シーケンス編集 | ||
+ | <pre> | ||
+ | シーケンスの最大値確認 | ||
+ | select max(id) from lists; | ||
+ | |||
+ | シーケンスを最大値以上に設定 | ||
+ | select setval ('lists_id_seq', 128); | ||
+ | </pre> | ||
+ | |||
==== ListsのidをClientsのlist_idに入れる ==== | ==== ListsのidをClientsのlist_idに入れる ==== | ||
<pre> | <pre> | ||
124行目: | 192行目: | ||
\COPY (select properties.ie_id as id, categories.id as list_id from categories join properties on categories.id = properties.tiku_id where categories.jiin_id = 1 order by properties.ie_id asc) TO '/Users/jq/3tep_gomi/ie_id_list_id_1.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER; | \COPY (select properties.ie_id as id, categories.id as list_id from categories join properties on categories.id = properties.tiku_id where categories.jiin_id = 1 order by properties.ie_id asc) TO '/Users/jq/3tep_gomi/ie_id_list_id_1.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER; | ||
</pre> | </pre> | ||
− | * | + | * tep3_developmentデータベース内に作業用一時テーブルを作る(後で削除) Clientsのlist_idに上で抜き出したlist_idを入れるため |
<pre> | <pre> | ||
CREATE TABLE x_list_id (client_id int, list_id int); | CREATE TABLE x_list_id (client_id int, list_id int); | ||
137行目: | 205行目: | ||
UPDATE clients SET list_id = x_list_id.list_id FROM x_list_id WHERE clients.id = x_list_id.client_id; | UPDATE clients SET list_id = x_list_id.list_id FROM x_list_id WHERE clients.id = x_list_id.client_id; | ||
</pre> | </pre> | ||
+ | |||
==== Categories ==== | ==== Categories ==== | ||
− | * | + | * 旧Categories set_id 2 が主なカテゴリ |
<pre> | <pre> | ||
− | + | * エクスポート | |
+ | \COPY (select id, user_id, jiin_id, content, line, created_at, updated_at from categories where jiin_id = 1 and set_id = 2 and content <> '' order by line asc) TO '/Users/jq/3tep_gomi/category_1_1.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER; | ||
+ | ... | ||
+ | </pre> | ||
+ | <pre> | ||
+ | * インポート | ||
+ | \COPY categories(id, user_id, note_id, content, line, created_at, updated_at) FROM '/Users/jq/3tep_gomi/category_1_1.csv' WITH CSV DELIMITER ','; | ||
+ | </pre> | ||
+ | :- シーケンス編集 | ||
+ | <pre> | ||
+ | シーケンスの最大値確認 | ||
+ | select max(id) from categories; | ||
+ | |||
+ | シーケンスを最大値以上に設定 | ||
+ | select setval ('categories_id_seq', 128); | ||
</pre> | </pre> | ||
<pre> | <pre> | ||
146行目: | 229行目: | ||
----+---------+---------+---------+------+------+---------------------+--------------------- | ----+---------+---------+---------+------+------+---------------------+--------------------- | ||
1 | 1 | 1 | 檀家 | 1 | t | 2022-02-24 07:00:00 | 2022-02-24 07:00:00 | 1 | 1 | 1 | 檀家 | 1 | t | 2022-02-24 07:00:00 | 2022-02-24 07:00:00 | ||
+ | </pre> | ||
+ | |||
+ | ==== Client_categories ==== | ||
+ | * | ||
+ | <pre> | ||
+ | エクスポート | ||
+ | \COPY (select properties.user_id, properties.jiin_id, properties.ie_id, properties.category_id, properties.created_at, properties.updated_at from properties join categories on properties.category_id = categories.id where properties.jiin_id = 1 and categories.set_id = 2 order by ie_id asc, category_id asc) TO '/Users/jq/3tep_gomi/client_category_1_1.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER; | ||
+ | |||
+ | ... | ||
+ | </pre> | ||
+ | <pre> | ||
+ | インポート | ||
+ | \COPY client_categories(user_id, note_id, client_id, category_id, created_at, updated_at) FROM '/Users/jq/3tep_gomi/client_category_1_1.csv' WITH CSV DELIMITER ','; | ||
+ | |||
+ | ... | ||
+ | </pre> | ||
+ | <pre> | ||
</pre> | </pre> | ||
<pre> | <pre> | ||
152行目: | 252行目: | ||
</pre> | </pre> | ||
− | + | <pre> | |
− | + | </pre> | |
− | + | <pre> | |
− | + | </pre> | |
+ | <pre> | ||
+ | </pre> |
2022年3月1日 (火) 07:17時点における最新版
% ruby -v % rbenv versions % rbenv local 2.7.5 % rails -v
% rails new tep3 -d postgresql database.yml username rails db:create
$ yarn add bootstrap@next $ yarn add @popperjs/core
app/javascript/packs/application.js import "bootstrap"; import "../stylesheets/application";
% mkdir app/javascript/stylesheets/ % touch app/javascript/stylesheets/application.scss @import "bootstrap";
app/views/layouts/application.html.erb <%= stylesheet_pack_tag 'application', media: 'all', 'data-turbolinks-track': 'reload' %>
目次
データベース移行[編集]
エクスポート インポート[編集]
Users[編集]
- はじめにtepnoteのユーザーをエクスポートし tep3にインポートする
Notes[編集]
- tepnoteのjiinsをエクスポートし tep3のnotesにインポートする
エクスポート \COPY (select user_id, jiin_name, kind, meeting_time, role_num, created_at, updated_at from jiins order by user_id asc) TO '/Users/jq/3tep_gomi/notes.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER;
インポート \COPY notes(user_id, name, kind, meeting_time, role, created_at, updated_at) FROM '/Users/jq/3tep_gomi/notes.csv' WITH CSV DELIMITER ',';
- - このときJiinsのidとNotesのidを合わせる Notesのuser_idは1にする(1は淨久寺グループが代々引き継ぐアカウントのユーザーID)
update notes set user_id = 1;
tep3_development=# select * from notes; id | user_id | name | kind | meeting_time | role | belong | created_at | updated_at ----+---------+--------------------+------+--------------+------+--------+----------------------------+---------------------------- 1 | 1 | 淨久寺 | | | 1 | | 2019-01-30 01:12:29.847655 | 2020-02-28 18:17:08.925978 2 | 1 | 光源寺 | | | 1 | | 2019-02-01 13:04:29.292541 | 2020-02-28 18:17:45.068205 3 | 1 | 廣國院 | | | 1 | | 2019-02-01 13:04:37.908007 | 2020-08-25 14:25:08.311179 4 | 1 | 安養寺 | | | 1 | | 2019-02-01 13:04:50.543585 | 2020-02-28 18:18:04.200338 5 | 1 | 太陽 | | | 1 | | 2019-05-11 12:28:30.551584 | 2020-02-28 18:18:15.454727 6 | 1 | 家族 | | | 1 | | 2019-06-20 11:28:05.371417 | 2020-02-28 18:18:28.641819 7 | 1 | SANデザイン事務所 | | | 1 | | 2019-08-11 12:43:11.472652 | 2020-12-15 15:39:00.831541 8 | 1 | 一般 | | | 1 | | 2019-12-24 11:27:21.384603 | 2019-12-24 11:27:21.384603 9 | 1 | 親戚 | | | 1 | | 2019-12-24 13:11:43.345268 | 2019-12-24 13:11:43.345268 10 | 1 | 光源寺護持会 | | | 3 | | 2020-02-15 17:39:30.312985 | 2020-03-06 11:35:27.445689 11 | 1 | 淨久寺護持会 | | | 3 | | 2020-02-28 18:16:01.176519 | 2020-02-28 18:16:01.176519 12 | 1 | 廣國院護持会 | | | 3 | | 2020-03-02 13:18:23.289695 | 2020-03-02 13:18:23.289695 13 | 1 | 安養寺護持会 | | | 3 | | 2020-03-02 13:22:51.367168 | 2020-03-02 13:22:51.367168 14 | 1 | 光源寺晋山結制会計 | | | 3 | | 2020-03-25 11:33:09.239345 | 2020-03-25 11:33:09.239345 (14 rows)
Clients[編集]
- 旧ClientsをClientsとOthersに分ける 寺ごと(ノートごと)に出し入れする
- - 新Clients(家テーブル) 旧Clientsから主を抜き出す ie_idを新Clientsのidにする そのidにOthersがぶらさがる 旧Clientsのmemoは別テーブルに移す
エクスポート \COPY (select ie_id, user_id, jiin_id, table_order, created_at, updated_at from clients where jiin_id = 1 and nusi = true order by ie_id asc, jiin_id asc) TO '/Users/jq/3tep_gomi/clients_1.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER; \COPY (select ie_id, user_id, jiin_id, table_order, created_at, updated_at from clients where jiin_id = 2 and nusi = true order by ie_id asc, jiin_id asc) TO '/Users/jq/3tep_gomi/clients_2.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER; ...
インポート \COPY clients(id, user_id, note_id, line, created_at, updated_at) FROM '/Users/jq/3tep_gomi/clients_1.csv' WITH CSV DELIMITER ','; \COPY clients(id, user_id, note_id, line, created_at, updated_at) FROM '/Users/jq/3tep_gomi/clients_2.csv' WITH CSV DELIMITER ','; ...
- - シーケンス編集
シーケンスの最大値確認 select max(id) from clients; シーケンスを最大値以上に設定 select setval ('clients_id_seq', 5128);
- - list_idにnullがあるとき
リスト「未分類」を作りそのidを入れる update clients set list_id = 102 where list_id is null;
Others[編集]
- 旧ClientsをClientsとOthersに分ける 寺ごと(ノートごと)に出し入れする
エクスポート \COPY (select user_id, jiin_id, ie_id, name, yomi, nusi, each_order, generation, created_at, updated_at from clients where jiin_id = 1 order by ie_id asc) TO '/Users/jq/3tep_gomi/others_1.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER; ...
インポート \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 ','; ...
おまけ[編集]
- エラー
- - Othersのclient_idがClientsのidにないとき
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".
- - others_1.csvから不要なclient_idを含む行の削除方法
作業用データベースを作る % 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を使ってClientsにないOthersのclient_idを拾う 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); others_1.csvをインポートする 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 ','; ClientsにないOthersのclient_idを含む行を削除する 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; tep3のOthersにインポートする tep3_development=# \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 ',';
Lists[編集]
- 旧Categoriesのset_id1を寺ごとに抜き出し Listsに入れる
エクスポート \COPY (select id, user_id, jiin_id, content, line, created_at, updated_at from categories where jiin_id = 1 and content <> '' and set_id = 1 order by line asc) TO '/Users/jq/3tep_gomi/list_1.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER; ...
インポート \COPY lists(id, user_id, note_id, content, line, created_at, updated_at) FROM '/Users/jq/3tep_gomi/list_1.csv' WITH CSV DELIMITER ','; ...
- - シーケンス編集
シーケンスの最大値確認 select max(id) from lists; シーケンスを最大値以上に設定 select setval ('lists_id_seq', 128);
ListsのidをClientsのlist_idに入れる[編集]
tepnote::DATABASE=> select * from categories where id = 70; id | user_id | jiin_id | content | set_id | created_at | updated_at | line ----+---------+---------+---------+--------+----------------------------+----------------------------+------ 70 | 1 | 1 | 別所 | 1 | 2021-06-16 02:13:29.941705 | 2021-06-30 23:36:32.003691 | 2
tepnote::DATABASE=> select * from properties where jiin_id = 1 and ie_id = 2597; id | user_id | jiin_id | client_id | ie_id | tiku_id | category_id | tana | tuki | nenga | yaku | eitai | name | created_at | updated_at | category_3 | category_4 | category_5 | nenkai_h | goji | haru | aki | yaku_g ----+---------+---------+-----------+-------+---------+-------------+------+------+-------+------+-------+------+----------------------------+----------------------------+------------+------------+------------+----------+------+------+-----+-------- 64 | 2 | 1 | 1060 | 2597 | 70 | 101 | t | f | t | t | f | f | 2019-03-07 16:16:19.197973 | 2021-08-25 23:47:50.265849 | | | | t | t | t | t | f
- 旧CategoriesとPropertiesをjoinしてie_id(新Clientsのidは旧ie_id)とlist_idを抜き出す
エクスポート \COPY (select properties.ie_id as id, categories.id as list_id from categories join properties on categories.id = properties.tiku_id where categories.jiin_id = 1 order by properties.ie_id asc) TO '/Users/jq/3tep_gomi/ie_id_list_id_1.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER;
- tep3_developmentデータベース内に作業用一時テーブルを作る(後で削除) Clientsのlist_idに上で抜き出したlist_idを入れるため
CREATE TABLE x_list_id (client_id int, list_id int);
インポート \COPY x_list_id(client_id, list_id) FROM '/Users/jq/3tep_gomi/ie_id_list_id_1.csv' WITH CSV DELIMITER ',';
- Clientsをアップデートしてlist_idを入れる
アップデート UPDATE clients SET list_id = x_list_id.list_id FROM x_list_id WHERE clients.id = x_list_id.client_id;
Categories[編集]
- 旧Categories set_id 2 が主なカテゴリ
* エクスポート \COPY (select id, user_id, jiin_id, content, line, created_at, updated_at from categories where jiin_id = 1 and set_id = 2 and content <> '' order by line asc) TO '/Users/jq/3tep_gomi/category_1_1.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER; ...
* インポート \COPY categories(id, user_id, note_id, content, line, created_at, updated_at) FROM '/Users/jq/3tep_gomi/category_1_1.csv' WITH CSV DELIMITER ',';
- - シーケンス編集
シーケンスの最大値確認 select max(id) from categories; シーケンスを最大値以上に設定 select setval ('categories_id_seq', 128);
id | user_id | note_id | content | line | defa | created_at | updated_at ----+---------+---------+---------+------+------+---------------------+--------------------- 1 | 1 | 1 | 檀家 | 1 | t | 2022-02-24 07:00:00 | 2022-02-24 07:00:00
Client_categories[編集]
エクスポート \COPY (select properties.user_id, properties.jiin_id, properties.ie_id, properties.category_id, properties.created_at, properties.updated_at from properties join categories on properties.category_id = categories.id where properties.jiin_id = 1 and categories.set_id = 2 order by ie_id asc, category_id asc) TO '/Users/jq/3tep_gomi/client_category_1_1.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER; ...
インポート \COPY client_categories(user_id, note_id, client_id, category_id, created_at, updated_at) FROM '/Users/jq/3tep_gomi/client_category_1_1.csv' WITH CSV DELIMITER ','; ...