MySQL의 Information_schema를 읽어서 PG용 DDL을 생성합니다.
-- MySQL startdb에 접속해 실행합니다.
-- CREATE TABLE 만들기(From MySQL, To PostgreSQL)
-- StartUP SQL MySQL 용 데이터베이스에 접속해 아래 SQL을 실행합니다.
WITH W1 AS(
SELECT T1.TABLE_NAME
,T2.COLUMN_NAME
,T2.IS_NULLABLE
,CASE WHEN T2.DATA_TYPE = 'datetime' THEN 'timestamp'
WHEN T2.DATA_TYPE in ('int','bigint') THEN 'decimal'
ELSE T2.DATA_TYPE END DATA_TYPE
,T2.CHARACTER_MAXIMUM_LENGTH
,T2.NUMERIC_PRECISION
,T2.NUMERIC_SCALE
,CASE WHEN T2.DATA_TYPE in ('date','datetime') THEN ''
WHEN T2.DATA_TYPE = 'varchar' THEN CONCAT('(',T2.CHARACTER_MAXIMUM_LENGTH,')')
WHEN T2.DATA_TYPE in ('int','bigint','numeric','decimal') THEN CONCAT('(',T2.NUMERIC_PRECISION,',',T2.NUMERIC_SCALE,')')
END DT_LEN
,T2.ORDINAL_POSITION POS
,MAX(T2.ORDINAL_POSITION) OVER(PARTITION BY T1.TABLE_NAME) LAST_POS
FROM INFORMATION_SCHEMA.TABLES T1
INNER JOIN INFORMATION_SCHEMA.COLUMNS T2
ON (T1.TABLE_CATALOG = T2.TABLE_CATALOG
AND T1.TABLE_SCHEMA = T2.TABLE_SCHEMA
AND T1.TABLE_NAME = T2.TABLE_NAME)
WHERE T1.TABLE_SCHEMA = 'startdb'
AND T1.TABLE_NAME NOT IN ('test_a','vw_itemfull','student','courseregistration')
)
,W2 AS(
SELECT CASE WHEN T1.POS = 1 THEN CONCAT('CREATE TABLE ',T1.TABLE_NAME,' (') ELSE ' ,' END CT
,CONCAT(LOWER(T1.COLUMN_NAME),' ',T1.DATA_TYPE,' ',T1.DT_LEN,' ',CASE WHEN T1.IS_NULLABLE = 'YES' THEN ' NULL ' ELSE ' NOT NULL ' END) COL
,CASE WHEN T1.POS = T1.LAST_POS THEN ');' ELSE '' END FIN
,T1.TABLE_NAME ,T1.POS
FROM W1 T1
ORDER BY T1.TABLE_NAME ,T1.POS
)
SELECT CONCAT(T2.CT, T2.COL, T2.FIN) DDL_SQL
FROM W2 T2
ORDER BY T2.TABLE_NAME ,T2.POS;
위 결과로 얻은 SQL(아래 참고)을 PG의 startdb에 접속해 실행합니다.
-- pg startdb에 접속해 실행합니다.
CREATE TABLE basecd (basecddv varchar (40) NOT NULL
,basecd varchar (40) NOT NULL
,basecdnm varchar (100) NULL
,sortorder decimal (10,0) NULL );
CREATE TABLE basecddv (basecddv varchar (40) NOT NULL
,basecddvnm varchar (100) NULL );
CREATE TABLE basedt (basedt date NOT NULL
,baseymd varchar (8) NOT NULL
,basedtseq decimal (10,0) NOT NULL
,basewkd varchar (10) NOT NULL );
CREATE TABLE event (eventid varchar (40) NOT NULL
,eventnm varchar (100) NOT NULL
,eventstartdtm timestamp NOT NULL
,eventenddtm timestamp NOT NULL );
CREATE TABLE evententry (eventid varchar (40) NOT NULL
,memberid varchar (40) NOT NULL
,entrydtm timestamp NOT NULL
,entryresultcd varchar (40) NOT NULL );
CREATE TABLE item (itemid varchar (40) NOT NULL
,itemnm varchar (100) NULL
,itemcat varchar (40) NULL
,itemsizecd varchar (40) NULL
,hotcoldcd varchar (40) NULL
,launchdt date NULL );
CREATE TABLE itemcat (itemcat varchar (40) NOT NULL
,itemcatnm varchar (100) NULL );
CREATE TABLE itemprchist (itemid varchar (40) NOT NULL
,fromdt date NOT NULL
,todt date NOT NULL
,saleprc decimal (18,3) NULL );
CREATE TABLE member (memberid varchar (40) NOT NULL
,nicknm varchar (100) NULL
,mobileno varchar (100) NULL
,email varchar (100) NULL
,joindtm timestamp NULL
,membergd varchar (40) NULL
,memberst varchar (40) NULL
,leavedtm timestamp NULL );
CREATE TABLE ord (ordno decimal (20,0) NOT NULL
,orddtm timestamp NULL
,preparecmpdtm timestamp NULL
,pickupdtm timestamp NULL
,memberid varchar (40) NULL
,shopid varchar (40) NULL
,ordst varchar (40) NULL
,ordamt decimal (18,3) NULL
,paytp varchar (40) NULL );
CREATE TABLE orddet (ordno decimal (20,0) NOT NULL
,orddetno decimal (10,0) NOT NULL
,itemid varchar (40) NULL
,ordqty decimal (10,0) NULL
,saleprc decimal (18,3) NULL );
CREATE TABLE shop (shopid varchar (40) NOT NULL
,shopnm varchar (100) NULL
,shopsize decimal (10,0) NULL
,shopopertp varchar (40) NULL
,tableqty decimal (10,0) NULL
,chairqty decimal (10,0) NULL
,opentime varchar (4) NULL
,closetime varchar (4) NULL
,shopst varchar (40) NULL
,shopstartymd varchar (8) NULL
,shopendymd varchar (8) NULL );
MySQL의 데이터를 Pg로 밀어 넣기
-
DBeaver를 이용, 왼쪽의 MySQL 네비게이션을 열어서, startdb아래에 이관할 테이블들을 선택합니다. (아래 그림 참고)
-
테이블을 선택후 마우스 오른쪽을 누르고 데이터내보내기 선택
-
아래 화면(Export target)에서 데이터베이스:테이블을 선택후 다음을 클릭
-
오른쪽 위에, Choose Target Catalog 의 폴더모양 버튼 선택
-
만들어놓은 postgresql-startdb 컨넥션 밑에 startdb 아래 schemas 아래 public을 선택후 확인
-
아래와 같이 existing이 나와야 합니다.
-
-
그 다음부터는 ‘다음’ 버튼으로 계속 넘어가고 마지막에 ‘진행’만 눌러주면 mysql로 구성한 startdb의 데이터를 pg에도 밀어 넣습니다.
MySQL의 PK 제약을 가져와서 pg에도 만들어줍니다.
-- MySQL에서 실행합니다.
SELECT *
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'information_schema';
WITH W1 AS(
SELECT LOWER(T1.TABLE_NAME) TABLE_NAME
,GROUP_CONCAT(LOWER(T1.COLUMN_NAME) ORDER BY T1.ORDINAL_POSITION) COLS
FROM information_schema.KEY_COLUMN_USAGE T1
WHERE T1.TABLE_SCHEMA = 'startdb'
AND T1.CONSTRAINT_NAME = 'PRIMARY'
AND T1.TABLE_NAME NOT IN ('student','courseregistration')
GROUP BY LOWER(T1.TABLE_NAME)
)
SELECT CONCAT('ALTER TABLE ',T1.TABLE_NAME, ' ADD CONSTRAINT ',T1.TABLE_NAME,'_pk PRIMARY KEY(',T1.COLS ,');') C_PK
FROM W1 T1;
-- PG에서 실행합니다.(PK생성)
ALTER TABLE basecd ADD CONSTRAINT basecd_pk PRIMARY KEY(basecddv,basecd);
ALTER TABLE basecddv ADD CONSTRAINT basecddv_pk PRIMARY KEY(basecddv);
ALTER TABLE basedt ADD CONSTRAINT basedt_pk PRIMARY KEY(basedt);
ALTER TABLE event ADD CONSTRAINT event_pk PRIMARY KEY(eventid);
ALTER TABLE evententry ADD CONSTRAINT evententry_pk PRIMARY KEY(eventid,memberid);
ALTER TABLE item ADD CONSTRAINT item_pk PRIMARY KEY(itemid);
ALTER TABLE itemcat ADD CONSTRAINT itemcat_pk PRIMARY KEY(itemcat);
ALTER TABLE itemprchist ADD CONSTRAINT itemprchist_pk PRIMARY KEY(itemid,fromdt);
ALTER TABLE member ADD CONSTRAINT member_pk PRIMARY KEY(memberid);
ALTER TABLE ord ADD CONSTRAINT ord_pk PRIMARY KEY(ordno);
ALTER TABLE orddet ADD CONSTRAINT orddet_pk PRIMARY KEY(ordno,orddetno);
ALTER TABLE shop ADD CONSTRAINT shop_pk PRIMARY KEY(shopid);
MySQL의 FK 제약을 가져와서 pg에도 만들어줍니다.
-- MySQL에서 실행합니다.(FK 생성 SQL 만들기)
WITH W1 AS(
SELECT LOWER(T1.CONSTRAINT_NAME) CONS_NAME
,LOWER(T1.TABLE_NAME) TABLE_NAME
,LOWER(T1.REFERENCED_TABLE_NAME) REF_TAB
,GROUP_CONCAT(LOWER(T1.COLUMN_NAME) ORDER BY T1.ORDINAL_POSITION) COLS
,GROUP_CONCAT(LOWER(T1.REFERENCED_COLUMN_NAME) ORDER BY T1.ORDINAL_POSITION) REF_COLS
FROM information_schema.KEY_COLUMN_USAGE T1
WHERE T1.TABLE_SCHEMA = 'startdb'
AND T1.CONSTRAINT_NAME != 'PRIMARY'
AND T1.TABLE_NAME NOT IN ('student','courseregistration')
GROUP BY LOWER(T1.TABLE_NAME)
,LOWER(T1.REFERENCED_TABLE_NAME)
,LOWER(T1.CONSTRAINT_NAME)
)
SELECT CONCAT('ALTER TABLE ',T1.TABLE_NAME, ' ADD CONSTRAINT ',T1.CONS_NAME,' FOREIGN KEY(',T1.COLS ,') REFERENCES ',T1.REF_TAB,'(',T1.REF_COLS,');') C_FK
FROM W1 T1;
-- PG에서 실행합니다. FK 생성
ALTER TABLE basecd ADD CONSTRAINT basecd_fk01 FOREIGN KEY(basecddv) REFERENCES basecddv(basecddv);
ALTER TABLE evententry ADD CONSTRAINT evententry_fk01 FOREIGN KEY(eventid) REFERENCES event(eventid);
ALTER TABLE evententry ADD CONSTRAINT evententry_fk02 FOREIGN KEY(memberid) REFERENCES member(memberid);
ALTER TABLE item ADD CONSTRAINT item_fk01 FOREIGN KEY(itemcat) REFERENCES itemcat(itemcat);
ALTER TABLE itemprchist ADD CONSTRAINT itemprchist_fk01 FOREIGN KEY(itemid) REFERENCES item(itemid);
ALTER TABLE ord ADD CONSTRAINT ord_fk01 FOREIGN KEY(memberid) REFERENCES member(memberid);
ALTER TABLE ord ADD CONSTRAINT ord_fk02 FOREIGN KEY(shopid) REFERENCES shop(shopid);
ALTER TABLE orddet ADD CONSTRAINT orddetail_fk02 FOREIGN KEY(itemid) REFERENCES item(itemid);
ALTER TABLE orddet ADD CONSTRAINT orddetail_fk01 FOREIGN KEY(ordno) REFERENCES ord(ordno);
이제 PG에 MySQL용 StartDB와 똑같은 데이터 셋이 만들어졌습니다. 연습으로 SQL 한번 실행해보죠. 잘 되네요.
select t1.memberid ,t3.nicknm ,t1.sumordamt
from (
select b.memberid
,sum(b.ordamt) sumordamt
from shop a
inner join ord b
on (a.shopid = b.shopid)
where a.shopstartymd = '20180327'
and b.orddtm >= TO_DATE('20200101','YYYYMMDD')
and b.orddtm < TO_DATE('20200201','YYYYMMDD')
group by b.memberid
) t1
inner join member t3
on (t3.memberid = t1.memberid)
where t3.membergd = 'PLAT'
order by t1.memberid;
memberid|nicknm |sumordamt|
--------+---------+---------+
M0291 |Star5 |28500.000|
M0298 |Thunder5 |10500.000|
M1283 |Quantum25|12000.000|
M1297 |Swift25 |15500.000|