[MSSQL to PostgreSQL] Migration 변환 작업

DataType Mapping

posgreSQL	MSSQL	Description
BIGINT	BIGINT	64-bit integer
BOOLEAN	BOOLEAN	1, 0 or NULL
BYTEA	BINARY(n) IMAGE ROWVERSION TIMESTAMP VARBINARY(n) VARBINARY(max)	binary data (“byte array”)
CHARACTER(n)	CHAR(n), CHARACTER(n)	fixed-length character string
DATE	DATE	Date includes year, month, and day
DOUBLE PRECISION (Alias FLOAT8)	DOUBLE PRECISION FLOAT(p)	double precision floating-point number (8 bytes)
INTEGER	INT, INTEGER	32-bit integer
MONEY	MONEY SMALLMONEY	currency amount
NUMERIC (Alias DECIMAL(p,s))	NUMERIC(p,s) DECIMAL(p,s), DEC(p,s)	exact numeric of selectable precision
POINT, LINE, LSEG, BOX, PATH, POLYGON, CIRCLE	GEOMETRY	Geometric types
REAL	REAL	Single-precision floating-point number
SMALLINT	SMALLINT TINYINT	signed two-byte integer
TEXT	NTEXT NVARCHAR(max) TEXT VARCHAR(max)	Variable length char string, <= 2GB
TIME(p)	TIME(p)	Time (hour, minute, second and fraction)
TIMESTAMP(0)	SMALLDATETIME	Date and time
TIMESTAMP(3)	DATETIME	Date and time with fraction
TIMESTAMP(p)	DATETIME2(p)	Date and time with fractional seconds
TIMESTAMP(p) WITH TIME ZONE (Alias timestamptz)	DATETIMEOFFSET(p)	date and time, including time zone
VARCHAR(n)	NVARCHAR(n) VARCHAR(n)	Variable length char string, 1 <= n <= 8000
XML	XML	XML data

 

Migration 작업 진행 순서

1. pom.xml에 postgreSQL maven 추가함

<!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.3.1</version>
</dependency>

2. MSSQL 스키마 변환

    1) NVARCHAR, VARCHAR  =>  VARCHAR

    2) VARCHAR(max)  =>  TEXT

    3) DATETIME  =>  TIMESTAMP(3)

    등등....

3. MSSQL 함수 변환

    1) MSSQL의 GETDATE()

         =>  postgreSQL의 NOW()

    2) OFFSET ROWS FETCH NEXT

          =>  LIMIT OFFSET

    3) DATEDIFF

          => DATE_PART

    등등....

4. Database.xml에서 변경된 username,password 변경

 

 

* 주의사항 및 기타 수정사항

1. POSTGRESQL은 Alias(as) 뒤에 ‘’와 “”의 사용이 다르기때문에 ‘’→””로 수정함 (AS ‘A’ ⇒ AS “A”)

2. ${}로 받는값에 대한 데이터 형식 선언해줘야함(숫자,문자 혼동) → ::VARCHAR

3. MSSQL은 STRING,DATE 연산가능 // POSTGRESQL STRING,DATE 연산X

    (형변환 오류 = ::INTEGER)

4. XML 수정

AND VIEW_ID LIKE  '%' + #{pageId, jdbcType=VARCHAR} + '%’
→ AND VIEW_ID LIKE CONCAT('%',#{pageId, jdbcType=VARCHAR},'%')

CONVERT(CHAR(19), UPDATE_DATE, 20) AS UPDATE_DATE
→ SUBSTRING(CAST(UPDATE_DATE as varchar),0,20) AS UPDATE_DATE

AND SUBSTRING(CAST(UPDATE_DATE AS VARCHAR),0,120) >= SUBSTRING(CAST(#{fromDate} as varchar),0,120)
→ AND substring(cast(UPDATE_DATE as VARCHAR),0,11)::DATE >= to_timestamp(#{fromDate, jdbcType=VARCHAR},'YYYY-MM-DD')

5. CONVERT → TO_CHAR

    LEN → LENGTH (문자열 길이)

    STUFF → OVERLAY (문자열 치환)

    CHARINDEX → POSITION (특정 문자 찾기)

    DATEDIFF → DATE_PART

 

 

 

 

(2022년 신입때 작업한 내용)