3,765
回編集
差分
→転送・起動・Nodeバージョン Git
<nowiki> h1= title
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>
== 設計 ==