Dialect Differences

You choose the dialect once, with db.Use(dialect) or db.New(conn, dialect). The builder API is the same for all of them. This page lists what differs underneath and where a feature is missing.

Dialect	Constant
PostgreSQL	db.Postgres
MySQL	db.MySQL
MariaDB	db.MariaDB
SQLite	db.SQLite

Tested against: PostgreSQL 16, MySQL 8.4, MariaDB 11.4 and SQLite (through a pure-Go driver). Other server versions are untested. SQL Server is not supported.

The tables cover PostgreSQL, MySQL and SQLite. MariaDB is the MySQL column except where its section says otherwise.

On this page: Basics · Matching · Dates · Writes · Joins, locks, hints · JSON · Behaviour to know about · MariaDB

Basics

	PostgreSQL	MySQL	SQLite
Identifier quoting	"x"	`x`	"x"
Placeholders	$1, $2, ...	?	?
OFFSET with no LIMIT	OFFSET n	adds LIMIT 18446744073709551615	adds LIMIT -1
Random order	RANDOM()	RAND() / RAND(seed)	RANDOM()
Union members	(SELECT ...)	(SELECT ...)	SELECT * FROM (SELECT ...)
CTEMaterialized, CTENotMaterialized	as [not] materialized	ErrUnsupported	as [not] materialized
With on update / delete	before the verb	before the verb (MariaDB: ErrUnsupported)	before the verb
Insert, no columns	DEFAULT VALUES	() VALUES ()	DEFAULT VALUES

Matching and comparison

	PostgreSQL	MySQL	SQLite
Case-insensitive WhereLike	ILIKE	LIKE	LIKE
Case-sensitive WhereLike	LIKE	LIKE BINARY	GLOB (%→*, _→?, literal * ? escaped)
like on a column	column cast ::TEXT
WhereNullSafeEquals	IS NOT DISTINCT FROM	<=>	IS

Dates

Part	PostgreSQL	MySQL	SQLite
WhereDate	col::DATE	DATE(col)	STRFTIME('%Y-%m-%d', col)
WhereTime	col::TIME	TIME(col)	STRFTIME('%H:%M:%S', col)
WhereDay / Month / Year	EXTRACT(DAY FROM col)	DAY(col)	STRFTIME('%d', col)

SQLite compares the part as text (CAST(? AS TEXT)), which is why numeric day and month values are zero-padded for every dialect.

Writes

	PostgreSQL	MySQL	SQLite
Insert(...).Ignore()	ON CONFLICT DO NOTHING	INSERT IGNORE	INSERT OR IGNORE
Insert(...).Upsert(...)	ON CONFLICT (...) DO UPDATE SET x = "excluded"."x"	ON DUPLICATE KEY UPDATE x = VALUES(x)	as PostgreSQL
Returning(...)	Yes	ErrUnsupported	Yes (SQLite 3.35+)
Update/delete with a join or limit	ctid subquery	Native. Join plus order/limit is ErrUnsupported	rowid subquery
Update(...).FromJoins()	Yes	ErrUnsupported	ErrUnsupported
DB.Truncate	TRUNCATE ... RESTART IDENTITY	TRUNCATE TABLE	DELETE + reset sqlite_sequence (one transaction; reset skipped when no table uses AUTOINCREMENT)
Insert(...).ExecGetID	RETURNING "id"	last insert id	last insert id
Rows affected by an update	matched rows	changed rows only	matched rows
Upsert rows affected	per row	2 per updated row	per row

Joins, locks, hints

	PostgreSQL	MySQL	SQLite
JoinLateral, LeftJoinLateral	Yes	Yes (8.0.14+)	ErrUnsupported
StraightJoin	ErrUnsupported	Yes	ErrUnsupported
LockForUpdate	FOR UPDATE	FOR UPDATE	ignored
SharedLock	FOR SHARE	LOCK IN SHARE MODE	ignored
Lock("...")	the clause as given	the clause as given	ignored
UseIndex, IgnoreIndex	ignored	use index (...), ignore index (...)	ignored
ForceIndex	ignored	FORCE INDEX (...)	INDEXED BY name (one name)

JSON

The column->a->b[0] syntax works everywhere. The SQL differs. Full tables are on the JSON Support page.

	PostgreSQL	MySQL	SQLite
Text selector	"c"->'a'->>'b'	JSON_UNQUOTE(JSON_EXTRACT(...))	JSON_EXTRACT(...)
WhereJsonOverlaps	ErrUnsupported	JSON_OVERLAPS(...)	ErrUnsupported
JSON path update	JSONB_SET	JSON_SET	JSON_SET

Behaviour to know about

• HAVING and aliases: PostgreSQL does not allow output aliases in HAVING. Use HavingRaw with the expression.
• Strict GROUP BY: PostgreSQL and MySQL (ONLY_FULL_GROUP_BY) require selected columns to be grouped or aggregated. Count over a grouped query wraps it, so select the grouped column.
• Duplicate column names: Count over a grouped, distinct or limited query wraps it in a derived table. MySQL rejects duplicate column names in a derived table, so a join that selects * from two tables sharing a column name should select explicit columns.
• Affected rows on MySQL count changed rows, so updating a row to its current value reports 0.
• Version floors: SQLite RETURNING needs 3.35. MySQL lateral joins need 8.0.14. CTEs need MySQL 8.0, MariaDB 10.2 and SQLite 3.8.3. materialized needs PostgreSQL 12 or SQLite 3.35. The floors are the vendors' documented minimums and are untested here.
• Probed on the integration servers (MariaDB 11.4, MySQL 8.4, SQLite 3.53): MariaDB rejects WITH before UPDATE/DELETE and the MATERIALIZED hint. MySQL rejects the hint but accepts WITH before UPDATE/DELETE. MySQL and MariaDB reject WITH before INSERT, so a CTE on an insert is moved into the SELECT given to Using.
• Recursion depth on MySQL is capped by cte_max_recursion_depth (default 1000).

MariaDB

db.MariaDB uses MySQL's grammar: backtick quoting, ? placeholders, INSERT IGNORE, ON DUPLICATE KEY UPDATE, native UPDATE ... JOIN, DELETE alias FROM, STRAIGHT_JOIN, index hints, LOCK IN SHARE MODE, TRUNCATE TABLE.

The generated SQL is identical to MySQL's except in two places, both of which the server rejects otherwise:

	MySQL	MariaDB
JoinLateral, LeftJoinLateral	lateral (8.0.14+)	ErrUnsupported (no LATERAL)
JSON container value in an update (map or slice)	CAST(? AS JSON)	JSON_QUERY(?, '$') (no CAST AS JSON)

Verified on MariaDB 11.4 against a live server:

• JSON text selectors are unchanged (JSON_UNQUOTE(JSON_EXTRACT(...))).
• WhereJsonDoesntContain on a missing path includes the row (like SQLite). MySQL excludes it.
• json_overlaps and json_contains_path work, so WhereJsonOverlaps is available.
• RETURNING: the server supports it for INSERT and DELETE but not UPDATE. quill does not use it yet, so Returning(...) returns ErrUnsupported on MariaDB as on MySQL.
• Affected-row counts follow MySQL (an update reports changed rows).

Adding another dialect

A dialect is a Grammar implementation in internal/dialect plus a Dialect constant. See Contributing.