Menu

PostgreSQL vs SQL Server: Differences and Which to Choose

PostgreSQL is free and open source; SQL Server is Microsoft's commercial database with T-SQL and a Windows-centered toolchain. Here is how their SQL, JSON, locking and licensing differ.

This page includes runnable editors. Edit, run, and see output instantly.

Choose PostgreSQL when license cost, running on any operating system or cloud, or extensions such as PostGIS matter. Choose SQL Server when you are already on the Microsoft stack and want what comes with it: T-SQL, SQL Server Management Studio, Windows authentication, and the SSIS, SSRS and SSAS data tools. Everyday SQL is close. The differences are in syntax details, locking defaults and licensing.

PostgreSQL 17SQL Server 2022
LicensePostgreSQL License, freeCommercial; free Express and Developer editions
Runs onLinux, Windows, macOS, BSDWindows, Linux
DialectPostgreSQL SQL, PL/pgSQLT-SQL
Return changed rowsRETURNINGOUTPUT
UpsertINSERT ... ON CONFLICT, MERGEMERGE
First n rowsLIMIT n, FETCH FIRST n ROWS ONLYTOP (n), OFFSET ... FETCH
Boolean typebooleanbit
JSONjson and jsonb typesText (nvarchar) plus JSON functions
ArraysYesNo
Clustered indexNo, tables are heapsYes, the primary key by default
DDL in a transactionYesYes

Return the changed rows

SQL Server's OUTPUT inserted.* and OUTPUT deleted.* become RETURNING, which works on INSERT, UPDATE, DELETE and MERGE:

It returns tickets 1 and 2, now closed. The T-SQL version:

UPDATE tickets SET status = 'closed'
OUTPUT inserted.id, inserted.status
WHERE status = 'open';

Upsert without MERGE

SQL Server upserts with MERGE. PostgreSQL has MERGE too (since version 15, with RETURNING since 17). The shorter INSERT ... ON CONFLICT is the usual choice, and it stays correct when two sessions insert the same key at once:

A-1 is updated to 12 and B-2 is inserted. See the upsert page for the variants.

T-SQL syntax to rewrite

EU has 1 paid order and US has 2. Line by line, this is what changes from T-SQL:

SQL ServerPostgreSQL
SELECT TOP (10) ...... LIMIT 10
'Region ' + region'Region ' || region
SUM(CASE WHEN paid = 1 THEN 1 ELSE 0 END)count(*) FILTER (WHERE paid)
ISNULL(a, b)COALESCE(a, b)
GETDATE()now()
[order]"order"
#temp tablesCREATE TEMP TABLE

Text comparison differs too. SQL Server's default collations are case-insensitive, while PostgreSQL compares text case-sensitively. WHERE name = 'ana' no longer matches Ana, so use ILIKE or lower().

Query JSON

SQL Server 2022 stores JSON as text and reads it with JSON_VALUE, JSON_QUERY and OPENJSON. PostgreSQL's jsonb is a type of its own, parsed once on write, with operators and GIN indexes:

The T-SQL equivalent filters on JSON_VALUE(data, '$.type') = 'purchase'. The JSONB page lists the operators.

Readers, writers and locks

PostgreSQL always uses multiversion concurrency control (MVCC): a reader sees a snapshot and never waits for a writer. SQL Server's default read committed level takes shared locks, so a reader can wait for a writer. Turning on READ_COMMITTED_SNAPSHOT, or using SNAPSHOT isolation, makes SQL Server read row versions too.

Storage differs as well. PostgreSQL keeps old row versions in the table until VACUUM removes them. SQL Server stores a table as a clustered index on its primary key by default. It also has columnstore indexes for analytics, which PostgreSQL does not have built in.

Replication, licenses and tools

PostgreSQL has streaming replication to read-only standbys and logical replication of chosen tables. Automatic failover needs an external tool. SQL Server offers Always On availability groups, log shipping and transactional replication. Full availability groups need Enterprise edition.

PostgreSQL costs nothing at any size. SQL Server 2022 Express is free up to 10 GB per database, and Developer is free outside production. Standard and Enterprise are paid. The clients are psql and pgAdmin for PostgreSQL, and SQL Server Management Studio (Windows only) and sqlcmd for SQL Server.

Frequently Asked Questions

Is PostgreSQL a good replacement for SQL Server?

For application databases, usually yes: tables, joins, transactions, stored functions and JSON all have PostgreSQL equivalents. The work is in porting T-SQL procedures and replacing SQL Server-only pieces such as SSIS packages, temporal tables or columnstore indexes.

Is SQL Server free?

SQL Server 2022 Express is free with limits, including a 10 GB maximum database size. The Developer edition is free for development and testing, not for production. Standard is licensed per core or per server plus client access licenses, and Enterprise per core.

What is the PostgreSQL equivalent of SELECT TOP?

LIMIT: SELECT * FROM orders ORDER BY id LIMIT 10. PostgreSQL also accepts the standard FETCH FIRST 10 ROWS ONLY, which SQL Server supports after OFFSET 0 ROWS.

Does PostgreSQL run on Windows?

Yes. PostgreSQL runs on Windows, Linux, macOS and the BSDs. SQL Server runs on Windows and Linux, and in Linux containers.

Coddy programming languages illustration

Learn to code with Coddy

Get started