Convert SQL Server T-SQL to PostgreSQL: the full function and statement mapping table, and the six translations that convert cleanly and return a different answer
11 min read Migration The Adapters team
Last updated September 2026
Field mapping auto-plugged · tap a port to rewire
Plug a source port into
Transform on this cable
JSON in
JSON out
5 sample records ready
Most T-SQL converts to PostgreSQL mechanically. TOP becomes LIMIT, IIF becomes a CASE expression, NEWID becomes gen_random_uuid, and a converter or a careful afternoon handles the bulk of it. The full mapping table is below. Then comes the part that costs teams a fortnight of debugging after go-live: six translations that every conversion guide publishes, which compile, run, return a value, and return a different value than the statement they replaced.
Key takeaways
- now() is not GETDATE(). The PostgreSQL manual says now() is transaction_timestamp() and does not change during a transaction. GETDATE() advances on every call.
- LEN is not length. Microsoft documents LEN as excluding trailing spaces. PostgreSQL length() counts them, so every padded CHAR column returns a different number.
- CHARINDEX and strpos take their arguments in opposite orders. The renamed call still runs and returns 0.
- DATEDIFF counts boundary crossings, not elapsed time. A gap of one day across New Year is one year to DATEDIFF.
- CONCAT ignores NULL, || does not. Translate CONCAT to concat, never to the pipe operator.
- Test on values, not on whether it compiles. Every failure here produces a running query with a wrong answer.
How do I convert SQL Server T-SQL to PostgreSQL?
Split the work into three piles and do them in order. Pile one is syntax that maps one to one, which a converter handles: TOP, IIF, NEWID, IDENTITY, CAST. Pile two is syntax with no equivalent, which you redesign: SCOPE_IDENTITY becomes a RETURNING clause, temp table idioms change shape, and every stored procedure body gets rewritten in PL/pgSQL. Pile three is the dangerous one: functions that exist in both engines under similar names and behave differently. Pile three is where the bugs come from, because nothing in it fails loudly.
The reason this ordering matters is that pile three is invisible to the tooling. AWS rates data type conversion four stars out of four for automation in its own SQL Server to Aurora PostgreSQL playbook. It rates collation handling zero on the same scale. Nothing rates the semantic drift between two functions that share a purpose and disagree about edge cases, because no converter can detect it. It compiles. It runs. It returns a number.
SQL Server to PostgreSQL T-SQL mapping table
Every row checked against Microsoft Learn and the PostgreSQL 18 manual on 1 September 2026. Rows marked "see the table below" are the ones that look equivalent and are not.
| T-SQL | PostgreSQL | Note |
|---|---|---|
| SELECT TOP 10 | LIMIT 10 | TOP n WITH TIES becomes FETCH FIRST n ROWS WITH TIES. TOP inside UPDATE or DELETE has no direct equivalent |
| ISNULL(a, b) | coalesce(a, b) | Not equivalent. See the table below: ISNULL can truncate its replacement, coalesce does not |
| GETDATE() | now() or clock_timestamp() | Not equivalent inside a transaction. See the table below |
| GETUTCDATE() | now() at time zone 'utc' | Returns a naive timestamp. Do not then store it in a timestamptz column or it converts twice |
| SYSDATETIME() | clock_timestamp() | The closest match, because both give the actual current reading rather than a frozen one |
| LEN(x) | length(rtrim(x)) | LEN excludes trailing spaces and length does not. Plain length() changes the answer |
| DATALENGTH(x) | octet_length(x) | Both count bytes. Values differ anyway because NVARCHAR is UTF-16 and PostgreSQL text is UTF-8 |
| CHARINDEX(find, in) | strpos(in, find) | The arguments are in the opposite order. A direct swap of the name alone silently reverses the search |
| SUBSTRING(x, s, l) | substring(x from s for l) | substring(x, s, l) also works. Check behavior where the start position is below 1 |
| DATEADD(day, n, d) | d + n * interval '1 day' | Straightforward. Watch that adding a month to 31 January differs between engines |
| DATEDIFF(day, a, b) | No direct equivalent | DATEDIFF counts boundary crossings, not elapsed units. See the table below |
| DATEPART(year, d) | extract(year from d) | Also date_part('year', d). Genuinely equivalent |
| CONVERT(varchar, d, 112) | to_char(d, 'YYYYMMDD') | Every CONVERT style number has to be translated to an explicit format string by hand |
| CAST(x AS INT) | x::integer | CAST works in both. PostgreSQL is stricter about what it will accept from a string |
| a + b (strings) | a || b | Both return NULL if an operand is NULL. Equivalent |
| CONCAT(a, b) | concat(a, b) | Both ignore NULL. Do not translate CONCAT to || , because || does not |
| IIF(c, a, b) | CASE WHEN c THEN a ELSE b END | Direct. PostgreSQL has no IIF |
| IDENTITY(1,1) | GENERATED BY DEFAULT AS IDENTITY | Run setval on every sequence after the data load or the first insert fails |
| SCOPE_IDENTITY() | RETURNING id | A different shape. The insert returns the value rather than a later call retrieving it |
| NEWID() | gen_random_uuid() | Built in since PostgreSQL 13. No extension needed |
| @@ROWCOUNT | GET DIAGNOSTICS x = ROW_COUNT | Only inside PL/pgSQL. There is no session variable equivalent in plain SQL |
| SELECT INTO #temp | CREATE TEMP TABLE AS SELECT | PostgreSQL SELECT INTO means something different in PL/pgSQL, so do not translate it literally |
| MERGE | INSERT ... ON CONFLICT | MERGE also exists from PostgreSQL 15, but ON CONFLICT is usually the better fit |
| CREATE PROCEDURE | CREATE PROCEDURE or FUNCTION | Procedures exist from PostgreSQL 11. The body is PL/pgSQL, not T-SQL, so this is a rewrite |
Six T-SQL translations that convert cleanly and return a different answer
This is the table that does not exist anywhere else, and it is the reason this page is worth bookmarking. Each row is a translation published in mainstream conversion guidance. Each one produces valid PostgreSQL. Each one returns a different result from the T-SQL it replaced, in a way no test suite catches unless somebody wrote an assertion about the specific value.
| T-SQL | The usual translation | Why the answer differs | What to write instead |
|---|---|---|---|
| DATEDIFF(year, a, b) | date_part or a subtraction | AWS states in its own playbook that DATEDIFF "returns an integer value of DATEPART boundaries that are crossed between two dates". It counts boundaries, not elapsed time. DATEDIFF(year, '2025-12-31', '2026-01-01') is 1, for one day apart. | extract(year from b) - extract(year from a) to keep boundary counting, or age(b, a) if you actually wanted elapsed time. Decide which the original meant. |
| LEN(col) | length(col) | Microsoft documents LEN as returning the number of characters "excluding trailing spaces". PostgreSQL length() counts every character. On a CHAR(n) column, which SQL Server pads, every single row returns a different number. | length(rtrim(col)). Check CHAR columns specifically, because that is where the padding lives. |
| CHARINDEX(needle, haystack) | strpos(needle, haystack) | The two functions take their arguments in opposite orders. CHARINDEX takes the string to find first; strpos takes the string to search first. The translated call still runs and still returns an integer, usually 0. | strpos(haystack, needle). Grep for every CHARINDEX and swap the arguments rather than trusting a find and replace. |
| GETDATE() inside a transaction | now() | The PostgreSQL manual states now() is transaction_timestamp() and that "their values do not change during the transaction", calling it a feature. GETDATE() advances on every call. A procedure that stamped 100 rows with 100 times now stamps them all identically. | clock_timestamp() where the code relied on time advancing mid transaction. now() everywhere else. |
| ISNULL(short_col, 'a longer default') | coalesce(short_col, 'a longer default') | Microsoft documents that ISNULL "returns the same type as check_expression" and that the replacement "can be truncated if replacement_value is longer than check_expression". coalesce resolves to the wider type instead, so the default stops being truncated. | Usually leave coalesce, because it is the sane behavior. Just know the output changes, and check anything that compared the result to a fixed string. |
| CONCAT(a, b) with a NULL | a || b | SQL Server CONCAT converts NULL arguments to an empty string. The PostgreSQL || operator returns NULL if either side is NULL. A translated expression turns a partly populated string into no string at all. | concat(a, b), which exists in PostgreSQL and ignores NULL exactly like the SQL Server version. |
What is the PostgreSQL equivalent of GETDATE()?
There are two, and picking the wrong one is the most common silent bug in a T-SQL conversion. Use clock_timestamp() when the original code relied on the clock advancing, and now() when it did not. The PostgreSQL manual is explicit that now() is a traditional equivalent of transaction_timestamp(), and that these functions return the start time of the current transaction, so "their values do not change during the transaction".
The manual calls that a feature, and it is: the intent is to let a single transaction have one consistent idea of the current time, so that everything written in that transaction carries the same stamp. That is genuinely better behavior for most applications. It is also the opposite of what SQL Server does, and a stored procedure that loops over a thousand rows calling GETDATE() produced a spread of timestamps that somebody downstream may be sorting on, deduplicating on, or using to reconstruct the order of events. After a naive conversion, all thousand rows share one value and that ordering is gone.
Worth noting that AWS's own playbook lists CURRENT_TIMESTAMP with the parenthetical "(start of current transaction)" in its function definition table, then maps GETDATE to NOW in its summary table without repeating the caveat. Both statements are individually accurate. Read together at speed, they produce the bug.
What is the PostgreSQL equivalent of ISNULL?
coalesce, and it is a better function, which is why the difference catches people out. ISNULL takes exactly two arguments and coalesce takes any number, returning the first that is not null. The behavior difference is in typing. Microsoft documents that ISNULL "returns the same type as check_expression", and that the replacement value "can be truncated if replacement_value is longer than check_expression".
So ISNULL on a varchar(10) column with a twenty character default returns ten characters. coalesce on the same inputs returns all twenty, because it resolves to the wider type rather than forcing the second argument into the shape of the first. Nine times out of ten that is the fix you wanted. The tenth time, something downstream was parsing a fixed width string, and it now receives a longer one. Search for comparisons against literal strings anywhere a converted ISNULL feeds them.
How do I convert SQL Server TOP to PostgreSQL?
SELECT TOP 10 becomes LIMIT 10, appended at the end of the statement rather than sitting after SELECT. TOP 10 WITH TIES becomes FETCH FIRST 10 ROWS WITH TIES, which PostgreSQL has supported since version 13. TOP with a percentage has no direct equivalent and needs a window function or a count subquery.
The case that needs redesign is TOP inside a data modification. DELETE TOP (1000) FROM t is a common batching idiom in SQL Server and PostgreSQL has no LIMIT on DELETE. The usual rewrite deletes from a subquery that selects the primary keys with its own LIMIT, which is clearer anyway because it forces you to say which thousand rows you meant. If the original had no ORDER BY, and batching deletes often do not, that is worth flagging: it was non-deterministic in SQL Server too, and the migration is a reasonable moment to fix it.
Does PostgreSQL have stored procedures?
Yes, since PostgreSQL 11, created with CREATE PROCEDURE and invoked with CALL. Before that there were only functions. Procedures matter for conversion because unlike functions they can commit and roll back transactions internally, which is what a lot of T-SQL batch logic assumes.
Having the construct does not make this a translation job. The body is PL/pgSQL, a different language with different variable declaration, different error handling, and no equivalent of the T-SQL statement batch. Expect to rewrite rather than convert, expect the converter to give you a report rather than working code, and scope it by counting objects and lines in sys.sql_modules before you promise anyone a date. If the count comes back large enough to be frightening, that is the moment to price Babelfish for Aurora PostgreSQL against a rewrite, because Babelfish runs the T-SQL as it stands.
How do I convert SQL Server money to PostgreSQL?
Use numeric(19,4) and never the PostgreSQL type that is also called money. The name match is a trap. Microsoft documents SQL Server money as accurate to a ten-thousandth of a monetary unit, which is four decimal places. The PostgreSQL manual states that the money type's "fractional precision is determined by the database's lc_monetary setting", which on a typical US English configuration is two.
Convert a four decimal column into a two decimal type and the migration succeeds, the row counts reconcile, and the third and fourth decimal places are gone. On per unit prices, tax fractions and FX rates, that is a real loss. The PostgreSQL manual adds a second reason to avoid the type: because its output is locale sensitive, money data may not load into a database with a different lc_monetary, so a dump taken on one server may not restore cleanly on another. Microsoft's own page, for what it is worth, advises against SQL Server money too, recommending decimal with at least four places if the values are used in calculations.
How do I verify a T-SQL conversion?
Run both databases side by side and compare answers, not schemas. A schema diff tells you the conversion produced the objects you expected. It says nothing about whether they return the same values, and everything on this page is a case where the schema is right and the values are not.
The practical method is to capture real queries from the SQL Server query log, replay them against both databases, and diff the result sets. Prioritize anything containing the six functions in the table above, anything aggregating a money column, and anything with an ORDER BY on text, since collation changes the order. Do the same for stored procedure outputs by calling each one with representative parameters and comparing the rows it returns. This is unglamorous and it is the only thing that catches semantic drift.
Do the replay in a staging environment before the cutover rather than during it. The practical sequence is to stand the new database up, point a copy of the application at it, and let it run against real traffic patterns for long enough to be boring. Teams that can stand up a parallel environment and deploy to it without downtime tend to do this properly, because the cost of trying is an afternoon rather than a change request. Teams that cannot tend to skip it, and find the six rows above one at a time over the following month.
The full tool comparison for this route, including what each one bills by and the eight published type mappings we could not verify, is on our SQL Server to PostgreSQL migration tools page, and the step by step cutover sequence is in the SQL Server to PostgreSQL migration guide. The equivalent treatment for the other two commercial routes into Postgres is in convert Oracle PL/SQL to PostgreSQL and convert MySQL data types to PostgreSQL. If SQL Server is not actually switching off, the ongoing case is covered in Postgres ETL tools, and the budget model behind either program is in what a data migration really costs. If the target is Snowflake rather than Postgres, the equivalent column level treatment is in convert SQL Server data types to Snowflake.
Once the schema is PostgreSQL, keep it in agreement with everything else
Map the fields once, run the backfill, then let the same mapping run incrementally with retries, alerts and per-record logs. From $49 a month, never metered by rows.
No credit card required.