본문 바로가기

DB/PostgresQL

mssql to postgresql migration

Data Type

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

Function

 

 
MSSQL
PostgreSQL
DATEPART
DATEPART( datepart , date )
date_part( text , timestamp )
   
date_part( text , interval )
ISNULL
ISNULL(exp, replacement)
COALESCE(exp, replacement)
SPACE
SPACE($n)
REPEAT(‘ ’, $n)
DATEADD
SELECT DATEADD(day, 2, GETDATE());
SELECT CURRENT_DATE + INTERVAL ‘2 day’;
String Concatenation
SELECT FirstName + LastName FROM employee;
SELECT FirstName || LastName FROM employee;
CHARINDEX
SELECT CHARINDEX('our', 'resource');
SELECT POSITION('our' in 'resource');
GETDATE
SELECT GETDATE();
SELECT NOW();