[DATABASE/POSTGRESQL] 마이그레이션 : SQL Server → PostgreSQL

▶ 데이터 타입

SQL Server		PostgreSQL
BIGINT	64-bit integer	BIGINT
BINARY(n)	Fixed-length byte string	BYTEA
BIT	1, 0 or NULL	BOOLEAN
CHAR(n), CHARACTER(n)	Fixed-length character string, 1 ⇐ n ⇐ 8000	CHAR(n), CHARACTER(n)
DATE	Date (year, month and day)	DATE
DATETIME	Date and time with fraction	TIMESTAMP(3)
DATETIME2(p)	Date and time with fraction	TIMESTAMP(p)
DATETIMEOFFSET(p)	Date and time with fraction and time zone	TIMESTAMP(p) WITH TIME ZONE
DECIMAL(p,s), DEC(p,s)	Fixed-point number	DECIMAL(p,s), DEC(p,s)
DOUBLE PRECISION	Double-precision floating-point number	DOUBLE PRECISION
FLOAT(p)	Floating-point number	DOUBLE PRECISION
IMAGE	Variable-length binary data, ⇐ 2G	BYTEA
INT, INTEGER	32-bit integer	INT, INTEGER
MONEY	64-bit currency amount	MONEY
NCHAR(n)	Fixed-length Unicode UCS-2 string	CHAR(n)
NTEXT	Variable-length Unicode UCS-2 data, ⇐ 2G	TEXT
NUMERIC(p,s)	Fixed-point number	NUMERIC(p,s)
NVARCHAR(n)	Variable-length Unicode UCS-2 string	VARCHAR(n)
NVARCHAR(max)	Variable-length Unicode UCS-2 data, ⇐ 2G	TEXT
REAL	Single-precision floating-point number	REAL
ROWVERSION	Automatically updated binary data	BYTEA
SMALLDATETIME	Date and time	TIMESTAMP(0)
SMALLINT	16-bit integer	SMALLINT
SMALLMONEY	32-bit currency amount	MONEY
TEXT	Variable-length character data, ⇐ 2G	TEXT
TIME(p)	Time (hour, minute, second and fraction)	TIME(p)
TIMESTAMP	Automatically updated binary data	BYTEA
TINYINT	8-bit unsigned integer, 0 to 255	SMALLINT
UNIQUEIDENTIFIER	16-byte GUID (UUID) data	CHAR(16)
VARBINARY(n)	Variable-length byte string, 1 ⇐ n ⇐ 8000	BYTEA
VARBINARY(max)	Variable-length binary data, ⇐ 2G	BYTEA
VARCHAR(n)	Variable-length character string, 1 ⇐ n ⇐ 8000	VARCHAR(n)
VARCHAR(max)	Variable-length character data, ⇐ 2G	TEXT
XML	XML data	XML

 

▶ 함수

SQL Server		PostgreSQL
DATEADD	Add an interval to datetime	INTERVAL expression
ISNULL(exp, replacement)	Replace NULL with the specified value	COALESCE(exp, replacement)