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 17 | SQL Server 2022 | |
|---|---|---|
| License | PostgreSQL License, free | Commercial; free Express and Developer editions |
| Runs on | Linux, Windows, macOS, BSD | Windows, Linux |
| Dialect | PostgreSQL SQL, PL/pgSQL | T-SQL |
| Return changed rows | RETURNING | OUTPUT |
| Upsert | INSERT ... ON CONFLICT, MERGE | MERGE |
| First n rows | LIMIT n, FETCH FIRST n ROWS ONLY | TOP (n), OFFSET ... FETCH |
| Boolean type | boolean | bit |
| JSON | json and jsonb types | Text (nvarchar) plus JSON functions |
| Arrays | Yes | No |
| Clustered index | No, tables are heaps | Yes, the primary key by default |
| DDL in a transaction | Yes | Yes |
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 Server | PostgreSQL |
|---|---|
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 tables | CREATE 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.