Translating a query between dialects: a worked example (PostgreSQL to SQL Server)

Convert a PostgreSQL query to SQL Server line by line: LIMIT/OFFSET, || concat, ILIKE, :: casts, booleans, date_trunc, RETURNING and generate_series.

Author: Łukasz Żochowski

  • #sql
  • #postgresql
  • #sqlserver
  • #dbeaver
  • #migration

Porting a query from PostgreSQL to SQL Server is rarely a clean copy-paste. The two speak the same SQL standard at the core, but the moment you reach for pagination, string functions, casts or booleans, each engine insists on its own spelling. This article takes one realistic PostgreSQL query and translates it to SQL Server one difference at a time, with every rule checked against the official PostgreSQL and Microsoft documentation. The goal is a method you can reuse, not a lookup table you have to trust.

What does the original PostgreSQL query look like?

Here is a reporting query of the kind you actually meet — a paged list of paid orders for one customer domain, with a formatted name, a cast amount and the order month:

SELECT
    o.id::text                              AS order_ref,
    c.first_name || ' ' || c.last_name      AS customer,
    o.total::numeric(10,2)                  AS amount,
    date_trunc('month', o.created_at)       AS order_month,
    o.is_paid
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.email ILIKE '%@acme.com'
  AND o.is_paid = true
ORDER BY o.created_at DESC
LIMIT 20 OFFSET 40;

Every highlighted construct — ::, ||, date_trunc, ILIKE, true, LIMIT/OFFSET — is a place where a direct paste into SQL Server fails. Let’s take them in turn.

How do you translate LIMIT/OFFSET to SQL Server?

PostgreSQL pages with LIMIT count and OFFSET start (PostgreSQL: LIMIT and OFFSET). SQL Server has no LIMIT. Its historical tool is TOP (expression), which caps rows but has no offset of its own (Microsoft Learn: TOP) — so TOP alone cannot express “skip 40, take 20”.

The portable answer is the SQL-standard OFFSET ... FETCH clause, which SQL Server supports on ORDER BY:

ORDER BY o.created_at DESC
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

Note the order flips: PostgreSQL writes LIMIT 20 OFFSET 40, SQL Server writes OFFSET 40 ... FETCH NEXT 20. And OFFSET ... FETCH is only valid with an ORDER BY (Microsoft Learn: ORDER BY clause) — which is a feature, not a limitation: pagination without a deterministic sort is a bug on either engine.

How do you convert || string concatenation?

In PostgreSQL, || concatenates strings — 'Post' || 'greSQL' returns PostgreSQL (PostgreSQL: string functions). SQL Server does not use || for this; its string concatenation operator is +, as in SELECT 'book' + 'case' returning bookcase (Microsoft Learn: + string concatenation). So the name expression becomes:

c.first_name + ' ' + c.last_name AS customer

There is a semantic trap here worth naming. SQL Server also offers the CONCAT function, and it is tempting to reach for it — but CONCAT implicitly converts null values to empty strings (Microsoft Learn: CONCAT), whereas + (like PostgreSQL’s ||) propagates NULL: concatenating anything with NULL yields NULL. If a last_name can be null, + reproduces the PostgreSQL behavior faithfully while CONCAT silently changes the result. Pick the one that matches the semantics you are porting, not the one that looks neatest.

How do you replace ILIKE for case-insensitive matching?

ILIKE is PostgreSQL’s case-insensitive LIKE. The documentation is explicit that it “can be used instead of LIKE to make the match case-insensitive,” that it “is not in the SQL standard but is a PostgreSQL extension,” and that the operator ~~* corresponds to it (PostgreSQL: pattern matching). SQL Server has no ILIKE keyword at all.

In SQL Server, case sensitivity of LIKE is decided by collation, not by the operator. Many databases run a case-insensitive default collation, in which plain LIKE already ignores case — but you cannot assume that. To force case-insensitive matching regardless of the column’s collation, either lower both sides with LOWER (Microsoft Learn: LOWER), or apply an explicit case-insensitive (_CI_) collation to the comparison with the COLLATE clause (Microsoft Learn: collations):

-- portable, works under any column collation:
WHERE LOWER(c.email) LIKE '%@acme.com'
-- or, forcing the collation explicitly:
WHERE c.email COLLATE Latin1_General_100_CI_AS LIKE '%@acme.com'

LOWER on both sides is the most predictable choice when you are porting and do not control the target collation. Be aware it can prevent an index from being used, exactly as any function wrapped around a column does.

How do you rewrite :: casts and booleans?

PostgreSQL accepts two cast syntaxes: the standard CAST(expression AS type) and the “historical PostgreSQL usage” shorthand expression::type (PostgreSQL: type casts). SQL Server supports only CAST(expression AS data_type) (or CONVERT) (Microsoft Learn: CAST and CONVERT). So both :: casts expand to the explicit form:

