[ORACLE] [POSTGRESQL] oracle to postgresql

정말 쓸데없는 썰인데.
참고로 말하자면 난 요즘 도마뱀에 관심이 많다.

/***** S : POSTGRESQL 스키마 생성 (Ver. POSTGRESQL 13)**********************************************************/

CREATE TABLE T_GECKO
   (   SEQ int not null primary key,                   
      name varchar(50),
      kr_name varchar(50),
      "position" VARCHAR(20),
      "SORT" NUMERIC(5,0),
      Life Varchar(50),
      origin varchar(50),
      desCription teXt,
      humidity varchar(50),
      temperature varchar(50),
      rearing varchar(100),
      reg_date timestamp(0) default now() 
   );
COMMENT ON COLUMN T_GECKO.SEQ IS '일련번호';
COMMENT ON COLUMN T_GECKO.name IS '이름';
COMMENT ON COLUMN T_GECKO.kr_name IS '한국식 이름';
COMMENT ON COLUMN T_GECKO."position" IS '건식일까 습식일까 말고는 뭐가 있나 등';
COMMENT ON COLUMN T_GECKO."SORT" IS '내 마음의 우선순위';
COMMENT ON COLUMN T_GECKO.Life IS '오래 오래 살아라';
COMMENT ON COLUMN T_GECKO.origin IS '출신이 어디야';
COMMENT ON COLUMN T_GECKO.desCription IS '나에 대해 나열해보아라';
COMMENT ON COLUMN T_GECKO.humidity IS '피부를 촉촉하게';
COMMENT ON COLUMN T_GECKO.temperature IS '녹지도 얼지도 마라';
COMMENT ON COLUMN T_GECKO.rearing IS '내 집 마련';
COMMENT ON COLUMN T_GECKO.reg_date IS '구글링 날짜';

CREATE SEQUENCE GECKO_SEQ  MINVALUE 1 MAXVALUE 99999 INCREMENT BY 1 START WITH 1 CACHE 20  NO CYCLE ;
;

INSERT INTO t_gecko
(seq, "name", kr_name, "position", "SORT", life, origin, description, humidity, temperature, rearing)
VALUES(1, 'crested gecko', '볏도마뱀붙이', '습식', 1, '15~30년', '뉴칼레도니아', '외래종 미친개미에 의한 피해로 야생 개체수는 적은 편', '50~70%', '20~27도', '적절한 환경');
INSERT INTO t_gecko
(seq, "name", kr_name, "position", "SORT", life, origin, description, humidity, temperature, rearing)
VALUES(2, 'fattail gecko', '아프리카살찐꼬리도마뱀붙이', '습식', 2, '10~25년', '세네갈에서 카메룬까지의 서아프리카', '아프리카 팻테일은 레오파드 게코보다 성질이 순하지만 습도를 더 높게 맞춰줘야 한다.', '바닥재 50~70%', '쿨존-23~26도, 핫존-31~33도', '적절한 환경');
INSERT INTO t_gecko
(seq, "name", kr_name, "position", "SORT", life, origin, description, humidity, temperature, rearing)
VALUES(3, 'leopard gecko', '표범도마뱀붙이', '건식', 3, '15년', '서남아시아 원산의 사막', NULL, '20~40%', '쿨존-25도, 핫존-30~33도', NULL);

/***** E : POSTGRESQL 스키마 생성 (Ver. POSTGRESQL 13)**********************************************************/
;


0. postgre 의 대문자/소문자
   * 모든 DDL은 생성하면서 소문자로 치환되지만, 대문자나 예약어 모두 사용가능하다.
   * keyword : 쌍따움표[""]
   * 쌍따옴표를 이용한 DDL은 컬럼뿐만 아니라 함수나 프로시져, 테이블에도 적용된다.
   * 예약어 position과 대문자 SORT로 컬럼을 생성하였다.
      select position from t_gecko;         (O)
      select "position" from t_gecko;         (O)
      select POSITION from t_gecko;         (O)
      select "POSITION" from t_gecko;         (X)
      
      select sort from t_gecko;            (X)
      select "sort" from t_gecko;            (X)
      select SORT from t_gecko;            (X)
      select "SORT" from t_gecko;            (O)

1. dual 테이블의 사용.
   [oracle] : 반드시 테이블이 필요함. 기본 제공 테이블인 dual이라도 사용해야 함.
     select sysdate 
       from dual;
   [postgre] : dual 테이블 없이 그냥 조회 가능
     select now();

2. NULL 처리
   [oralce]  
     select NVL(description, '[[귀여움주의]]') 
       from t_gecko;
   [postgre]
     select coalesce(description, '[[귀여움주의]]') 
       from t_gecko;

3. DB 시스템 시간
   [oracle]
     select sysdate 
       from dual;
   [postgre] : now() 를 주로 씀.
     select current_timestamp, now();

4. 날짜 계산(월 단위 ADD)
   [oracle]
     select add_months(to_date('202108', 'YYYYMM'), 1) from dual;               //한달 후
     select add_months(to_date('202108', 'YYYYMM'), -2) from dual;               //2달 전
   [postger] - interval 의 위치는 아래의 두 경우 모두 가능함.
     select to_date('202108', 'YYYYMM') + interval '1 months';                  //한달 후
     select to_date('202108', 'YYYYMM') + '-2 months'::interval;               //2달 전

