Everything you've written so far is standard SQL — the portable core that every database understands. That was on purpose. But the moment you start a real job, you'll meet code that looks a little different, and queries that run fine on one database and break (or quietly return a different number) on another. This lesson is the map of where that happens, so nothing surprises you.
There is one SQL standard, but every database adds its own shortcuts and quirks. Write the standard form by default; recognise the shortcuts because you'll read them in other people's code every day.
Casting: one portable form, several shortcuts
You learned to change a value's type with CAST(value AS TYPE). That works in every database ever made. But in Postgres, Snowflake, Redshift, and the DuckDB engine running these lessons, you'll constantly see a shorthand: value::TYPE.
They do exactly the same thing. Run it — both columns come back identical:
Paste d::DATE into SQL Server or MySQL and you get a syntax error — they've never heard of ::. Paste CAST(d AS DATE) and it just works. That's the whole reason we taught you CAST first: it's the one you can carry between jobs.
The trap that returns a wrong number with no error
A syntax error is the friendly kind of difference — the query refuses to run, so you know something's off. The dangerous kind is when the same query runs everywhere but returns a different answer. The classic is integer division.
| You write | DuckDB / PostgreSQL | SQL Server / older MySQL |
|---|---|---|
| SELECT 3 / 4 | 0.75 | 0 |
| SELECT 7 / 2 | 3.5 | 3 |
Same query, no error, completely different result. SQL Server sees two whole numbers and gives you a whole-number answer, throwing away the remainder. DuckDB and Postgres give you the decimal. Imagine that quietly happening inside an on-time-rate calculation.
The portable fix is to make sure at least one side isn't a plain integer — multiply by 1.0, or cast it:
This is why the rate cases here add * 100.0 or * 1.0 — not decoration, but a habit that keeps the number correct no matter which database runs it.
A few more you'll meet
You don't need to memorise these — just recognise them when they appear, and know there's a standard alternative.
| Job | Standard / portable | Dialect shortcuts you'll see |
|---|---|---|
| First N rows | FETCH FIRST 5 ROWS ONLY | LIMIT 5 (Postgres, MySQL, DuckDB) · TOP 5 (SQL Server) |
| Join text together | CONCAT(a, b) | a \|\| b (Postgres, DuckDB) · a + b (SQL Server) |
| Minutes between two times | (no single standard) | date_diff('minute', a, b) (DuckDB) · DATEDIFF(minute, a, b) (SQL Server) · EXTRACT(EPOCH FROM (b - a)) / 60 (Postgres) |
That last row is worth a look: there is genuinely no standard function for "minutes between two timestamps", so every database invented its own. When you reach the advanced sessionization case, you'll use DuckDB's date_diff — and now you know that the exact spelling is one of the things you'd change when moving that query elsewhere.
From practice
When you move a query from one database to another, check these three first — they're where most "it worked yesterday" bugs hide: casts (does :: need to become CAST?), division (will integers floor your rate to a wrong number?), and date functions (the names rarely match).
A colleague's query uses order_date::DATE and runs fine on their Postgres database, but errors on SQL Server. What is the most portable fix?
SELECT total_hits / total_attempts returns 0.83 on DuckDB but 0 on an older SQL Server, with no error on either. Why?
You now write the portable form by default and can read the shortcuts when you meet them. That's exactly how a working analyst operates: standard SQL in your own queries, and a quick mental translation when you open someone else's.