For the most part, SQL Server and PostgreSQL have compatible data types. They are not always called the same thing, but any data that fits in SQL Server will fit in PostgreSQL.
In fact, PostgreSQL has two data types that SQL Server doesn't have - JSON and BIT (which is not the same as SQL Server's BIT type).
Type mapping
Below are commonly SQL Server types and the column type that we translate to in PostgreSQL.
SQL Server Type | PostgreSQL Type
------------------|----------------
nvarchar | text
nchar | text
varchar | text
char | text
ntext | text
text | text
bigint | bigint
int | int
smallint | smallint
tinyint | smallint
float | double precision
real | real
numerical | decimal
datetime | timestamp
datetime2 | timestamp
smalldatetime | timestamp
datetimeoffset | timestamptz
date | date
time | time
binary | bytea
varbinary | bytea
image | bytea
uniqueidentifier | uuid
bit | bool
xml | xml
Type differences
You may be wondering why we use a certain PostgreSQL type - let us explain the more interesting transformations:
String types
According to the PostgreSQL documentation, there is no real performance difference between the TEXT, VARCHAR, and CHAR types. Unless you need to constrain the length of a string column, you should use the TEXT type.
The relevant quote from the docs:
"There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead."
In fact, if you interpret that quote literally, the TEXT type should actually be the fastest, since it isn't blank padded (like the CHAR type) and isn't length constrained (like the CHAR or VARCHAR types).
Number types
The integer types are mostly the same, or just called something slightly different, with the exception of PostgreSQL not supporting an 8 bit integer type (called TINYINT in PostgreSQL). If you want to store an 8 bit number, just use SMALLINT.
One notable difference is that PostgreSQL does not use an IDENTITY constraint to create an auto incementing integer. Instead, you use SMALL SERIAL, SERIAL, or BIGSERIAL, which are simply a small int, int, or bigint that auto increments. A nice difference here is that you can have an arbitrary number of the SERIAL types in a table, whereas SQL Server restricts you to have a single IDENTITY constraint in a table.
The 64 bit floating point numbers (FLOAT and DOUBLE PRECISION) are named differently, but behave the same.
Another nice thing about PostgreSQL is that it allows you to create a DECIMAL column without specifying the scale or precision! Unless you need to enforce those things (for example, when keeping track of money), you can just create a decimal column and not worry about it!
Date and time types
SQL Server's datetime and time types supports 7 decimal places of precision, whereas PostgreSQL's TIMESTAMP and TIME types only supports 6.
Bits, byte, bools, and images
PostgreSQL only has one type to store raw bytes: BYTEA. You should use that in place of SQL Server's BINARY, VARBINARY, and IMAGE types.
PostgreSQL's BIT type is used to store a variable number of 1's and 0's. SQL Server's only stores a single 1 or 0 (and is used as a boolean type). So, if you want to migrate a SQL Server BIT type, make it a PostgreSQL BOOLEAN. There is no equivalent to PostgreSQL BIT type in SQL Server.
PostgreSQL additional types
Since most people reading this article are migrating from SQL Server, you probably don't have much JSON or variable-length binary data in your database (since those are not official column types in SQL Server). However, PostgreSQL supports these types!
In particular, PostgreSQL's ability to store JSON can be a game changer. It is such a ubiquitous data format, and it's a shame that SQL Server treats it as a second class citizen by storing it in VARCHAR columns.
FYI, there are two JSON types in PostgreSQL: JSON, and JSONB. JSONB stores the record in a binary format on disk, and is faster to read. JSON is not transformed in this way, which makes it slightly faster to write. Most of the time, people elect to use JSONB, but if you have a write-only application for a certain column, it may make sense to use JSON.