5. 날짜 계산(일 단위 ADD) - trunc 사용시
   [oracle]
     select trunc(sysdate-1) from dual;                                 //하루 전
     select trunc(sysdate) from dual;                                    //오늘
   [postgre]
     select date_trunc('day', now() - '1 day'::interval) from dual;            //하루 전
     select date_trunc('day', now()::timestamp)                           //오늘

6. 날짜 계산(년, 월, 일, 시, 분, 초)
   [oracle]
     select sysdate + (interval '2' year) from dual;                        //2년 후
     select add_months(sysdate, 2) from dual;                              //2달 후
     select sysdate + (interval '2' month) from dual;                        //2달 후
     select sysdate + 2 from dual;                                       //2일 후
     select sysdate + (interval '2' day) from dual;                        //2일 후
     select sysdate + 2/24 from dual;                                    //2시간 후
     select sysdate + (interval '2' hour) from dual;                        //2시간 후
     select sysdate + 2/(24*60) from dual;                                 //2분 후
     select sysdate + (interval '2' minute) from dual;                        //2분 후
     select sysdate + 2/(24*60*60) from dual;                              //2초 후
     select sysdate + (interval '2' second) from dual;                        //2초 후
   [postgre]
     select now() + '2 years'::interval;                                 //2년 후
     select date_trunc('year', now() + '2 years'::interval);                  //2년 후(월일시분초 초기화)
     select now() + '2 months'::interval;                                 //2달 후
     select date_trunc('month', now() + '2 months'::interval);                  //2달 후(일시분초 초기화)
     select now() + '2 days'::interval;                                 //2일 후
     select date_trunc('day', now() + '2 days'::interval);                     //2일 후(시분초 초기화)
     select now() + '2 hours'::interval;                                 //2시간 후
     select date_trunc('hour', now() + '2 hours'::interval);                  //2시간 후(분초 초기화)
     select now() + '2 minutes'::interval;                                 //2분 후
     select date_trunc('minute', now() + '2 minutes'::interval);               //2분 후(초 초기화)
     select now() + '2 seconds'::interval;                                 //2초 후

7. 날짜 계산(일 수 차이 from 현재일) :: reg_date가 오늘인 데이터 조회
   [oracle]
     select *
       from t_gecko 
      where trunc(reg_date - sysdate) = 0;
   [postgre]
     select *
       from t_gecko
      where date_trunc('day', reg_date::timestamp) - date_trunc('day' , current_timestamp) = '0 days';

8. 데이터 조회 제한
   [oracle]
     select *
       from t_gecko
      where ROWNUM <= 2
   [postgre]
     select *
       from t_gecko
      limit 2

10. sequence nextval
   [oracle]
     select GECKO_SEQ.NEXTVAL from dual;
   [postgre]
     select nextval('gecko_seq');
     select nextval('GECKO_SEQ');
     select NEXTVAL('gecko_seq');
     select NEXTVAL('GECKO_SEQ');

11. 문자 채우기
   [oracle]
     select LPAD (TO_CHAR (sort), 4, '0') from t_gecko;         //왼쪽부터
     select RPAD (TO_CHAR (sort), 4, '0') from t_gecko;         //오른쪽부터
   [postgre]
     select LPAD ("SORT"::varchar, 4, '0') from t_gecko;         //왼쪽부터
     select RPAD ("SORT"::varchar, 4, '0') from t_gecko;         //오른쪽부터

12. paging - between
   [oracle] : from 절에 괄호가 들어가도 alias가 꼭 필요한 건 아니다.
     select *
       from ( select rownum as rnum
                , tg.*
              from t_gecko tg
            ) 
      where rnum <= 1;                                    // 1번 조회
      --where rnum >= 2 and rnum <= 3;                        // 2 ~ 3 번 조회
      --where rnum >= 3 and rnum <=5;                        // 3 ~ 5 번 조회
   [postgre] : from 절에 괄호가 들어가면 반드시 alias가 필요하다. 
     select A.*
       from ( select *
              from t_gecko 
           ) A
      limit 1 offset 0   ;                                 // 1(offset+1) 번부터 1건 조회
      --limit 2 offset 1                                 // 2(offset+1) 번부터 2건 조회
      --limit 3 offset 2   ;                              // 3(offset+1) 번부터 3건 조회

13. merge [postgre]
  13-1. 중복키 update 할 때
    insert into t_gecko ( seq, reg_date ) 
   values ( 1 , now() )
   on conflict(seq) do 
   update set reg_date = now();
  13-2. insert만 할 때
    insert into t_gecko ( seq, reg_date ) 
   values ( 1 , now() )
   on conflict(seq) do nothing;
  13-3. merge의 다른 방법. (keyword : upsert)
    with aa as 
   (
      update t_gecko
         set reg_date = now()
       where seq = 1
       returning *
   )
   insert into t_gecko 
   ( seq, reg_date )
   select 1, now()
    where not exists(select * from aa);

14. type cast from type error
  * 여러가지 방법이 있지만 '::varchar'와 같은 방법이 제일 편해서 한가지만 적어둠.
  * mybatis나 ibatis에서의 cast 오류는 대부분 이걸로 해결된다.
   int_val::int
   double_val::numeric
   var_val::varchar
;