Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.
SQL Server vs PostgreSQL syntax differences: TOP vs LIMIT, IDENTITY vs SERIAL, + vs || concatenation, GETDATE() vs NOW(), and more, with side-by-side SQL.
Quick answer
SQL Server vs PostgreSQL syntax differences show up in row limiting (TOP vs LIMIT), auto-increment columns (IDENTITY vs GENERATED ALWAYS AS IDENTITY), string concatenation (+ vs ||), current-date functions (GETDATE() vs NOW()), identifier quoting (square brackets vs double quotes), and boolean handling (BIT vs native BOOLEAN). None of these translate one-to-one — each needs a small syntax rewrite, and T-SQL stored procedures need the most rework when porting to PL/pgSQL.
FAQs
Is SQL Server syntax compatible with PostgreSQL?
No. SQL Server's T-SQL and PostgreSQL both extend standard SQL in incompatible directions — row limiting, identity columns, string concatenation, and procedural code all use different syntax. A query written for one usually errors, rather than just behaving differently, when run against the other.
What is the PostgreSQL equivalent of SQL Server's IDENTITY column?
GENERATED ALWAYS AS IDENTITY is the closest match — like IDENTITY, it rejects user-supplied values unless the insert opts out explicitly with OVERRIDING SYSTEM VALUE. PostgreSQL's older SERIAL pseudo-type also auto-increments, but it allows any INSERT to override the generated value, which is a looser guarantee than SQL Server's IDENTITY.
Can I run T-SQL stored procedures in PostgreSQL without changes?
No. T-SQL stored procedures need to be rewritten as PL/pgSQL functions — @variable declarations, IF/ELSE control flow, and TRY...CATCH error handling all use different syntax in PL/pgSQL, and there's no automated line-by-line translation between the two.
Does PostgreSQL support the TOP keyword?
No, PostgreSQL has no TOP keyword. Use LIMIT n after ORDER BY instead, or the ANSI-standard OFFSET ... FETCH NEXT n ROWS ONLY, which also runs unchanged on SQL Server 2012 and later.
Why does my migrated query return NULL instead of an empty string?
You likely ported string concatenation from SQL Server's + operator to PostgreSQL's || operator — both propagate NULL the same way, so if a column can be NULL, wrap it in COALESCE(column, '') or use the CONCAT() function, which treats NULL arguments as empty strings in both dialects.
Try it now
Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.
SQL Server vs PostgreSQL syntax differences break migrations in small, specific ways — port a query by find-and-replace and it breaks on the first SELECT TOP 10, because PostgreSQL has no TOP keyword. That's the pattern behind most SQL Server-to-PostgreSQL migration bugs: the two dialects share enough standard SQL that you assume compatibility, then hit a wall of syntax differences that don't produce helpful error messages.
SQL Server runs T-SQL, a Microsoft dialect with its own procedural extensions — @variable syntax, TRY...CATCH, and proprietary functions like GETDATE() and ISNULL(). PostgreSQL follows the ANSI SQL standard more closely for queries and layers PL/pgSQL on top for procedural code. The practical result: query-level differences you fix with a rewrite pattern, and procedural-code differences that need to be rebuilt from scratch.
This guide covers each difference that actually breaks migrations — row limiting, identity columns, string concatenation, date functions, identifier quoting, variables, and NULL/boolean handling — with runnable SQL side by side for both dialects, plus a migration checklist to work through query by query. If you're migrating from MySQL instead, the differences are just as sharp — see our MySQL vs PostgreSQL syntax differences guide.
How do you limit rows differently: TOP vs LIMIT?
SQL Server limits result rows with the TOP clause, placed directly after SELECT. PostgreSQL has no TOP keyword — it uses the ANSI-standard LIMIT and OFFSET clauses at the end of the query, after ORDER BY.
SQL Server: TOP
SELECT TOP 10 order_id, customer_id, order_totalFROM ordersORDER BY order_total DESC;
PostgreSQL: LIMIT
SELECT order_id, customer_id, order_totalFROM ordersORDER BY order_total DESCLIMIT 10;
TOP without an ORDER BY is just as nondeterministic in SQL Server as LIMIT without ORDER BY is in PostgreSQL — sort explicitly whenever row order matters, in either dialect. TOP also supports a PERCENT modifier (TOP 10 PERCENT) that has no direct PostgreSQL equivalent; you'd compute the percentage against a total row count yourself.
There's one row-limiting pattern that migrates for free: both dialects support the ANSI-standard OFFSET ... FETCH NEXT ... ROWS ONLY syntax. Write ORDER BY order_total DESC OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY and it runs unchanged on SQL Server 2012 and later, and on PostgreSQL. If you're building pagination logic that has to run on both, standardize on OFFSET/FETCH instead of TOP or LIMIT. See Microsoft's TOP (Transact-SQL) reference and PostgreSQL's LIMIT and OFFSET documentation for the full syntax rules.
How do auto-increment columns differ: IDENTITY vs SERIAL?
SQL Server auto-generates primary key values with the IDENTITY(seed, increment) property on the column definition. PostgreSQL's classic equivalent is the SERIAL pseudo-type, but the modern, SQL-standard way is GENERATED ALWAYS AS IDENTITY — and it's the closer semantic match to SQL Server's IDENTITY, not SERIAL.
The distinction matters in practice. SERIAL is Postgres-only sugar: it creates a sequence and an implicit DEFAULT nextval(...), and nothing stops a plain INSERT from overwriting it with an arbitrary value. GENERATED ALWAYS AS IDENTITY behaves like SQL Server's IDENTITY — the column rejects user-supplied values unless the insert explicitly opts out with OVERRIDING SYSTEM VALUE. If you want the same guardrail against accidental ID collisions that SQL Server gives you by default, reach for GENERATED ALWAYS AS IDENTITY, not the SERIAL that most tutorials default to. Full syntax is in Microsoft's IDENTITY property reference and PostgreSQL's identity columns documentation.
How does string concatenation differ: + vs ||?
SQL Server concatenates strings with the + operator — the same operator it uses for arithmetic. PostgreSQL uses the ANSI-standard || operator instead.
Both dialects propagate NULL by default: concatenate anything with NULL using + in SQL Server or || in PostgreSQL, and the whole expression becomes NULL. Both also offer a CONCAT() function as an escape hatch — and in both, CONCAT() treats NULL arguments as empty strings instead of propagating them. That symmetry is easy to miss: teams porting first_name + ' ' + last_name to || are sometimes surprised when a single missing middle name blanks out an entire name column — the SQL Server version behaved identically with +.
How do date and time functions differ: GETDATE() vs NOW()?
SQL Server returns the current timestamp with GETDATE() (or the more precise SYSDATETIME()). PostgreSQL uses NOW() or the ANSI-standard CURRENT_TIMESTAMP — both return the same value, so pick one and use it consistently.
Extracting a date part is where the rewrite gets more involved: SQL Server's DATEPART(YEAR, order_date) becomes PostgreSQL's EXTRACT(YEAR FROM order_date). Date arithmetic diverges too — SQL Server's DATEADD(day, 7, order_date) becomes order_date + INTERVAL '7 days' in PostgreSQL, and DATEDIFF(day, start_date, end_date) becomes plain subtraction, end_date - start_date, since PostgreSQL's date subtraction already returns an integer number of days.
How are identifiers quoted differently: brackets vs double quotes?
SQL Server wraps identifiers that contain spaces or reserved words in square brackets: [Order Date]. PostgreSQL uses ANSI-standard double quotes instead: "Order Date".
The bigger gotcha is case sensitivity, not the quote character. SQL Server identifiers are case-insensitive by default. PostgreSQL folds unquoted identifiers to lowercase and treats quoted identifiers as case-sensitive — a table created as "Orders" (quoted, mixed case) must be referenced as "Orders" everywhere afterward, while unquoted Orders silently becomes orders. Schemas that quoted every identifier in SQL Server to preserve PascalCase carry that same requirement into PostgreSQL. For the full quoting picture across all five major dialects, see our guide to single vs double quotes in SQL.
How do variables and stored procedures differ: DECLARE and procedural code?
SQL Server lets you declare and use variables directly in an ad hoc script with DECLARE @variable_name type and reference them with the @ prefix — no function or procedure required. PostgreSQL doesn't support bare variables in plain SQL at all: DECLARE only works inside a PL/pgSQL function or an anonymous DO block.
This is the biggest source of migration effort. A T-SQL stored procedure with @variable declarations, IF...ELSE control flow, and TRY...CATCH error handling has no line-by-line PostgreSQL translation — it has to be rebuilt as a PL/pgSQL function, with BEGIN...EXCEPTION...END replacing TRY...CATCH and RAISE replacing THROW. Budget the most migration time for stored procedures, not plain queries.
How do NULL handling and booleans differ: ISNULL/BIT vs COALESCE/BOOLEAN?
SQL Server's ISNULL(expression, replacement) takes exactly two arguments, and can silently truncate values because it returns the data type of its first argument. PostgreSQL supports the ANSI-standard COALESCE(expr1, expr2, ...) instead, which takes any number of arguments and returns the type of the first non-null expression.
Booleans are a bigger structural gap. SQL Server has no native BOOLEAN type — flags are stored as BIT (0 or 1), and comparisons look like WHERE is_active = 1. PostgreSQL has a real BOOLEAN type with TRUE/FALSE/NULL literals, so the equivalent is WHERE is_active = TRUE, or simply WHERE is_active. When migrating a schema, convert BIT flag columns to BOOLEAN rather than SMALLINT — you get better query readability and a constraint that only three values are ever valid. For how NULL comparisons behave once you're on PostgreSQL, see our breakdown of why = NULL never works.
SQL Server vs PostgreSQL syntax differences: migration checklist
Replace TOP n (and TOP n PERCENT) with LIMIT n, or standardize on OFFSET ... FETCH NEXT ... ROWS ONLY, which both dialects support unchanged.
Convert IDENTITY(seed, increment) columns to GENERATED ALWAYS AS IDENTITY — it's the closer match to SQL Server's guardrails against accidental value overwrites, not SERIAL.
Swap + string concatenation for ||, and re-check every spot that relied on CONCAT() for NULL-safe joins — the empty-string behavior carries over.
Replace GETDATE()/SYSDATETIME() with NOW()/CURRENT_TIMESTAMP, DATEPART() with EXTRACT(), and DATEADD()/DATEDIFF() with interval arithmetic.
Convert bracketed [identifiers] to double-quoted "identifiers", and audit every unquoted identifier for PostgreSQL's lowercase-folding behavior.
Rewrite ISNULL(x, y) as COALESCE(x, y) — this also fixes ISNULL's occasional silent type-truncation.
Convert BIT flag columns to native BOOLEAN, and rewrite = 1 / = 0 checks as TRUE / FALSE.
Rebuild T-SQL stored procedures as PL/pgSQL functions — @variable DECLARE/SET blocks become DECLARE/BEGIN blocks, and TRY...CATCH becomes BEGIN...EXCEPTION.
Re-test execution plans after migrating; PostgreSQL's query planner and SQL Server's differ enough that an index-friendly query in one can table-scan in the other.
Format and validate every migrated query before it ships, so a leftover TOP or bracketed identifier gets caught in review instead of in production.
Wrapping up
SQL Server and PostgreSQL look similar enough on the surface — both relational, both speaking roughly-standard SQL — that it's tempting to migrate by search-and-replace. The differences that actually bite are specific and learnable: TOP vs LIMIT, IDENTITY vs GENERATED ALWAYS AS IDENTITY, + vs ||, GETDATE() vs NOW(), bracket vs double-quote identifiers, and BIT vs BOOLEAN. Work through them with the checklist above and most queries migrate cleanly — stored procedures are the one category that needs a genuine rewrite rather than a syntax swap. If SQL Server throws its own parser error mid-migration, see our guide to fixing 'Incorrect syntax near' in SQL Server.
Once you've made the swap, sqlfmt formats and validates SQL Server and PostgreSQL SQL side by side, so a stray bracketed identifier or leftover TOP clause gets flagged before it reaches review or a production deploy.