PostgreSQL vs. SQL Server (MSSQL) - Detailed Comparison

What are the syntax differences between PostgreSQL and SQL Server? Compare PostgreSQL vs. MSSQL Server Syntax

Syntax                                                         PostgreSQL                          SQL Server

SELECT                                                     col1, col2                               Select [col1], [col2]

Aliases for columns and tables    SELECT AVG(col1) AS avg1      SELECT AVG(col1)=avg1

Working with dates                        CURRENT_DATE()                     GETDATE() DATEPART()

                                                                CURRENT_TIME()

                                                               EXTRACT()

What are the data type differences between PostgreSQL and SQL Server? Compare data types in PostgreSQL vs. MSSQL

Data type                                                                     PostgreSQL                                   SQL Server

64-bit integer                                                             BIGINT                                           BIGINT

Fixed length byte string                                             BYTEA                                         BINARY(n)

1, 0 or NULL                                                                        BOOLEAN                               BIT

Fixed length char string, 1 <= n <= 8000           CHAR(n)                                              CHAR(n)

Variable length char string, 1 <= n <= 8000   VARCHAR(n)                           VARCHAR(n)

Variable length char string, <= 2GB               TEXT                                       VARCHAR(max)

Variable length byte string , 1 <= n <= 8000     BYTEA                                   VARBINARY(n)

Variable length byte string , <= 2GB               BYTEA                                   VARBINARY(max)

Variable length Unicode UCS-2 string               VARCHAR(n)                     NVARCHAR(n)

Variable length Unicode UCS-2 data, <= 2GB   TEXT                                   NVARCHAR(max)

Variable length character data, <= 2GB           TEXT                                             TEXT

Variable length Unicode UCS-2 data, <= 2GB   TEXT                                           NTEXT

Double precision floating point number         DOUBLE PRECISION   DOUBLE PRECISION

Floating point number                                           DOUBLE PRECISION             FLOAT(p)

32 bit integer                                                               INTEGER                               INTEGER

Fixed point number                                                   NUMERIC(p,s)                       NUMERIC(p,s)

Date includes year, month, and day                       DATE                                       DATE

Date and time with fractional seconds           TIMESTAMP(p)               DATETIME, DATETIME2(p)

Date and time with time zone       TIMESTAMP(p) WITH TIME ZONE   DATETIMEOFFSET(p)

Date and time                                                     TIMESTAMP(0)             SMALLDATETIME

Unsigned integer, 0 to 255 (8 bit)                      SMALLINT                             TINYINT

UUID (16 byte)                                                       CHAR(16)                     UNIQUEIDENTIFIER

Automatically updated binary data                     BYTEA                           ROWVERSION

Currency amount (32 bit)                                       MONEY                       SMALLMONEY

Variable length binary data, <= 2GB                   BYTEA                                   IMAGE

Geometric types           POINT, LINE, LSEG, BOX, PATH, POLYGON, CIRCLE   GEOMETRY

What are the trigger differences between PostgreSQL and SQL Server? Compare the triggers in PostgreSQL vs. MSSQL

PostgreSQL

PostgreSQL has advanced triggers. Supported triggering events are AFTER, BEFORE, and INSTEAD OF, and they can be used for INSERT, UPDATE, and DELETE events. Functions can be used to execute a complex SQL when the trigger gets invoked. PostgreSQL can execute this dynamically. 

SQL Server

SQL Server offers triggers for different types of database events:

• DML Triggers: for a data manipulation language (DML) specific event, such as inserting, updating, or deleting records. These triggers fire on events irrespective to the number of rows affected.

• DDL Triggers: for data definition language (DDL) events, such as CREATE, DROP, or ALTER statements. These are useful for preventing or auditing changes to the database schema. 

• Logon Triggers: for logon events, such as when a user session is established. These triggers fire after successful authentication and before establishing the user session. They are useful for auditing and controlling login activity. 

What are the differences of integers between PostgreSQL and SQL Server? Compare the integers in PostgreSQL vs. MSSQL

PostgreSQL

There are three kinds of integers in PostgreSQL: 

1. SMALLINT  (small integer, a 2-byte type with a range from -32,768 to 32,767)

2. INT (integer, a 4-byte type with a range from -2,147,483,648 to 2,147,483,647)

3. BIGINT (a large-range integer:  -9223372036854775808 to 9223372036854775807)

SQL Server

SQL SERVER supports standard SQL integer types BIGINT, INT, SMALLINT, and TINYINT. The range and storage size of each type is as follows:

Data type         Range                                                          Storage

BIGINT   -263 (-9,223,372,036,854,775,808) to 263-1 (9,223,372,036,854,775,807) 8 Bytes

INT -231 (-2,147,483,648) to 231-1 (2,147,483,647)                         4 Bytes

SMALLINT -215 (-32,768) to 215-1 (32,767)                             2 Bytes

TINYINT 0 to 255                                                 1 Byte

What are the boolean type differences between PostgreSQL and SQL Server? Compare the boolean types in PostgreSQL vs. MSSQL

PostgreSQL

The PostgreSQL Boolean data type can have 3 states: 

1. TRUE, 

2. FALSE, and 

3. NULL. 

SQL Server

In SQL SERVER the BIT data type is used to represent true/false boolean data. A BIT field's value is either 1, 0, or null.