Writing Portable SQL

Portable SQL is not "never use dialect features." It is SQL whose assumptions are visible enough that you know what will travel unchanged, what needs translation, and what must be tested on the target engine.

The trap is that some SQL looks portable while its meaning changes underneath you. Syntax portability is only the first gate; semantic portability is the one that keeps production reports honest.

The portable core

The safest core is boring on purpose:

• Name the columns you need instead of relying on SELECT *.
• Use standard INNER JOIN, LEFT JOIN, ON, WHERE, GROUP BY, HAVING, CASE, and UNION ALL shapes.
• Qualify columns in multi-table queries.
• Keep grouped queries strict: every selected expression is either grouped or aggregated.
• Use typed literals where the type matters, such as DATE '2026-05-01'.
• Keep window basics behind a target-version check: OVER, PARTITION BY, and ORDER BY are widely available now, but old engines and compatibility modes still exist.

That last point is the portability mindset: write the ordinary shape first, then feature-detect anything beyond it. The more a query depends on engine-specific helpers, the more it needs a labeled escape hatch.

PostgreSQL's strict GROUP BY rule is a good portable habit because it forces the report sentence to be honest. This query asks for one row per category, but also asks for one ungrouped name from inside each category:

SELECT category,
       name,
       count(*) AS items
FROM menu_items
GROUP BY category
ORDER BY category;

ERROR:  column "menu_items.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2:        name,
               ^

The portable rewrite says the real output grain out loud: one row per category and stock status, with a count inside each group.

SELECT category,
       in_stock,
       count(*) AS items
FROM menu_items
GROUP BY category, in_stock
ORDER BY category, in_stock DESC;

 category | in_stock | items
----------+----------+-------
 coffee   | t        |     3
 coffee   | f        |     1
 drinks   | t        |     1
 pastry   | t        |     3
 tea      | t        |     1
 tea      | f        |     1
(6 rows)

Some MySQL configurations have allowed loose grouped columns, producing a value that is not determined by the GROUP BY sentence. The course treats that as a trap, not a convenience. Strict grouping travels better because it refuses to hide nondeterminism.

The classic breakpoints

Here are the lines you inspect first when a query moves between engines:

-- Identifier quoting: standard/PostgreSQL double quotes vs MySQL backticks.
SELECT "order", "total_amount"
FROM "daily_orders";
 
SELECT `order`, `total_amount`
FROM `daily_orders`;
 
-- Row limiting: LIMIT, TOP, and FETCH FIRST are not the same spelling.
SELECT order_id, ordered_at
FROM orders
ORDER BY ordered_at DESC
LIMIT 10;
 
SELECT TOP (10) order_id, ordered_at
FROM orders
ORDER BY ordered_at DESC;
 
SELECT order_id, ordered_at
FROM orders
ORDER BY ordered_at DESC
FETCH FIRST 10 ROWS ONLY;
 
-- String concatenation and upsert spellings are common translation points.
SELECT first_name || ' ' || last_name AS display_name
FROM customers;
 
SELECT CONCAT(first_name, ' ', last_name) AS display_name
FROM customers;
 
INSERT INTO inventory_counts (item_code, quantity)
VALUES ('COF-ESP', 18)
ON CONFLICT (item_code) DO UPDATE
SET quantity = EXCLUDED.quantity;
 
INSERT INTO inventory_counts (item_code, quantity)
VALUES ('COF-ESP', 18)
AS incoming
ON DUPLICATE KEY UPDATE quantity = incoming.quantity;

Identifier quotes are the first obvious accent. The SQL standard uses double quotes for delimited identifiers, and PostgreSQL follows that shape. MySQL's default identifier quote is the backtick, with ANSI_QUOTES changing how double quotes are read. That means quoted names are not just decoration; they can decide whether a script parses at all.

Row limiting is another loud accent. PostgreSQL, MySQL, SQLite, and DuckDB commonly use LIMIT; SQL Server has TOP; Oracle supports row limiting with FETCH FIRST. All of them still need the old lesson from 2.3: no ORDER BY, no predictable "top" anything.

String concatenation is sneakier. || is standard and works in PostgreSQL, but lesson 2.1 already warned you that MySQL historically treats it as logical OR rather than concatenation. Function-shaped CONCAT() is often easier to translate, but even functions can differ around NULL, types, and argument coercion. Test the exact expression that will run.

Upsert is the write-side breakpoint. PostgreSQL uses ON CONFLICT; MySQL-family code uses ON DUPLICATE KEY UPDATE; and MySQL 8 still lacks standard MERGE. If the write changes money, inventory, or idempotency state, do not "translate by vibe." Rehearse the target rows and verify the affected-row behavior in the target engine.

Date math and text comparison

Dates look innocent because every engine can store calendar values. The break happens in functions: adding intervals, truncating to week or month, extracting fields, and formatting labels. PostgreSQL's DATE_TRUNC, INTERVAL, and typed date literals are clear in this course, but a warehouse, SQL Server database, MySQL app, or SQLite file may spell the same idea differently.

Text comparison is even more likely to pass tests and fail later. Collation means the database rules for sorting and comparing text. Case sensitivity, accent sensitivity, and locale rules can live at the database, column, or expression level depending on the engine. A test database with a case-insensitive collation can make an equality filter look correct; a production database with a case-sensitive collation can return a smaller grid.

The portable habit is not "lower everything forever." You learned in 16.4 that wrapping indexed columns in functions can kill index use unless you designed for it. The portable habit is to decide the rule—case-sensitive, case-insensitive, accent-sensitive, normalized stored value, expression index, special type—and make that rule part of the schema or query contract.

Feature detection and escape hatches

Before you move a serious query, answer these questions:

• Which engine and major version will run it?
• Which compatibility modes, SQL modes, or extensions are enabled?
• What are the database and column collations?
• How are booleans stored and rendered?
• Which date/time functions and time-zone rules does the target use?
• Does the target support the exact join, CTE, window, JSON, upsert, or merge feature you are using?
• Is there a fallback query shape if the nice feature is missing?
• Is there a target-engine test that proves the grid, command tag, and row count?

A dialect-specific escape hatch is a deliberate non-portable choice. It is fine when you label it. ON CONFLICT in a PostgreSQL app is good SQL. GROUP BY ALL in a DuckDB notebook can be good DuckDB. A SQL Server TOP query can be exactly right in T-SQL. The mistake is letting the accent masquerade as portable core.

What the SQL standard buys you

The SQL standard is a vocabulary and direction of travel, not a magic compatibility layer. It gives you shared ideas: tables, joins, CASE, GROUP BY, constraints, transactions, many window concepts, and newer directions such as SQL/JSON. It also leaves room for optional features, late adoption, extensions, and vendor-specific behavior.

So use the standard as your starting map. Prefer standard-shaped SQL when the cost is low. Then read the target engine's docs when the query crosses a breakpoint. Portability is not purity. It is making the boundary visible before the boundary makes your report wrong.

You now have the practical portability loop: write the portable core, circle the breakpoints, feature-detect the target, and label every escape hatch. Next, the course zooms out one more level: when relational SQL is the right tool, and when key-value, document, graph, or distributed SQL systems are a better fit.