最新版 |
編集中の文章 |
28行目: |
28行目: |
| <nowiki> h1= title | | <nowiki> h1= title |
| p Welcome to #{title}</nowiki> | | p Welcome to #{title}</nowiki> |
− |
| |
− | == てぷノートからデータベース移行 ==
| |
− | <nowiki>jq@ubuntu:~/tep5$ psql tep5_production -W -c "COPY (select * from fam_others_client_district_class_kai_addr2 where note_id = 1) TO STDOUT WITH CSV HEADER" > ~/csv-back/others_jq.csv
| |
− | Password:</nowiki>
| |
− |
| |
− | <nowiki>CREATE TABLE familymembers AS
| |
− | SELECT
| |
− | clients.id,
| |
− | clients.note_id,
| |
− | lists.content AS lists_content,
| |
− | lists.list_line,
| |
− | clients.content AS clients_content,
| |
− | clients.client_line,
| |
− | clients.memo
| |
− | FROM
| |
− | clients
| |
− | JOIN
| |
− | lists ON clients.list_id = lists.id;
| |
− |
| |
− | CREATE TABLE familymembers2 AS
| |
− | SELECT
| |
− | f.client_id,
| |
− | f.note_id,
| |
− | f.lists_content,
| |
− | f.list_line,
| |
− | f.clients_content,
| |
− | f.client_line,
| |
− | f.memo,
| |
− | f.id,
| |
− | c.content AS categories_content,
| |
− | c.category_line
| |
− | FROM
| |
− | familymembers f
| |
− | JOIN
| |
− | relationship_categories rc ON f.client_id = rc.client_id
| |
− | JOIN
| |
− | categories c ON rc.category_id = c.id
| |
− | WHERE
| |
− | c.content IN ('檀家', '信徒', '離檀', '絶家', '不明', '一般', '寺院', '他檀家', '業者');
| |
− |
| |
− | SELECT *
| |
− | FROM familymembers2
| |
− | WHERE client_id IN (
| |
− | SELECT client_id
| |
− | FROM familymembers2
| |
− | GROUP BY client_id
| |
− | HAVING COUNT(*) > 1
| |
− | );
| |
− |
| |
− | CREATE TABLE familymembers3 AS
| |
− | SELECT f.*, p.zip, p.ken, p.city, p.etc, p.flag, p.memo AS places_memo
| |
− | FROM familymembers2 f
| |
− | LEFT JOIN places p ON f.client_id = p.client_id;
| |
− |
| |
− | ——————————————————————————————————
| |
− | CREATE TABLE places_flag_true AS
| |
− | SELECT id, note_id, client_id, zip, ken, city, etc, flag, memo
| |
− | FROM places
| |
− | WHERE client_id IN (
| |
− | SELECT client_id
| |
− | FROM places
| |
− | GROUP BY client_id
| |
− | HAVING COUNT(*) > 1
| |
− | ) AND flag = true;
| |
− |
| |
− | CREATE TABLE places_flag_false AS
| |
− | SELECT client_id, zip, ken, city, etc, flag, memo
| |
− | FROM places
| |
− | WHERE client_id IN (
| |
− | SELECT client_id
| |
− | FROM places
| |
− | GROUP BY client_id
| |
− | HAVING COUNT(*) > 1
| |
− | ) AND flag = false;
| |
− |
| |
− | DELETE FROM places_flag_false
| |
− | WHERE etc = '';
| |
− | DELETE 28
| |
− |
| |
− | tep5_production=# select count(*) from places_flag_false ;
| |
− | count
| |
− | -------
| |
− | 93
| |
− |
| |
− | CREATE TABLE places_multi_flag_false_no_multi AS
| |
− | SELECT client_id, zip, ken, city, etc, flag, memo
| |
− | FROM places_flag_false
| |
− | WHERE client_id IN (
| |
− | SELECT client_id
| |
− | FROM places_flag_false
| |
− | GROUP BY client_id
| |
− | HAVING COUNT(*) = 1
| |
− | );
| |
− | SELECT 79
| |
− |
| |
− | CREATE TEMPORARY TABLE places_1_2 AS
| |
− | SELECT p2.*, t.zip AS zip2, t.ken AS ken2, t.city AS city2, t.etc AS etc2, t.flag AS flag2, t.memo AS memo2
| |
− | FROM places_flag_true p2
| |
− | LEFT JOIN temp2 t ON p2.client_id = t.client_id;
| |
− |
| |
− | 重複を分ける
| |
− | CREATE TEMPORARY TABLE temp3 AS
| |
− | SELECT client_id, zip, ken, city, etc, flag, memo
| |
− | FROM temp
| |
− | WHERE client_id IN (
| |
− | SELECT client_id
| |
− | FROM temp
| |
− | GROUP BY client_id
| |
− | HAVING COUNT(*) > 1
| |
− | );
| |
− |
| |
− | CREATE TEMPORARY TABLE temp4 AS
| |
− | SELECT * FROM temp3
| |
− | ORDER BY client_id;
| |
− |
| |
− | -- 偶数IDのテーブルを作成
| |
− | CREATE TABLE temp4_even AS
| |
− | SELECT * FROM pg_temp_3.temp4
| |
− | WHERE id % 2 = 0;
| |
− |
| |
− | -- 奇数IDのテーブルを作成
| |
− | CREATE TABLE temp4_odd AS
| |
− | SELECT * FROM pg_temp_3.temp4
| |
− | WHERE id % 2 != 0;
| |
− |
| |
− | CREATE TABLE places_cp AS
| |
− | SELECT * FROM places;
| |
− |
| |
− | SELECT id, note_id, client_id, zip, ken, city, etc, flag, memo
| |
− | FROM places_cp
| |
− | WHERE client_id IN (
| |
− | SELECT client_id
| |
− | FROM places_cp
| |
− | GROUP BY client_id
| |
− | HAVING COUNT(*) > 1
| |
− | )
| |
− | places_cpから重複した行を削除したい。
| |
− |
| |
− | places_cpから
| |
− | SELECT client_id, array_agg(id) AS ids
| |
− | FROM places_cp
| |
− | GROUP BY client_id
| |
− | HAVING COUNT(*) > 1;の結果を削除したい。
| |
− |
| |
− | places_multi_flag_false_no_multiテーブルにtemp4_even
| |
− | INSERT INTO places_multi_flag_false_no_multi (client_id, zip, ken, city, etc, flag, memo)
| |
− | SELECT client_id, zip, ken, city, etc, flag, memo
| |
− | FROM temp4_even;
| |
− |
| |
− | ————————————————————————————————
| |
− |
| |
− | CREATE TABLE public.fam_others_client_district_class (
| |
− | id bigint,
| |
− | note_id bigint,
| |
− | client_id bigint,
| |
− | client_name boolean,
| |
− | name character varying,
| |
− | kana character varying,
| |
− | other_line integer,
| |
− | memo_other text,
| |
− | generation character varying,
| |
− | lists_content character varying,
| |
− | list_line integer,
| |
− | client_line integer,
| |
− | categories_content character varying,
| |
− | category_line integer,
| |
− | memo_client text
| |
− | );
| |
− |
| |
− | INSERT INTO public.fam_others_client_district_class
| |
− | SELECT
| |
− | o.id,
| |
− | o.note_id,
| |
− | o.client_id,
| |
− | o.client_name,
| |
− | o.name,
| |
− | o.kana,
| |
− | o.other_line,
| |
− | o.memo,
| |
− | o.generation,
| |
− | CASE WHEN o.client_name = true THEN f.lists_content ELSE NULL END,
| |
− | CASE WHEN o.client_name = true THEN f.list_line ELSE NULL END,
| |
− | CASE WHEN o.client_name = true THEN f.client_line ELSE NULL END,
| |
− | CASE WHEN o.client_name = true THEN f.categories_content ELSE NULL END,
| |
− | CASE WHEN o.client_name = true THEN f.category_line ELSE NULL END,
| |
− | CASE WHEN o.client_name = true THEN f.memo ELSE NULL END
| |
− | FROM public.others o
| |
− | LEFT JOIN public.fam_client_district_class f ON o.client_id = f.client_id;
| |
− |
| |
− | othersのすべての行を挿入したい。その中でclient_name = trueの行には f.lists_content, f.list_line, f.client_line, f.categories_content, f.category_line, f.memoを追加したい。
| |
− |
| |
− | ———————————————————————————————————
| |
− |
| |
− | Table "public.fam_others_client_district_class"
| |
− | Column | Type | Collation | Nullable | Default
| |
− | --------------------+-------------------+-----------+----------+---------
| |
− | id | bigint | | |
| |
− | note_id | bigint | | |
| |
− | client_id | bigint | | |
| |
− | client_name | boolean | | |
| |
− | name | character varying | | |
| |
− | kana | character varying | | |
| |
− | other_line | integer | | |
| |
− | memo_other | text | | |
| |
− | generation | character varying | | |
| |
− | lists_content | character varying | | |
| |
− | list_line | integer | | |
| |
− | client_line | integer | | |
| |
− | categories_content | character varying | | |
| |
− | category_line | integer | | |
| |
− | memo_client | text | | |
| |
− |
| |
− | Table "public.kaimyous"
| |
− | Column | Type | Collation | Nullable | Default
| |
− | --------------+--------------------------------+-----------+----------+--------------------------------------
| |
− | id | bigint | | not null | nextval('kaimyous_id_seq'::regclass)
| |
− | user_id | bigint | | not null |
| |
− | note_id | bigint | | not null |
| |
− | client_id | bigint | | not null |
| |
− | other_id | bigint | | not null |
| |
− | content | character varying | | |
| |
− | birth | date | | |
| |
− | death | date | | |
| |
− | g_age | character varying | | |
| |
− | relationship | character varying | | |
| |
− | memo | text | | |
| |
− |
| |
− | public.fam_others_client_district_classにpublic.kaimyousをLEFT JOINし、public.fam_others_client_district_classにcontent, birth, death, g_age, relationship, memo列を追加したい。
| |
− |
| |
− | ALTER TABLE public.fam_others_client_district_class
| |
− | ADD COLUMN kaimyou character varying,
| |
− | ADD COLUMN birth date,
| |
− | ADD COLUMN death date,
| |
− | ADD COLUMN g_age character varying,
| |
− | ADD COLUMN relationship character varying,
| |
− | ADD COLUMN memo_kaimyous text;
| |
− |
| |
− | UPDATE public.fam_others_client_district_class f
| |
− | SET kaimyou = k.content,
| |
− | birth = k.birth,
| |
− | death = k.death,
| |
− | g_age = k.g_age,
| |
− | relationship = k.relationship,
| |
− | memo_kaimyous = k.memo
| |
− | FROM public.kaimyous k
| |
− | WHERE f.id = k.other_id;
| |
− |
| |
− | UPDATE 3838
| |
− |
| |
− | tep5_production=# select count(*) from kaimyous;
| |
− | count
| |
− | -------
| |
− | 3842
| |
− |
| |
− |
| |
− |
| |
− | ——————————————————————————————————
| |
− |
| |
− | Table "public.fam_others_client_district_class"
| |
− | Column | Type | Collation | Nullable | Default
| |
− | --------------------+-------------------+-----------+----------+---------
| |
− | id | bigint | | |
| |
− | note_id | bigint | | |
| |
− | client_id | bigint | | |
| |
− | client_name | boolean | | |
| |
− | name | character varying | | |
| |
− | kana | character varying | | |
| |
− | other_line | integer | | |
| |
− | memo_other | text | | |
| |
− | generation | character varying | | |
| |
− | lists_content | character varying | | |
| |
− | list_line | integer | | |
| |
− | client_line | integer | | |
| |
− | categories_content | character varying | | |
| |
− | category_line | integer | | |
| |
− | memo_client | text | | |
| |
− | kaimyou | character varying | | |
| |
− | birth | date | | |
| |
− | death | date | | |
| |
− | g_age | character varying | | |
| |
− | relationship | character varying | | |
| |
− | memo_kaimyous | text | | |
| |
− |
| |
− |
| |
− | tep5_production=# \d places_cp
| |
− | Table "places_c”p
| |
− | Column | Type | Collation | Nullable | Default
| |
− | -----------+-------------------+-----------+----------+---------
| |
− | id | bigint | | |
| |
− | note_id | bigint | | |
| |
− | client_id | bigint | | |
| |
− | zip | character varying | | |
| |
− | ken | character varying | | |
| |
− | city | character varying | | |
| |
− | etc | character varying | | |
| |
− | flag | boolean | | |
| |
− | memo | text | | |
| |
− | zip2 | character varying | | |
| |
− | ken2 | character varying | | |
| |
− | city2 | character varying | | |
| |
− | etc2 | character varying | | |
| |
− | flag2 | boolean | | |
| |
− | memo2 | text | | |
| |
− | zip3 | character varying | | |
| |
− | ken3 | character varying | | |
| |
− | city3 | character varying | | |
| |
− | etc3 | character varying | | |
| |
− | flag3 | boolean | | |
| |
− | memo3 | text | | |
| |
− |
| |
− | public.fam_others_client_district_classにplaces_1_2_3をLEFT JOIN したい。
| |
− | client_idが同じで、さらに、client_nameがtrueの場合は、
| |
− | 次の値を追加したい。
| |
− | zip ken city etc flag memo zip2 ken2 city2 etc2 flag2 memo2 zip3 ken3 city3 etc3 flag3 memo3
| |
− |
| |
− |
| |
− | public.fam_others_client_district_classのすべての行を結果に反映し、新しいテーブルをつくりたい。
| |
− |
| |
− | CREATE TABLE fam_others_client_district_class_kai_addr2 AS
| |
− | SELECT f.*,
| |
− | COALESCE(p.zip, '') AS zip,
| |
− | COALESCE(p.ken, '') AS ken,
| |
− | COALESCE(p.city, '') AS city,
| |
− | COALESCE(p.etc, '') AS etc,
| |
− | COALESCE(p.flag, false) AS flag,
| |
− | COALESCE(p.memo, '') AS memo,
| |
− | COALESCE(p.zip2, '') AS zip2,
| |
− | COALESCE(p.ken2, '') AS ken2,
| |
− | COALESCE(p.city2, '') AS city2,
| |
− | COALESCE(p.etc2, '') AS etc2,
| |
− | COALESCE(p.flag2, false) AS flag2,
| |
− | COALESCE(p.memo2, '') AS memo2,
| |
− | COALESCE(p.zip3, '') AS zip3,
| |
− | COALESCE(p.ken3, '') AS ken3,
| |
− | COALESCE(p.city3, '') AS city3,
| |
− | COALESCE(p.etc3, '') AS etc3,
| |
− | COALESCE(p.flag3, false) AS flag3,
| |
− | COALESCE(p.memo3, '') AS memo3
| |
− | FROM public.fam_others_client_district_class AS f
| |
− | LEFT JOIN places_cp AS p
| |
− | ON f.client_id = p.client_id
| |
− | AND f.client_name = true;
| |
− |
| |
− | SELECT 5558</nowiki>
| |
| | | |
| == 設計 == | | == 設計 == |