CAST(o.id AS varchar(20))      AS order_ref,
CAST(o.total AS decimal(10,2)) AS amount,

The boolean is a bigger shift. PostgreSQL has a real boolean type that accepts literals such as TRUE, FALSE and NULL (PostgreSQL: boolean type). SQL Server has no boolean data type; the equivalent is bit, “an integer data type that can take a value of 1, 0, or NULL,” where the string TRUE converts to 1 and FALSE to 0 (Microsoft Learn: bit). So o.is_paid = true becomes:

AND o.is_paid = 1

and the is_paid column itself is declared bit rather than boolean in the SQL Server schema.

What about date_trunc?

PostgreSQL’s date_trunc('month', o.created_at) truncates a timestamp to a given precision — date_trunc('hour', timestamp '2001-02-16 20:38:40') returns 2001-02-16 20:00:00 (PostgreSQL: date/time functions). SQL Server 2022 (16.x) introduced a direct counterpart, DATETRUNC(datepart, date) (Microsoft Learn: DATETRUNC):

DATETRUNC(month, o.created_at) AS order_month   -- SQL Server 2022+

Before 2022 there is no DATETRUNC, and the long-standing idiom is a DATEADD/DATEDIFF round-trip through a zero base date (Microsoft Learn: DATEADD):

DATEADD(month, DATEDIFF(month, 0, o.created_at), 0) AS order_month

It reads opaquely the first time, but it is the correct pre-2022 equivalent: count whole months since the base date, then add them back.

How do you port RETURNING and generate_series?

Two more PostgreSQL constructs show up constantly and have no literal SQL Server spelling.

RETURNING lets a PostgreSQL INSERT/UPDATE/DELETE hand back the rows it changed (PostgreSQL: RETURNING). SQL Server’s equivalent is the OUTPUT clause, which “returns information from, or expressions based on, each row affected” and exposes the INSERTED and DELETED pseudo-tables (Microsoft Learn: OUTPUT):

-- PostgreSQL
INSERT INTO orders (customer_id, total) VALUES (42, 99.90) RETURNING id;
-- SQL Server
INSERT INTO orders (customer_id, total) OUTPUT INSERTED.id VALUES (42, 99.90);

generate_series produces a set of values — generate_series(1, 10) yields the integers 1 through 10 (PostgreSQL: set-returning functions). SQL Server 2022 added a function of the same name, GENERATE_SERIES(start, stop [, step]), which requires compatibility level 160 (Microsoft Learn: GENERATE_SERIES). On older versions, a recursive common table expression is the portable substitute:

WITH series AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM series WHERE n < 10
)
SELECT n FROM series OPTION (MAXRECURSION 100);

What does the fully translated query look like?

Put every rule together and the SQL Server version of the opening query is:

SELECT
    CAST(o.id AS varchar(20))              AS order_ref,
    c.first_name + ' ' + c.last_name       AS customer,
    CAST(o.total AS decimal(10,2))         AS amount,
    DATETRUNC(month, o.created_at)         AS order_month,   -- SQL Server 2022+
    o.is_paid
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE LOWER(c.email) LIKE '%@acme.com'
  AND o.is_paid = 1
ORDER BY o.created_at DESC
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

Six constructs, six distinct rules — and this is only one query. The same split surfaces everywhere you move between engines, which is why cross-engine work is less about the SQL standard and more about the last 10%. These are exactly the dialect differences that trip you up across five engines, and the divergence does not stop at syntax: even reading an execution plan uses different commands and keywords on each engine.

Disclosure: this blog is published by LumaSoft (LUMA sp. z o.o.), and I work on the tool mentioned here.

SQL Sage for DBeaver is a plugin that adds an AI copilot inside DBeaver and is dialect-aware across five engines — PostgreSQL, MySQL/MariaDB, Oracle, SQL Server and SQLite — so when you ask it to rewrite a query for another engine, the pagination, casts, string functions and date handling match the connection you are on. A few facts stated plainly: it is Windows-only (Linux and macOS are not supported), it is keyless (you sign in with your own Claude or ChatGPT account, no API key), and it is read-only by default — on PostgreSQL and MySQL/MariaDB that read-only mode is enforced at the engine level. You can see both plugins on the products page. It is not affiliated with Microsoft, PostgreSQL or DBeaver.

The durable takeaway is the method, not the mapping: translate one construct at a time, confirm each against the target engine’s own documentation, and watch the semantic traps — CONCAT versus + on nulls, collation versus ILIKE, DATETRUNC versus the DATEADD idiom — because those are where a query that “runs” quietly returns the wrong answer.

← Back to the blog