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.
SQLite vs PostgreSQL syntax differences that break migrations: dynamic typing, AUTOINCREMENT vs SERIAL/IDENTITY, JOIN support, dates, and ALTER TABLE.
Quick answer
SQLite and PostgreSQL share the same core SQL, but their key syntax differences show up in typing, schema evolution, and configuration. SQLite uses dynamic typing while PostgreSQL enforces strict column types, SQLite's AUTOINCREMENT works differently from PostgreSQL's SERIAL and GENERATED ALWAYS AS IDENTITY, RIGHT and FULL OUTER JOIN only work in SQLite 3.39.0 and later, and SQLite's ALTER TABLE, date handling, and PRAGMA settings are all narrower than PostgreSQL's equivalents. Check these six areas before you port a schema or query between the two.
Can I use the same SQL file for both SQLite and PostgreSQL?
Only if you avoid dialect-specific syntax. AUTOINCREMENT, PRAGMA statements, and SQLite's flexible typing are the biggest sources of incompatibility. Stick to standard SELECT, JOIN, and GROUP BY clauses, use GENERATED ALWAYS AS IDENTITY instead of AUTOINCREMENT or SERIAL, and declare column types explicitly even though SQLite won't enforce them.
Does SQLite enforce FOREIGN KEY constraints?
Not by default. You have to run PRAGMA foreign_keys = ON on every connection, since the setting doesn't persist in the schema or the database file. PostgreSQL enforces foreign keys unconditionally, with no per-connection setup required.
What replaced SERIAL in PostgreSQL?
GENERATED ALWAYS AS IDENTITY, introduced in PostgreSQL 10. It behaves like SERIAL but manages the underlying sequence more safely and rejects manual inserts into the identity column unless you explicitly add OVERRIDING SYSTEM VALUE.
Why does my SQLite RIGHT JOIN throw a syntax error?
Your SQLite build is older than version 3.39.0, released in June 2022, which is when RIGHT and FULL OUTER JOIN support was added. Check your version with SELECT sqlite_version();, upgrade if you can, or rewrite the join using LEFT JOIN combined with UNION.
Can I add a CHECK constraint to an existing SQLite table?
Not directly. SQLite's ALTER TABLE can't add CHECK, UNIQUE, or NOT NULL constraints to an existing column. You have to create a new table with the constraint, copy the data across, drop the old table, and rename the new one.
SQLite and PostgreSQL both implement the SQL standard, so a plain SELECT usually runs unchanged on either engine. The real differences that break migrations and shared codebases show up in schema definitions, type enforcement, and a handful of clauses each database implements its own way.
That's because SQLite is a serverless, single-file, dynamically typed engine built for embedding, while PostgreSQL is a client-server database built for strict typing and concurrent multi-user workloads. Their design goals are different enough that syntax valid in one throws an error, or silently does the wrong thing, in the other. This matters most for teams that prototype against SQLite (a local file, an ORM's test database, a mobile app's on-device store) and then deploy the same schema and queries against PostgreSQL in production, since that's exactly when the gaps below turn into 2am incidents.
This guide covers the six places SQLite and PostgreSQL syntax diverge most: data typing, auto-incrementing keys, JOIN support, date and time handling, configuration, and ALTER TABLE, with runnable side-by-side examples for each dialect. If you're bridging MySQL instead of SQLite, our guide to MySQL vs PostgreSQL syntax differences covers that pair.
Why does SQLite accept any value in any column but PostgreSQL doesn't?
PostgreSQL is strictly typed: declare a column numeric and every insert must be a number, or something that casts cleanly to one. SQLite uses type affinity instead of enforcement, each column has a preferred storage class, but SQLite will still store a value of almost any type in almost any column. A NUMERIC column happily stores the string 'not a number' if it doesn't look numeric.
SQLite: type affinity accepts a bad insert
CREATE TABLE orders ( id INTEGER PRIMARY KEY, total NUMERIC);INSERT INTO orders (total) VALUES ('not a number');-- Succeeds: NUMERIC affinity only converts values-- that look numeric; SQLite stores the rest as-is.SELECT typeof(total) FROM orders; -- 'text'
PostgreSQL: strict typing rejects it
CREATE TABLE orders ( id serial PRIMARY KEY, total numeric);INSERT INTO orders (total) VALUES ('not a number');-- ERROR: invalid input syntax for type numeric: "not a number"
This isn't a bug SQLite forgot to fix, it's documented, deliberate behavior described in SQLite's type affinity documentation. If you want PostgreSQL-style enforcement inside SQLite, opt in with the STRICT table option, added in SQLite 3.37.0, which rejects the same insert that a normal SQLite table accepts silently. Without STRICT, the cost of this flexibility is that a typo in application code (passing a string where you meant an integer) becomes a silently corrupted column instead of a failed insert you'd catch in testing.
SQLite has a similar quoting quirk worth knowing about: it treats a double-quoted token as a string literal if it can't be resolved as an identifier, a documented backward-compatibility exception, not a general rule. See our guide to single vs double quotes in SQL for how quoting actually differs across dialects.
How do AUTOINCREMENT and SERIAL or IDENTITY differ for primary keys?
SQLite doesn't need an auto-increment keyword in the common case: declare a column INTEGER PRIMARY KEY and SQLite aliases it to the table's internal rowid, which already auto-increments. AUTOINCREMENT is a separate, stricter option, it guarantees IDs are never reused, even after deletes, by tracking the high-water mark in a hidden sqlite_sequence table. That guarantee costs a little performance, so use it only when you actually need IDs that are never reused.
SQLite: AUTOINCREMENT primary key
CREATE TABLE users ( id INTEGER PRIMARY KEY AUTOINCREMENT, email TEXT NOT NULL);INSERT INTO users (email) VALUES ('a@example.com');SELECT last_insert_rowid();
PostgreSQL's older idiom is SERIAL, a shorthand that creates a sequence and wires it up as the column default. Since PostgreSQL 10, GENERATED ALWAYS AS IDENTITY is the recommended replacement, it behaves like SERIAL but manages the sequence more safely and blocks accidental manual inserts into the identity column unless you explicitly add OVERRIDING SYSTEM VALUE. Neither AUTOINCREMENT nor IDENTITY is required, either database will happily use a UUID primary key instead, but if you're translating an existing integer-keyed schema between the two, this is the mapping that keeps behavior identical.
PostgreSQL: GENERATED ALWAYS AS IDENTITY
CREATE TABLE users ( id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email text NOT NULL);INSERT INTO users (email) VALUES ('a@example.com');SELECT lastval();
Does SQLite support RIGHT JOIN and FULL OUTER JOIN?
Only since version 3.39.0, released in June 2022. Earlier SQLite builds, which still ship inside older Python installations, mobile app runtimes, and embedded devices, support only INNER, LEFT, CROSS, and NATURAL joins. PostgreSQL has supported every join type for decades. This gap catches people off guard specifically because the failure mode is a plain syntax error rather than a warning about a missing feature, so it looks like a typo in your query instead of a version mismatch in your runtime.
Check your runtime's version with a query against sqlite_version() before assuming RIGHT or FULL OUTER JOIN will work. If you're stuck on an older build, emulate a FULL OUTER JOIN by combining two LEFT JOINs with UNION. And if SQLite throws a raw parser error rather than a clean missing-feature message, it's worth ruling out a typo first, see our guide to fixing "near X: syntax error" in SQLite.
SQLite before 3.39.0: emulating FULL OUTER JOIN
-- SQLite < 3.39.0 has no RIGHT or FULL OUTER JOIN.SELECT c.id, c.name, o.id AS order_idFROM customers cLEFT JOIN orders o ON o.customer_id = c.idUNIONSELECT c.id, c.name, o.id AS order_idFROM orders oLEFT JOIN customers c ON c.id = o.customer_idWHERE c.id IS NULL;
PostgreSQL (and SQLite 3.39.0+): native FULL OUTER JOIN
SELECT c.id, c.name, o.id AS order_idFROM customers cFULL OUTER JOIN orders o ON o.customer_id = c.id;
How do date and time types differ between SQLite and PostgreSQL?
SQLite has no dedicated date or time storage class. You store a timestamp as ISO-8601 text, a Julian day real number, or a Unix-epoch integer, and SQLite's date functions interpret whichever format you handed them at query time. Nothing stops different rows in the same column from using different formats.
SQLite: dates stored and computed as text
CREATE TABLE events ( id INTEGER PRIMARY KEY, happened_at TEXT -- ISO-8601 text; SQLite has no real date type);INSERT INTO events (happened_at) VALUES (datetime('now'));SELECT date(happened_at, '+1 day') FROM events;
PostgreSQL has dedicated date, time, timestamp, and timestamptz types, enforced and indexed like any other column. Timestamptz stores everything in UTC internally and converts to the session's timezone on the way out, a category of bugs that SQLite's text-based approach can't catch at write time. When you port a schema from SQLite to PostgreSQL, this is the point to decide explicitly whether a column is timezone-aware (timestamptz) or a naive local time (timestamp), rather than inheriting whatever format the SQLite text happened to be in.
PostgreSQL: native timestamptz with interval math
CREATE TABLE events ( id serial PRIMARY KEY, happened_at timestamptz NOT NULL DEFAULT now());SELECT happened_at + interval '1 day' FROM events;
PRAGMA vs SET: how do you configure each database?
SQLite has no server process and no config file. Behavior you'd expect to set once, foreign key enforcement, journaling mode, busy timeout, is controlled by PRAGMA statements that apply only to the current connection. Open a new connection and you're back to the defaults, which is why production SQLite code typically re-runs a small block of PRAGMAs immediately after connecting.
SQLite: PRAGMA is per-connection
-- Runs once per new connection; not stored in the schema.PRAGMA foreign_keys = ON;PRAGMA journal_mode = WAL;PRAGMA busy_timeout = 5000;
PostgreSQL separates the two scopes explicitly: SET changes a parameter for the current session only, while ALTER SYSTEM changes it cluster-wide and persists across connections. One consequence worth knowing: SQLite doesn't enforce foreign keys unless you set foreign key enforcement on for every connection; PostgreSQL enforces them unconditionally, no setup required.
PostgreSQL: SET vs ALTER SYSTEM
-- Session-level only:SET statement_timeout = '5s';-- Cluster-wide, persisted to postgresql.auto.conf:ALTER SYSTEM SET shared_buffers = '256MB';SELECT pg_reload_conf();
Why can't you ALTER TABLE the way you're used to in PostgreSQL?
PostgreSQL's ALTER TABLE can add or drop columns, add or drop constraints, and change column types in place. SQLite's ALTER TABLE is deliberately narrow: it can rename a table or column and add a column, but it cannot add a CHECK, UNIQUE, or NOT NULL constraint to an existing column, and DROP COLUMN fails if that column is part of a primary key, an index, or referenced anywhere else in the schema.
The standard SQLite workaround is the rebuild pattern: create a new table with the schema you actually want, copy the rows across, drop the old table, and rename the new one into place. SQLite documents this restriction deliberately: its file format and single-writer design make an in-place ALTER TABLE that rewrites every row far riskier than it is in PostgreSQL's multi-process architecture, so the library pushes that rewrite onto you instead of doing it silently.
SQLite: rebuild-and-rename to add a CHECK constraint
CREATE TABLE accounts_new ( id INTEGER PRIMARY KEY, balance NUMERIC NOT NULL CHECK (balance >= 0));INSERT INTO accounts_new SELECT * FROM accounts;DROP TABLE accounts;ALTER TABLE accounts_new RENAME TO accounts;
PostgreSQL: ALTER TABLE ADD CONSTRAINT directly
ALTER TABLE accounts ADD CONSTRAINT balance_non_negative CHECK (balance >= 0);
Before you port SQL between SQLite and PostgreSQL, check these 7 things
Look for implicit type coercion. SQLite will silently accept a mismatched type in an INSERT; PostgreSQL will reject it outright.
Translate AUTOINCREMENT correctly. Map SQLite's INTEGER PRIMARY KEY AUTOINCREMENT to PostgreSQL's GENERATED ALWAYS AS IDENTITY, not the legacy SERIAL.
Confirm your SQLite version supports the joins you use. RIGHT and FULL OUTER JOIN need version 3.39.0 or later.
Re-check every date column. Decide on a single storage format (ISO-8601 text is the safest SQLite default) and a timezone strategy before you convert to PostgreSQL's timestamptz.
Turn on foreign key enforcement in SQLite explicitly. PostgreSQL enforces foreign keys by default; SQLite does not, and it's easy to assume otherwise.
Plan constraint changes differently for each dialect. PostgreSQL supports ALTER TABLE ADD CONSTRAINT directly; SQLite needs the rebuild-and-rename pattern.
Run the ported SQL against both dialects before you ship it, not just the one you tested locally, the failures above are exactly the ones that pass code review and fail in production.
Wrapping up
SQLite and PostgreSQL agree on most of SQL, but they disagree hard on exactly the six things above: typing, auto-increment, JOIN support, dates, configuration, and ALTER TABLE. Test the boundary cases explicitly, insert a mismatched type, run a FULL OUTER JOIN, add a constraint after the fact, and you'll know your queries are actually compatible instead of assuming it.
If you maintain SQL that has to run correctly across both engines, sqlfmt formats and validates SQL, catching syntax errors and misspelled keywords, for SQLite, PostgreSQL, and three other dialects before you ever run it against a real database.