Readyset Docs

This page lists the SQL that Readyset caches, grouped by query shape, function, column type and connection setting, for PostgreSQL and MySQL and for both cache types. Each entry was run against a live Readyset instance. It was last tested on 2026-10-05 with PostgreSQL 17 and MySQL 8.4.

Deep caches are kept up to date automatically as data changes in the upstream database.
Shallow caches store results for a configurable time (TTL) and refresh them from the upstream database. They accept almost any read query.
A query that is not cached still works: Readyset passes it to the upstream database unchanged.

To check a specific query, run EXPLAIN CREATE CACHE FROM [YOUR_QUERY], or look at SHOW PROXIED QUERIES.

How to read the tables

Mark	Meaning
✔️	Cached, both with bind parameters (`$1` or `?`) and with values written inline in the SQL
Inline	Cached when values are written inline in the SQL, not with bind parameters
Partial	Some variants are cached, others are not
✖️	Passed through to the upstream database
–	Does not apply to this database

Query shapes

Each row is a query shape that was run against Readyset. Queries are SELECT statements over small test tables; the example shows the exact query that was tested. A ? marks a bind parameter (written $1 in PostgreSQL).

Select lists and projections

Query shape	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
Star `SELECT * FROM authors WHERE id = ?`	✔️	✔️	✔️	✔️
Alias expr `SELECT id, price * 2 AS dbl, title AS t FROM books WHERE id = ?`	✔️	✔️	✔️	✔️
Distinct multi `SELECT DISTINCT author_id, year FROM books WHERE price > ?`	✔️	✔️	✔️	✔️
Distinct in list `SELECT DISTINCT author_id FROM books WHERE author_id IN (?, ?)`	✖️	✔️	✖️	✔️
No table `SELECT 1 AS one`	✖️	✔️	✖️	✔️
Values only PostgreSQL: `VALUES (1), (2)` MySQL: `VALUES ROW(1), ROW(2)`	✖️	✖️	✖️	✖️
Case placeholder `SELECT id, CASE WHEN price > ? THEN 'hi' ELSE 'lo' END AS tier FROM books WHERE author_id = ?`	Inline	✔️	Inline	✔️
Const column `SELECT id, 'x' AS k, 42 AS n FROM books WHERE id = ?`	✔️	✔️	✔️	✔️
No where `SELECT id, title FROM books`	✔️	✔️	✔️	✔️
Count no where `SELECT COUNT(*) FROM books`	✔️	✔️	✔️	✔️
Qualified table PostgreSQL: `SELECT books.id, books.title FROM public.books WHERE books.id = ?` MySQL: `SELECT books.id, books.title FROM qs_mysql.books WHERE books.id = ?`	✔️	✔️	✔️	✔️
Quoted ident PostgreSQL: `SELECT "id", "title" FROM "books" WHERE "id" = ?` MySQL: SELECT `id`, `title` FROM `books` WHERE `id` = ?	✔️	✔️	✔️	✔️

Filters (WHERE)

Query shape	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
Eq and range `SELECT id FROM books WHERE author_id = ? AND year > ?`	Inline	✔️	Inline	✔️
Range two cols `SELECT id FROM books WHERE price > ? AND year < ?`	✔️	✔️	✔️	✔️
Lt le ge `SELECT id FROM books WHERE price <= ? AND year >= ?`	✔️	✔️	✔️	✔️
Neq `SELECT id FROM books WHERE author_id <> ?`	Inline	✔️	Inline	✔️
In single `SELECT id FROM books WHERE author_id IN (?)`	✔️	✔️	✔️	✔️
In three `SELECT id FROM books WHERE author_id IN (?, ?, ?)`	✔️	✔️	✔️	✔️
In literal only `SELECT id FROM books WHERE author_id IN (1, 3)`	✔️	✔️	✔️	✔️
Not in list `SELECT id FROM books WHERE author_id NOT IN (?, ?)`	Inline	✔️	Inline	✔️
Not between `SELECT id FROM books WHERE price NOT BETWEEN ? AND ?`	Inline	✔️	Inline	✔️
Like literal only `SELECT id FROM books WHERE title LIKE 'Alpha%'`	✔️	✔️	✔️	✔️
Not like `SELECT id FROM books WHERE title NOT LIKE ?`	Inline	✔️	Inline	✔️
Ilike `SELECT id FROM books WHERE title ILIKE ?`	Inline	✔️	–	–
Is null `SELECT id FROM books WHERE author_id IS NULL`	✔️	✔️	✔️	✔️
Is not null param `SELECT id FROM books WHERE author_id IS NOT NULL AND year > ?`	✔️	✔️	✔️	✔️
Is distinct from PostgreSQL: `SELECT id FROM books WHERE author_id IS DISTINCT FROM ?` MySQL: `SELECT id FROM books WHERE NOT (author_id <=> ?)`	Inline	✔️	✖️	✔️
Or diff cols `SELECT id FROM books WHERE author_id = ? OR year = ?`	Inline	✔️	Inline	✔️
And or mix `SELECT id FROM books WHERE author_id = ? AND (year > ? OR price < ?)`	Inline	✔️	Inline	✔️
Not eq `SELECT id FROM books WHERE NOT (author_id = ?)`	Inline	✔️	Inline	✔️
Row eq `SELECT id FROM books WHERE (author_id, year) = (?, ?)`	✔️	✔️	✔️	✔️
Expr gt `SELECT id FROM books WHERE price * 2 > ?`	Inline	✔️	Inline	✔️
Col expr eq `SELECT id FROM books WHERE year + 1 = ?`	Inline	✔️	Inline	✔️
Upper func `SELECT id FROM authors WHERE UPPER(name) = ?`	Inline	✔️	Inline	✔️
Col eq col `SELECT id FROM books WHERE id = author_id AND year > ?`	✔️	✔️	✔️	✔️
Regex PostgreSQL: `SELECT id FROM books WHERE title ~ ?` MySQL: `SELECT id FROM books WHERE title REGEXP ?`	✖️	✔️	✖️	✔️
Any array `SELECT id FROM books WHERE author_id = ANY(ARRAY[1, 3])`	✔️	✔️	–	–
Null safe eq `SELECT id FROM books WHERE author_id <=> ?`	–	–	✖️	✔️
String eq `SELECT id FROM authors WHERE country = ?`	✔️	✔️	✔️	✔️
In strings `SELECT id FROM authors WHERE country IN (?, ?)`	✔️	✔️	✔️	✔️
Decimal eq `SELECT id FROM books WHERE price = ?`	✔️	✔️	✔️	✔️

Ordering

Query shape	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
Simple `SELECT id, title FROM books WHERE author_id = ? ORDER BY price, id`	✔️	✔️	✔️	✔️
Desc multi `SELECT id FROM books WHERE year > ? ORDER BY price DESC, id`	✔️	✔️	✔️	✔️
Position `SELECT id, price FROM books WHERE year > ? ORDER BY 2, 1`	✔️	✔️	✔️	✔️
Expr `SELECT id FROM books WHERE year > ? ORDER BY price * -1, id`	✔️	✔️	✔️	✔️
Non selected `SELECT title FROM books WHERE author_id = ? ORDER BY price, id`	✔️	✔️	✔️	✔️
Nulls last PostgreSQL: `SELECT id FROM books WHERE year > ? ORDER BY author_id NULLS LAST, id` MySQL: `SELECT id FROM books WHERE year > ? ORDER BY author_id IS NULL, author_id, id`	✔️	✔️	✔️	✔️

Limits and paging

Query shape	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
Limit 1 `SELECT id, title FROM books WHERE author_id = ? LIMIT 1`	✔️	✔️	✔️	✔️
Limit param `SELECT id FROM books ORDER BY id LIMIT ?`	✔️	✔️	✔️	✔️
Offset literal `SELECT id FROM books ORDER BY id LIMIT 2 OFFSET 1`	✔️	✔️	✔️	✔️
Where limit offset param `SELECT id FROM books WHERE year > ? ORDER BY id LIMIT ? OFFSET ?`	✔️	✔️	✔️	✔️
Mysql limit comma `SELECT id FROM books ORDER BY id LIMIT 1, 2`	–	–	✔️	✔️
Fetch first `SELECT id FROM books ORDER BY id FETCH FIRST 2 ROWS ONLY`	✖️	✔️	–	–
Topk no where `SELECT id FROM books ORDER BY price DESC, id LIMIT 3`	✔️	✔️	✔️	✔️
Topk where join `SELECT b.id FROM books b JOIN authors a ON b.author_id = a.id WHERE a.country = ? ORDER BY b.price DESC, b.id LIMIT 2`	✔️	✔️	✔️	✔️

Aggregation and grouping

Query shape	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
Count star `SELECT COUNT(*) FROM books WHERE author_id = ?`	✔️	✔️	✔️	✔️
Count col `SELECT COUNT(author_id) FROM books WHERE year > ?`	✖️	✔️	✖️	✔️
Count distinct `SELECT COUNT(DISTINCT author_id) FROM books WHERE year > ?`	✖️	✔️	✖️	✔️
Min max `SELECT MIN(price), MAX(price) FROM books WHERE author_id = ?`	✔️	✔️	✔️	✔️
Sum int `SELECT SUM(year) FROM books WHERE author_id = ?`	✔️	✔️	✔️	✔️
Group having `SELECT author_id, COUNT() AS n FROM books WHERE year > ? GROUP BY author_id HAVING COUNT() > ?`	✖️	✔️	✖️	✔️
Group having sum `SELECT author_id, SUM(price) AS s FROM books WHERE year > ? GROUP BY author_id HAVING SUM(price) > ?`	✖️	✔️	✖️	✔️
Group expr `SELECT CASE WHEN year < 2010 THEN 'old' ELSE 'new' END AS era, COUNT(*) AS n FROM books WHERE price > ? GROUP BY CASE WHEN year < 2010 THEN 'old' ELSE 'new' END`	✖️	✔️	✖️	✔️
Group position `SELECT author_id, COUNT(*) AS n FROM books WHERE year > ? GROUP BY 1`	✖️	✔️	✖️	✔️
Group no agg `SELECT author_id FROM books WHERE year > ? GROUP BY author_id`	✔️	✔️	✔️	✔️
Group no agg extra key `SELECT author_id FROM books WHERE price > ? GROUP BY author_id, year`	✖️	✔️	✖️	✔️
Group multi col `SELECT author_id, year, COUNT(*) AS n FROM books WHERE price > ? GROUP BY author_id, year`	✖️	✔️	✖️	✔️
Group by pk dependent `SELECT a.id, a.name, COUNT(*) AS n FROM authors a JOIN books b ON b.author_id = a.id WHERE b.year > ? GROUP BY a.id`	✖️	✔️	✖️	✔️
Json agg PostgreSQL: `SELECT author_id, JSON_AGG(id) AS ids FROM books WHERE author_id = ? GROUP BY author_id` MySQL: `SELECT author_id, JSON_ARRAYAGG(id) AS ids FROM books WHERE author_id = ? GROUP BY author_id`	✖️	✔️	✖️	✔️
Array agg `SELECT author_id, ARRAY_AGG(id) AS ids FROM books WHERE author_id = ? GROUP BY author_id`	✔️	✔️	–	–
Stddev `SELECT STDDEV(price) FROM books WHERE year > ?`	✖️	✔️	✖️	✔️
Variance `SELECT VARIANCE(price) FROM books WHERE year > ?`	✖️	✔️	✖️	✔️
Bool and `SELECT BOOL_AND(price > 5) FROM books WHERE year > ?`	✖️	✔️	–	–
Bit and `SELECT BIT_AND(id) FROM books WHERE year > ?`	–	–	✖️	✔️
Filter `SELECT COUNT(*) FILTER (WHERE price > ?) FROM books WHERE year > ?`	✖️	✔️	–	–
Rollup PostgreSQL: `SELECT author_id, COUNT() AS n FROM books WHERE year > ? GROUP BY ROLLUP (author_id)` MySQL: `SELECT author_id, COUNT() AS n FROM books WHERE year > ? GROUP BY author_id WITH ROLLUP`	✖️	✔️	✖️	✔️
Grouping sets `SELECT author_id, year, COUNT(*) AS n FROM books WHERE price > ? GROUP BY GROUPING SETS ((author_id), (year))`	✖️	✔️	–	–
Percentile `SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) FROM books WHERE year > ?`	✖️	✔️	–	–
In list count `SELECT COUNT(*) FROM books WHERE author_id IN (?, ?)`	✖️	✔️	✖️	✔️
In list count distinct `SELECT COUNT(DISTINCT year) FROM books WHERE author_id IN (?, ?)`	✖️	✔️	✖️	✔️
Sum expr `SELECT SUM(price * 2) FROM books WHERE author_id = ?`	✔️	✔️	✔️	✔️
String agg sep PostgreSQL: `SELECT author_id, STRING_AGG(title, '; ') AS t FROM books WHERE author_id = ? GROUP BY author_id` MySQL: `SELECT author_id, GROUP_CONCAT(title SEPARATOR '; ') AS t FROM books WHERE author_id = ? GROUP BY author_id`	✔️	✔️	✔️	✔️
Sales group region `SELECT region, SUM(amount) AS total, COUNT(*) AS n FROM sales WHERE qty > ? GROUP BY region`	✖️	✔️	✖️	✔️
No where group `SELECT region, COUNT(*) AS n FROM sales GROUP BY region`	✔️	✔️	✔️	✔️
Count col eq `SELECT COUNT(author_id) FROM books WHERE author_id = ?`	✔️	✔️	✔️	✔️
Count distinct eq `SELECT COUNT(DISTINCT author_id) FROM books WHERE author_id = ?`	✔️	✔️	✔️	✔️
Group having eq `SELECT author_id, COUNT() AS n FROM books WHERE author_id = ? GROUP BY author_id HAVING COUNT() > ?`	Inline	✔️	Inline	✔️
Group having sum eq `SELECT author_id, SUM(price) AS s FROM books WHERE author_id = ? GROUP BY author_id HAVING SUM(price) > ?`	Inline	✔️	Inline	✔️
Group position eq `SELECT author_id, COUNT(*) AS n FROM books WHERE author_id = ? GROUP BY 1`	✔️	✔️	✔️	✔️
Group no agg eq `SELECT author_id FROM books WHERE author_id = ? GROUP BY author_id`	✔️	✔️	✔️	✔️
Group by pk dependent eq `SELECT a.id, a.name, COUNT(*) AS n FROM authors a JOIN books b ON b.author_id = a.id WHERE b.author_id = ? GROUP BY a.id`	✖️	✔️	✖️	✔️
Stddev eq `SELECT STDDEV(price) FROM books WHERE author_id = ?`	✖️	✔️	✖️	✔️
Variance eq `SELECT VARIANCE(price) FROM books WHERE author_id = ?`	✖️	✔️	✖️	✔️
Bool and eq `SELECT BOOL_AND(price > 5) FROM books WHERE author_id = ?`	✖️	✔️	–	–
Bit and eq `SELECT BIT_AND(id) FROM books WHERE author_id = ?`	–	–	✖️	✔️
Rollup eq PostgreSQL: `SELECT author_id, COUNT() AS n FROM books WHERE author_id = ? GROUP BY ROLLUP (author_id)` MySQL: `SELECT author_id, COUNT() AS n FROM books WHERE author_id = ? GROUP BY author_id WITH ROLLUP`	✖️	✔️	✖️	✔️
Percentile eq `SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) FROM books WHERE author_id = ?`	✖️	✔️	–	–

Joins

Query shape	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
Using `SELECT e.id, d.floor FROM emp e JOIN dept d USING (dept) WHERE e.id = ?`	✔️	✔️	✔️	✔️
Natural `SELECT id, floor FROM emp NATURAL JOIN dept WHERE id = ?`	✖️	✔️	✖️	✔️
Comma `SELECT b.id, a.name FROM books b, authors a WHERE b.author_id = a.id AND a.id = ?`	✔️	✔️	✔️	✔️
Cross `SELECT a.id, d.dept FROM authors a CROSS JOIN dept d WHERE a.id = ?`	✔️	✔️	✔️	✔️
Three way `SELECT b.id, a.name, r.rating FROM books b JOIN authors a ON b.author_id = a.id JOIN reviews r ON r.book_id = b.id WHERE a.id = ?`	✔️	✔️	✔️	✔️
Left where right `SELECT a.id, b.title FROM authors a LEFT JOIN books b ON b.author_id = a.id WHERE b.year > ?`	✔️	✔️	✔️	✔️
Left anti `SELECT a.id FROM authors a LEFT JOIN books b ON b.author_id = a.id WHERE b.id IS NULL AND a.id > ?`	✔️	✔️	✔️	✔️
Left on literal cond `SELECT a.id, b.title FROM authors a LEFT JOIN books b ON b.author_id = a.id AND b.year > 2005 WHERE a.id = ?`	✔️	✔️	Inline	✔️
Left on param `SELECT a.id, b.title FROM authors a LEFT JOIN books b ON b.author_id = a.id AND b.year > ? WHERE a.id = ?`	Inline	✔️	Inline	✔️
Left on func `SELECT a.id, b.title FROM authors a LEFT JOIN books b ON LOWER(b.title) = LOWER(a.name) WHERE a.id = ?`	✖️	✔️	✖️	✔️
Left multi eq `SELECT b.id, r.id AS rid FROM books b LEFT JOIN reviews r ON r.book_id = b.id AND r.id = b.id WHERE b.year > ?`	✔️	✔️	Inline	✔️
Right second `SELECT b.id, r.id AS rid FROM authors a JOIN books b ON b.author_id = a.id RIGHT JOIN reviews r ON r.book_id = b.id WHERE r.id = ?`	✖️	✔️	✖️	✔️
Non equi `SELECT a.id, b.id AS bid FROM authors a JOIN books b ON a.id < b.author_id WHERE a.id = ?`	✔️	✔️	✔️	✔️
On func `SELECT e.id, d.floor FROM emp e JOIN dept d ON LOWER(e.dept) = LOWER(d.dept) WHERE e.id > ?`	✔️	✔️	✔️	✔️
On expr `SELECT a.id, b.id AS bid FROM authors a JOIN books b ON b.author_id = a.id + 0 WHERE a.id = ?`	✔️	✔️	✔️	✔️
On or `SELECT a.id, b.id AS bid FROM authors a JOIN books b ON b.author_id = a.id OR b.author_id = 0 WHERE a.id = ?`	✔️	✔️	✔️	✔️
On extra nonkey filter `SELECT a.id, b.id AS bid FROM authors a JOIN books b ON b.author_id = a.id AND b.year > 2000 WHERE a.id = ?`	✔️	✔️	✔️	✔️
Self mgr `SELECT e.id, m.name AS mgr FROM emp e JOIN emp m ON e.mgr_id = m.id WHERE e.id = ?`	✔️	✔️	✔️	✔️
Self left `SELECT e.id, m.name AS mgr FROM emp e LEFT JOIN emp m ON e.mgr_id = m.id WHERE e.id = ?`	✔️	✔️	Inline	✔️
Keys both sides `SELECT b.id, r.id AS rid FROM books b JOIN reviews r ON r.book_id = b.id WHERE b.author_id = ? AND r.rating = ?`	✖️	✔️	✖️	✔️
Lateral `SELECT a.id, t.title FROM authors a, LATERAL (SELECT title FROM books b WHERE b.author_id = a.id) t WHERE a.id = ?`	✔️	✔️	✔️	✔️
Straight `SELECT STRAIGHT_JOIN b.id, a.name FROM books b JOIN authors a ON b.author_id = a.id WHERE a.id = ?`	–	–	✔️	✔️
Derived both `SELECT x.id, y.n FROM (SELECT id FROM authors) x JOIN (SELECT author_id, COUNT(*) AS n FROM books GROUP BY author_id) y ON y.author_id = x.id WHERE x.id = ?`	✔️	✔️	✔️	✔️
Agg join `SELECT a.id, c.n FROM authors a JOIN (SELECT author_id, COUNT(*) AS n FROM books GROUP BY author_id) c ON c.author_id = a.id WHERE a.id = ?`	✔️	✔️	✔️	✔️
Left agg `SELECT a.id, COUNT(b.id) AS n FROM authors a LEFT JOIN books b ON b.author_id = a.id WHERE a.country = ? GROUP BY a.id`	✔️	✔️	✔️	✔️
Join then order limit `SELECT a.name, b.title FROM authors a JOIN books b ON b.author_id = a.id WHERE a.id = ? ORDER BY b.price DESC, b.id LIMIT 1`	✔️	✔️	✔️	✔️
No where `SELECT a.name, b.title FROM authors a JOIN books b ON b.author_id = a.id`	✔️	✔️	✔️	✔️

Subqueries

Query shape	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
Not in notnull `SELECT id FROM authors WHERE id NOT IN (SELECT book_id FROM reviews WHERE rating > ?)`	Inline	✔️	Inline	✔️
Not in nullable `SELECT id FROM authors WHERE id NOT IN (SELECT author_id FROM books WHERE year > ?)`	Inline	✔️	Inline	✔️
Not in nullable null present `SELECT id FROM authors WHERE id NOT IN (SELECT author_id FROM books WHERE year > ?)`	Inline	Inline	Inline	✔️
Exists uncorrelated `SELECT id FROM authors WHERE EXISTS (SELECT 1 FROM books WHERE price > ?)`	Inline	✔️	Inline	✔️
Not exists corr `SELECT a.id FROM authors a WHERE NOT EXISTS (SELECT 1 FROM books b WHERE b.author_id = a.id AND b.year > ?)`	Inline	✔️	Inline	✔️
Corr nonequal `SELECT a.id FROM authors a WHERE EXISTS (SELECT 1 FROM books b WHERE b.price > a.id AND b.year > ?)`	✖️	✔️	✖️	✔️
Scalar select list `SELECT a.id, (SELECT COUNT(*) FROM books b WHERE b.author_id = a.id) AS n FROM authors a WHERE a.country = ?`	✔️	✔️	✔️	✔️
Exists select list `SELECT a.id, EXISTS (SELECT 1 FROM books b WHERE b.author_id = a.id) AS has FROM authors a WHERE a.country = ?`	✔️	✔️	✔️	✔️
Scalar pk `SELECT id FROM books WHERE price > (SELECT price FROM books WHERE id = ?)`	✔️	✔️	✔️	✔️
Any `SELECT id FROM books WHERE price > ANY (SELECT price FROM books WHERE author_id = ?)`	✖️	✔️	✖️	✔️
All `SELECT id FROM books WHERE price > ALL (SELECT price FROM books WHERE author_id = ?)`	✖️	✔️	✖️	✔️
In or pred `SELECT id FROM books WHERE author_id IN (SELECT id FROM authors WHERE country = ?) OR year > ?`	Inline	✔️	Inline	✔️
Derived `SELECT t.author_id, t.n FROM (SELECT author_id, COUNT(*) AS n FROM books GROUP BY author_id) t WHERE t.author_id = ?`	✔️	✔️	✔️	✔️
Derived limit `SELECT t.id FROM (SELECT id, price FROM books ORDER BY price DESC, id LIMIT 3) t WHERE t.price > ?`	✔️	✔️	✔️	✔️
Having scalar `SELECT author_id FROM books GROUP BY author_id HAVING COUNT() >= (SELECT COUNT() FROM authors WHERE id = ?)`	✔️	✔️	✔️	✔️
Nested in `SELECT id FROM authors WHERE id IN (SELECT author_id FROM books WHERE year > ?)`	Inline	✔️	Inline	✔️
In two level `SELECT id FROM books WHERE author_id IN (SELECT id FROM authors WHERE id IN (SELECT author_id FROM books WHERE year > ?))`	Inline	✔️	Inline	✔️
In literal `SELECT id FROM books WHERE author_id IN (SELECT id FROM authors WHERE country = 'US')`	✔️	✔️	✔️	✔️
Group by subq `SELECT COUNT(*) AS n FROM books GROUP BY (SELECT 1)`	✖️	✔️	✖️	✔️

Common table expressions (WITH)

Query shape	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
Param in body `WITH c AS (SELECT id, price FROM books WHERE author_id = ?) SELECT id, price FROM c WHERE price > 0`	Inline	✔️	Inline	✔️
Agg `WITH c AS (SELECT author_id, COUNT(*) AS n FROM books GROUP BY author_id) SELECT author_id, n FROM c WHERE author_id = ?`	✔️	✔️	✔️	✔️
Two joined `WITH x AS (SELECT id, name FROM authors), y AS (SELECT author_id, price FROM books) SELECT x.name, y.price FROM x JOIN y ON y.author_id = x.id WHERE x.id = ?`	✔️	✔️	✔️	✔️
Chain `WITH a1 AS (SELECT id, price, author_id FROM books), a2 AS (SELECT id, price FROM a1 WHERE price > 10) SELECT id FROM a2 WHERE price > ?`	✖️	✔️	✖️	✔️
Read twice `WITH c AS (SELECT id, author_id FROM books) SELECT c1.id FROM c c1 JOIN c c2 ON c1.author_id = c2.author_id WHERE c1.id = ?`	✖️	✔️	✖️	✔️
In subquery `WITH c AS (SELECT id FROM authors WHERE country = 'US') SELECT id FROM books WHERE author_id IN (SELECT id FROM c) AND year > ?`	✖️	✔️	✖️	✔️
Column list `WITH c (a, b) AS (SELECT id, price FROM books) SELECT a FROM c WHERE b > ?`	✖️	✔️	✖️	✔️
Recursive `WITH RECURSIVE t AS (SELECT id, mgr_id FROM emp WHERE id = ? UNION ALL SELECT e.id, e.mgr_id FROM emp e JOIN t ON e.mgr_id = t.id) SELECT id FROM t`	✖️	✔️	✖️	✔️
Materialized `WITH c AS MATERIALIZED (SELECT id, price FROM books WHERE price > 10) SELECT id FROM c WHERE price > ?`	✔️	✔️	–	–
Join table `WITH c AS (SELECT id, author_id FROM books WHERE year > 2000) SELECT c.id, a.name FROM c JOIN authors a ON a.id = c.author_id WHERE a.id = ?`	✔️	✔️	✔️	✔️
Window inside `WITH c AS (SELECT id, author_id, ROW_NUMBER() OVER (PARTITION BY author_id ORDER BY price, id) AS rn FROM books) SELECT id FROM c WHERE author_id = ?`	✔️	✔️	✔️	✔️
In list outer `WITH c AS (SELECT id, author_id FROM books) SELECT id FROM c WHERE author_id IN (?, ?)`	✔️	✔️	✔️	✔️
Literal body only `WITH c AS (SELECT id, price FROM books WHERE author_id = 1) SELECT id, price FROM c`	✔️	✔️	✔️	✔️

Set operations

Query shape	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
Union `SELECT id FROM books WHERE author_id = ? UNION SELECT id FROM authors WHERE id = ?`	✖️	✔️	✖️	✔️
Intersect `SELECT id FROM books WHERE author_id = ? INTERSECT SELECT id FROM authors WHERE id = ?`	✖️	✔️	✖️	✔️
Except `SELECT id FROM books WHERE year > ? EXCEPT SELECT id FROM authors WHERE id = ?`	✖️	✔️	✖️	✔️
Union derived `SELECT t.id FROM (SELECT id FROM books WHERE author_id = ? UNION ALL SELECT id FROM authors) t`	✖️	✔️	✖️	✔️
Union three `SELECT id FROM books WHERE author_id = ? UNION ALL SELECT id FROM authors WHERE id = ? UNION ALL SELECT id FROM reviews WHERE id = ?`	✖️	✔️	✖️	✔️
Union order limit `(SELECT id FROM books WHERE author_id = ?) UNION ALL (SELECT id FROM authors WHERE id = ?) ORDER BY id LIMIT 3`	✖️	✔️	✖️	✔️
Union in cte `WITH c AS (SELECT id FROM books UNION ALL SELECT id FROM authors) SELECT id FROM c WHERE id = ?`	✖️	✔️	✖️	✔️

Window functions

Query shape	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
Rank `SELECT id, RANK() OVER (PARTITION BY author_id ORDER BY price) AS r FROM books WHERE year > ?`	✖️	✔️	✖️	✔️
Dense rank `SELECT id, DENSE_RANK() OVER (PARTITION BY author_id ORDER BY price) AS r FROM books WHERE year > ?`	✖️	✔️	✖️	✔️
Sum partition `SELECT id, SUM(price) OVER (PARTITION BY author_id) AS s FROM books WHERE year > ?`	✖️	✔️	✖️	✔️
Running sum `SELECT id, SUM(price) OVER (PARTITION BY author_id ORDER BY id) AS s FROM books WHERE year > ?`	✖️	✔️	✖️	✔️
Two windows `SELECT id, ROW_NUMBER() OVER (PARTITION BY author_id ORDER BY id) AS rn, SUM(price) OVER (PARTITION BY author_id) AS s FROM books WHERE year > ?`	✖️	✔️	✖️	✔️
Lag `SELECT id, LAG(price) OVER (PARTITION BY author_id ORDER BY id) AS prev FROM books WHERE year > ?`	✖️	✔️	✖️	✔️
Lead `SELECT id, LEAD(price) OVER (PARTITION BY author_id ORDER BY id) AS nxt FROM books WHERE year > ?`	✖️	✔️	✖️	✔️
Ntile `SELECT id, NTILE(2) OVER (ORDER BY id) AS bucket FROM books WHERE year > ?`	✖️	✔️	✖️	✔️
First value `SELECT id, FIRST_VALUE(price) OVER (PARTITION BY author_id ORDER BY id) AS f FROM books WHERE year > ?`	✖️	✔️	✖️	✔️
Frame rows `SELECT id, SUM(price) OVER (PARTITION BY author_id ORDER BY id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS s FROM books WHERE year > ?`	✖️	✔️	✖️	✔️
Named window `SELECT id, ROW_NUMBER() OVER w AS rn FROM books WHERE year > ? WINDOW w AS (PARTITION BY author_id ORDER BY id)`	✖️	✔️	✖️	✔️
No partition `SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM books WHERE year > ?`	✖️	✔️	✖️	✔️
With in list `SELECT id, ROW_NUMBER() OVER (PARTITION BY author_id ORDER BY id) AS rn FROM books WHERE author_id IN (?, ?)`	✖️	✔️	✖️	✔️
Top1 per group `SELECT t.id, t.author_id FROM (SELECT id, author_id, ROW_NUMBER() OVER (PARTITION BY author_id ORDER BY price DESC, id) AS rn FROM books WHERE year > ?) t WHERE t.rn = 1`	Inline	✔️	Inline	✔️
Count over `SELECT id, COUNT(*) OVER (PARTITION BY author_id) AS n FROM books WHERE year > ?`	✖️	✔️	✖️	✔️
Avg over `SELECT id, AVG(price) OVER (PARTITION BY author_id) AS a FROM books WHERE year > ?`	✖️	✔️	✖️	✔️
Sales running `SELECT id, SUM(amount) OVER (PARTITION BY region ORDER BY sold_on, id) AS run FROM sales WHERE qty > ?`	✖️	✔️	✖️	✔️
Rank eq `SELECT id, RANK() OVER (PARTITION BY author_id ORDER BY price) AS r FROM books WHERE author_id = ?`	✔️	✔️	✔️	✔️
Dense rank eq `SELECT id, DENSE_RANK() OVER (PARTITION BY author_id ORDER BY price) AS r FROM books WHERE author_id = ?`	✔️	✔️	✔️	✔️
Sum partition eq `SELECT id, SUM(price) OVER (PARTITION BY author_id) AS s FROM books WHERE author_id = ?`	✔️	✔️	✔️	✔️
Running sum eq `SELECT id, SUM(price) OVER (PARTITION BY author_id ORDER BY id) AS s FROM books WHERE author_id = ?`	✔️	✔️	✔️	✔️
Two windows eq `SELECT id, ROW_NUMBER() OVER (PARTITION BY author_id ORDER BY id) AS rn, SUM(price) OVER (PARTITION BY author_id) AS s FROM books WHERE author_id = ?`	✖️	✔️	✖️	✔️
Lag eq `SELECT id, LAG(price) OVER (PARTITION BY author_id ORDER BY id) AS prev FROM books WHERE author_id = ?`	✖️	✔️	✖️	✔️
Lead eq `SELECT id, LEAD(price) OVER (PARTITION BY author_id ORDER BY id) AS nxt FROM books WHERE author_id = ?`	✖️	✔️	✖️	✔️
Ntile eq `SELECT id, NTILE(2) OVER (ORDER BY id) AS bucket FROM books WHERE author_id = ?`	✖️	✔️	✖️	✔️
First value eq `SELECT id, FIRST_VALUE(price) OVER (PARTITION BY author_id ORDER BY id) AS f FROM books WHERE author_id = ?`	✖️	✔️	✖️	✔️
Frame rows eq `SELECT id, SUM(price) OVER (PARTITION BY author_id ORDER BY id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS s FROM books WHERE author_id = ?`	✖️	✔️	✖️	✔️
Named window eq `SELECT id, ROW_NUMBER() OVER w AS rn FROM books WHERE author_id = ? WINDOW w AS (PARTITION BY author_id ORDER BY id)`	✖️	✔️	✖️	✔️
No partition eq `SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM books WHERE author_id = ?`	✔️	✔️	✔️	✔️
Top1 per group eq `SELECT t.id, t.author_id FROM (SELECT id, author_id, ROW_NUMBER() OVER (PARTITION BY author_id ORDER BY price DESC, id) AS rn FROM books WHERE author_id = ?) t WHERE t.rn = 1`	✔️	✔️	✔️	✔️
Count over eq `SELECT id, COUNT(*) OVER (PARTITION BY author_id) AS n FROM books WHERE author_id = ?`	✔️	✔️	✔️	✔️
Avg over eq `SELECT id, AVG(price) OVER (PARTITION BY author_id) AS a FROM books WHERE author_id = ?`	✔️	✔️	✔️	✔️

Locking reads

Query shape	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
For update `SELECT id FROM books WHERE id = ? FOR UPDATE`	✖️	✖️	✖️	✖️
For share `SELECT id FROM books WHERE id = ? FOR SHARE`	✖️	✖️	✖️	✖️
Skip locked `SELECT id FROM books WHERE id = ? FOR UPDATE SKIP LOCKED`	✖️	✖️	✖️	✖️
Lock in share `SELECT id FROM books WHERE id = ? LOCK IN SHARE MODE`	–	–	✖️	✖️

Other

Query shape	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
Select into var `SELECT id INTO @v FROM books WHERE id = ?`	–	–	✖️	✖️
Sql calc found rows `SELECT SQL_CALC_FOUND_ROWS id FROM books WHERE year > ? LIMIT 2`	–	–	✔️	✔️
Index hint `SELECT id FROM books USE INDEX (books_author_idx) WHERE author_id = ?`	–	–	✔️	✔️
Distinct count star `SELECT COUNT(*) FROM (SELECT DISTINCT author_id FROM books) t`	✔️	✔️	✔️	✔️
Select star join `SELECT * FROM books b JOIN authors a ON b.author_id = a.id WHERE a.id = ?`	✔️	✔️	✔️	✔️
Dup column names `SELECT b.id, a.id FROM books b JOIN authors a ON b.author_id = a.id WHERE a.id = ?`	✔️	✔️	✔️	✔️
In with null `SELECT id FROM books WHERE author_id IN (?, NULL)`	Inline	✔️	Inline	✔️

Functions and operators

Each expression was tested in the select list and in a WHERE filter. A check mark means both uses are cached.

Operators

Expression	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
`n + 1`	✔️	✔️	✔️	✔️
`n - 1`	✔️	✔️	✔️	✔️
`n * 2`	✔️	✔️	✔️	✔️
`n / 2`	✔️	✔️	Inline	✔️
`n % 3`	✔️	✔️	✔️	✔️
`s \|\| t`	✔️	✔️	–	–
`s \|\| t`	–	–	Inline	✔️
`s LIKE 'He%'`	✔️	✔️	✔️	✔️
`s NOT LIKE 'He%'`	✔️	✔️	✔️	✔️
`s ILIKE 'he%'`	✔️	✔️	–	–
`b IS TRUE`	✔️	✔️	✔️	✔️
`b IS NOT FALSE`	✔️	✔️	✔️	✔️
`n IS NULL`	✔️	✔️	✔️	✔️
`n IS DISTINCT FROM 3`	✔️	✔️	–	–
`n BETWEEN 1 AND 10`	✔️	✔️	✔️	✔️
`n IN (1, 7, 9)`	✔️	✔️	✔️	✔️
`NOT (n > 5)`	✔️	✔️	✔️	✔️
`(n > 5 AND d < 20) OR f > 100`	✔️	✔️	✔️	✔️
`CASE WHEN n > 5 THEN 'big' ELSE 'small' END`	✔️	✔️	✔️	✔️
`CASE n WHEN 7 THEN 'seven' ELSE 'other' END`	✔️	✔️	✔️	✔️
`(n, d) = (7, 12.35)`	✔️	✔️	✔️	✔️
`s ~ 'Hel'` (PostgreSQL) `s REGEXP 'Hel'` (MySQL)	✖️	✔️	✖️	✔️
`s RLIKE 'Hel'`	–	–	✖️	✔️
`s SIMILAR TO 'Hel%'`	✖️	✔️	–	–
`n <=> 7`	–	–	✖️	✔️
`n & 3`	✖️	✔️	✖️	✔️
`n \| 8`	✖️	✔️	✖️	✔️
`n << 1`	✖️	✔️	✖️	✔️
`n # 5` (PostgreSQL) `n ^ 5` (MySQL)	✖️	✔️	✖️	✔️
`n XOR 1`	–	–	✖️	✔️
`n DIV 2`	–	–	✖️	✔️
`s LIKE 'H!%' ESCAPE '!'`	✖️	✔️	✖️	✔️
`-n`	✔️	✔️	✔️	✔️
`CONCAT_WS(',', s, NULL, t)`	✔️	✔️	✔️	✔️

String functions

Expression	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
`CONCAT(s, t)`	✔️	✔️	✔️	✔️
`CONCAT_WS('-', s, t)`	✔️	✔️	✔️	✔️
`SUBSTRING(s, 2, 3)`	✔️	✔️	✔️	✔️
`SUBSTRING(s FROM 2 FOR 3)`	✔️	✔️	✔️	✔️
`SUBSTR(s, 2, 3)`	✔️	✔️	✔️	✔️
`SPLIT_PART(s, ' ', 1)`	✔️	✔️	–	–
`SUBSTRING_INDEX(s, ' ', 1)`	–	–	✖️	✔️
`LOWER(s)`	✔️	✔️	✔️	✔️
`UPPER(s)`	✔️	✔️	✔️	✔️
`LENGTH(s)`	✔️	✔️	✔️	✔️
`CHAR_LENGTH(s)`	✔️	✔️	✔️	✔️
`OCTET_LENGTH(s)`	✔️	✔️	✔️	✔️
`ASCII(s)`	✔️	✔️	✔️	✔️
`ENCODE(CONVERT_TO(s, 'UTF8'), 'hex')` (PostgreSQL) `HEX(s)` (MySQL)	✖️	✔️	✔️	✔️
`TRIM(s)`	✖️	✔️	✖️	✔️
`LTRIM(s)`	✖️	✔️	✖️	✔️
`RTRIM(s)`	✖️	✔️	✖️	✔️
`TRIM(BOTH ' ' FROM s)`	✖️	✔️	✖️	✔️
`BTRIM(s)`	✖️	✔️	–	–
`POSITION('o' IN s)`	✖️	✔️	✖️	✔️
`REPLACE(s, 'o', '0')`	✖️	✔️	✖️	✔️
`LPAD(s, 15, '*')`	✖️	✔️	✖️	✔️
`RPAD(s, 15, '*')`	✖️	✔️	✖️	✔️
`LEFT(s, 3)`	✖️	✔️	✖️	✔️
`RIGHT(s, 3)`	✖️	✔️	✖️	✔️
`LOCATE('o', s)`	–	–	✖️	✔️
`INSTR(s, 'o')`	–	–	✖️	✔️
`STRPOS(s, 'o')`	✖️	✔️	–	–
`REVERSE(s)`	✖️	✔️	✖️	✔️
`MD5(s)`	✖️	✔️	✖️	✔️
`REPEAT(s, 2)`	✖️	✔️	✖️	✔️
`INITCAP(t)`	✖️	✔️	–	–
`TRANSLATE(s, 'lo', 'xy')`	✖️	✔️	–	–
`OVERLAY(s PLACING 'xx' FROM 2 FOR 2)`	✖️	✔️	–	–
`REGEXP_REPLACE(s, 'o', '0')`	✖️	✔️	✖️	✔️
`REGEXP_LIKE(s, 'Hel')`	–	–	✖️	✔️
`REGEXP_MATCH(s, 'H(el)')`	✖️	✔️	–	–
`TO_CHAR(d, '999.99')`	✖️	✔️	–	–
`FORMAT(d, 1)`	–	–	✖️	✔️
`STRCMP(s, 'abc')`	–	–	✖️	✔️
`QUOTE(s)`	–	–	✖️	✔️
`CONCAT(n, s)`	✔️	✔️	✔️	✔️
`CHR(65)`	✖️	✔️	–	–
`CHAR(65)`	–	–	✖️	✔️
`SPACE(3)`	–	–	✖️	✔️
`STARTS_WITH(s, 'He')`	✖️	✔️	–	–

Math functions

Expression	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
`ROUND(d)`	✔️	✔️	✔️	✔️
`ROUND(d, 1)`	✔️	✔️	✔️	✔️
`GREATEST(n, 3, 10)`	✔️	✔️	✔️	✔️
`LEAST(n, 3, 10)`	✔️	✔️	✔️	✔️
`MOD(n, 3)`	✔️	✔️	✔️	✔️
`ABS(n)`	✖️	✔️	✖️	✔️
`CEIL(d)`	✖️	✔️	✖️	✔️
`CEILING(d)`	✖️	✔️	✖️	✔️
`FLOOR(d)`	✖️	✔️	✖️	✔️
`SIGN(n)`	✖️	✔️	✖️	✔️
`POWER(n, 2)`	✖️	✔️	✖️	✔️
`SQRT(f)`	✖️	✔️	✖️	✔️
`LN(f)`	✖️	✔️	✖️	✔️
`LOG10(f)`	✖️	✔️	✖️	✔️
`EXP(f)`	✖️	✔️	✖️	✔️
`TRUNC(d, 1)`	✖️	✔️	–	–
`TRUNCATE(d, 1)`	–	–	✖️	✔️
`PI()`	✖️	✔️	✖️	✔️
`DEGREES(f)`	✖️	✔️	✖️	✔️
`SIN(f)`	✖️	✔️	✖️	✔️
`RANDOM()` (PostgreSQL) `RAND()` (MySQL)	✖️	✔️	✖️	✔️
`DIV(n, 2)`	✖️	✔️	–	–
`f / 3.0`	✔️	✔️	✔️	✔️

Date and time

Expression	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
`EXTRACT(YEAR FROM dt)`	✔️	✔️	✔️	✔️
`EXTRACT(MONTH FROM ts)`	✔️	✔️	✔️	✔️
`EXTRACT(EPOCH FROM ts)`	✔️	✔️	–	–
`DATE_TRUNC('month', ts)`	✔️	✔️	–	–
`DATE(ts)`	✔️	✔️	✔️	✔️
`MONTH(dt)`	–	–	✔️	✔️
`DAYOFWEEK(dt)`	–	–	✔️	✔️
`DATE_FORMAT(ts, '%Y-%m')`	–	–	✔️	✔️
`ADDTIME(ts, '01:00:00')`	–	–	✔️	✔️
`TIMEDIFF(ts, '2024-03-15 08:00:00')`	–	–	✔️	✔️
`CONVERT_TZ(ts, '+00:00', '+02:00')`	–	–	✔️	✔️
`tstz AT TIME ZONE 'America/New_York'`	✔️	✔️	–	–
`NOW()`	✖️	✔️	✖️	✔️
`CURRENT_TIMESTAMP`	✖️	✔️	✖️	✔️
`CURRENT_DATE`	✖️	✔️	✖️	✔️
`CURDATE()`	–	–	✖️	✔️
`ts + INTERVAL '1 day'`	✖️	✔️	–	–
`ts + INTERVAL 1 DAY`	–	–	✖️	✔️
`DATE_ADD(dt, INTERVAL 1 DAY)`	–	–	✖️	✔️
`DATE_SUB(dt, INTERVAL 1 DAY)`	–	–	✖️	✔️
`DATEDIFF(dt, '2024-01-01')`	–	–	✖️	✔️
`TIMESTAMPDIFF(DAY, '2024-01-01', dt)`	–	–	✖️	✔️
`dt - DATE '2024-01-01'`	✖️	✔️	–	–
`YEAR(dt)`	–	–	✖️	✔️
`DAY(dt)`	–	–	✖️	✔️
`UNIX_TIMESTAMP(ts)`	–	–	✖️	✔️
`STR_TO_DATE('2024-01-05', '%Y-%m-%d')`	–	–	✖️	✔️
`TO_DATE('2024-01-05', 'YYYY-MM-DD')`	✖️	✔️	–	–
`TO_TIMESTAMP(86400)`	✖️	✔️	–	–
`TO_CHAR(ts, 'YYYY-MM')`	✖️	✔️	–	–
`AGE(ts, TIMESTAMP '2024-01-01')`	✖️	Inline	–	–
`dt > DATE '2024-01-01'`	✖️	✔️	✖️	✔️
`ts > TIMESTAMP '2024-01-01 00:00:00'`	✖️	✔️	✖️	✔️
`DATE_PART('year', ts)`	✖️	✔️	–	–
`LAST_DAY(dt)`	–	–	✖️	✔️
`DAYNAME(dt)`	–	–	✖️	✔️
`MAKEDATE(2024, 60)`	–	–	✖️	✔️
`HOUR(ts)`	–	–	✖️	✔️
`WEEKDAY(dt)`	–	–	✖️	✔️
`CAST(ts AS DATE)`	✔️	✔️	✔️	✔️
`ts + 0`	–	–	Inline	✔️
`CLOCK_TIMESTAMP()`	✖️	✔️	–	–
`CURRENT_TIME`	✖️	Inline	✖️	✔️
`UTC_TIMESTAMP()`	–	–	✖️	✔️

Conditional expressions

Expression	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
`COALESCE(n, 0)`	✔️	✔️	✔️	✔️
`IFNULL(n, 0)`	–	–	✔️	✔️
`NULLIF(n, 8)`	✖️	✔️	✖️	✔️
`IF(n > 5, 'a', 'b')`	–	–	✖️	✔️
`ISNULL(n)`	–	–	✖️	✔️
`COALESCE(NULL, n, 0)`	Partial	✔️	Inline	✔️
`CASE WHEN s IS NULL THEN 'none' ELSE s END`	✔️	✔️	✔️	✔️

Type casts

Expression	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
`CAST(n AS text)` (PostgreSQL) `CAST(n AS CHAR)` (MySQL)	✔️	✔️	✔️	✔️
`n::text`	✔️	✔️	–	–
`CAST(d AS integer)` (PostgreSQL) `CAST(d AS SIGNED)` (MySQL)	✔️	✔️	✔️	✔️
`d::int`	✔️	✔️	–	–
`CAST(n AS double precision)` (PostgreSQL) `CAST(n AS DOUBLE)` (MySQL)	✔️	✔️	✔️	✔️
`n::float8`	✖️	✔️	–	–
`CAST(n AS DECIMAL(10,2))`	✔️	✔️	✔️	✔️
`CAST('42' AS integer)` (PostgreSQL) `CAST('42' AS SIGNED)` (MySQL)	✔️	✔️	✔️	✔️
`CAST(dt::text AS date)` (PostgreSQL) `CAST(CAST(dt AS CHAR) AS DATE)` (MySQL)	✔️	✔️	✔️	✔️
`CAST('1 day' AS interval)`	✖️	Inline	–	–
`CONVERT(n, CHAR)`	–	–	✔️	✔️
`CAST(d AS CHAR)`	–	–	✔️	✔️
`CAST('10.0.0.1' AS inet)`	✖️	✔️	–	–
`CAST('{"a":1}' AS json)` (PostgreSQL) `CAST('{"a":1}' AS JSON)` (MySQL)	✔️	✔️	✔️	✔️
`CAST(n AS UNSIGNED)`	–	–	✔️	✔️
`CAST(n AS bigint)`	✔️	✔️	–	–

JSON

Expression	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
`jb -> 'a'`	✔️	✔️	–	–
`jb ->> 'a'`	✔️	✔️	–	–
`jb #> '{c,d}'`	✔️	✔️	–	–
`jb #>> '{c,d}'`	✔️	✔️	–	–
`js -> 'a'`	✔️	✔️	–	–
`js ->> 'a'`	✔️	✔️	–	–
`jb @> '{"a":1}'`	✔️	✔️	–	–
`jb <@ '{"a":1,"b":[1,2,3],"c":{"d":"x"},"e":2}'`	✔️	✔️	–	–
`jb \? 'a'`	✔️	✔️	–	–
`jb \?\| array['a','z']`	✔️	✔️	–	–
`jb \?& array['a','b']`	✔️	✔️	–	–
`jb \|\| '{"z":1}'`	✔️	✔️	–	–
`jb - 'a'`	✔️	✔️	–	–
`jb #- '{c,d}'`	✔️	✔️	–	–
`jsonb_build_object('k', n)`	✔️	✔️	–	–
`json_build_object('k', n)`	✔️	✔️	–	–
`jsonb_build_array(n, s)`	✔️	✔️	–	–
`json_build_array(n, s)`	✔️	✔️	–	–
`jsonb_set(jb, '{a}', '2')`	✔️	✔️	–	–
`jsonb_insert(jb, '{z}', '1')`	✔️	✔️	–	–
`jsonb_extract_path(jb, 'c', 'd')`	✔️	✔️	–	–
`jsonb_extract_path_text(jb, 'c', 'd')`	✔️	✔️	–	–
`jsonb_typeof(jb)`	✔️	✔️	–	–
`jsonb_array_length(jb -> 'b')`	✔️	✔️	–	–
`jsonb_strip_nulls(jb)`	✔️	✔️	–	–
`to_json(s)`	✖️	✔️	–	–
`to_jsonb(s)`	✖️	✔️	–	–
`row_to_json(fx)`	✖️	✔️	–	–
`jsonb_each(jb)`	✖️	Inline	–	–
`jsonb_array_elements(jb -> 'b')`	✖️	✔️	–	–
`jb @\? '$.a'`	✖️	✔️	–	–
`jb @@ '$.a == 1'`	✖️	✔️	–	–
`jsonb_path_query_first(jb, '$.a')`	✖️	✔️	–	–
`jsonb_pretty(jb)`	✔️	✔️	–	–
`JSON_OBJECT('k', n)`	–	–	✔️	✔️
`JSON_OVERLAPS(JSON_EXTRACT(j, '$.b'), '[1, 9]')`	–	–	✖️	✔️
`JSON_DEPTH(j)`	–	–	✔️	✔️
`JSON_VALID(s)`	–	–	✔️	✔️
`JSON_QUOTE(s)`	–	–	✔️	✔️
`j -> '$.a'`	–	–	✖️	✔️
`j ->> '$.a'`	–	–	✖️	✔️
`JSON_EXTRACT(j, '$.a')`	–	–	✖️	✔️
`JSON_UNQUOTE(JSON_EXTRACT(j, '$.c.d'))`	–	–	✖️	✔️
`JSON_CONTAINS(j, '1', '$.a')`	–	–	✖️	✔️
`JSON_LENGTH(j)`	–	–	✖️	✔️
`JSON_KEYS(j)`	–	–	✖️	✔️
`JSON_ARRAY(n, s)`	–	–	✖️	✔️
`7 MEMBER OF (JSON_EXTRACT(j, '$.b'))`	–	–	✖️	✔️
`JSON_SET(j, '$.a', 2)`	–	–	✖️	✔️
`JSON_TYPE(j)`	–	–	✖️	✔️
`JSON_SEARCH(j, 'one', 'x')`	–	–	✖️	✔️
`JSON_MERGE_PATCH(j, '{"z":1}')`	–	–	✖️	✔️
`j`	–	–	✔️	✔️

Arrays (PostgreSQL)

Expression	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
`ARRAY[n, 1]`	✔️	✔️	–	–
`ARRAY(SELECT id FROM fx WHERE id < 3)`	Partial	✔️	–	–
`n = ANY(arr)`	✔️	✔️	–	–
`arr @> ARRAY[1]`	✔️	✔️	–	–
`arr <@ ARRAY[1,2,3,4]`	✔️	✔️	–	–
`arr && ARRAY[3,9]`	✔️	✔️	–	–
`arr \|\| ARRAY[9]`	✔️	✔️	–	–
`array_to_string(arr, ',')`	✔️	✔️	–	–
`arr[1]`	✖️	✔️	–	–
`arr[1:2]`	✖️	✔️	–	–
`UNNEST(arr)`	✖️	✔️	–	–
`array_length(arr, 1)`	✖️	✔️	–	–
`cardinality(arr)`	✖️	✔️	–	–
`array_append(arr, 9)`	✖️	✔️	–	–
`array_position(arr, 2)`	✖️	✔️	–	–
`array_remove(arr, 2)`	✖️	✔️	–	–
`string_to_array(s, ' ')`	✖️	✔️	–	–
`arr`	✔️	✔️	–	–
`tarr`	✔️	✔️	–	–
`ARRAY['a','b']`	✔️	✔️	–	–

Other functions

Expression	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
`RAND()`	–	–	✖️	✔️
`UUID()`	–	–	✖️	✔️
`gen_random_uuid()`	✖️	✔️	–	–
`MATCH(t) AGAINST('lorem')`	–	–	✖️	✔️
`to_tsvector('english', t) @@ to_tsquery('lorem')`	✖️	✔️	–	–
`f_double(n)`	✖️	✔️	✖️	✔️
`LAST_INSERT_ID()`	–	–	✖️	✔️
`version()` (PostgreSQL) `VERSION()` (MySQL)	✖️	✔️	✖️	✔️
`current_database()` (PostgreSQL) `DATABASE()` (MySQL)	✖️	✔️	✖️	✔️
`current_user` (PostgreSQL) `CURRENT_USER()` (MySQL)	✖️	✔️	✖️	✔️
`CONNECTION_ID()`	–	–	✖️	✔️
`pg_backend_pid()`	✖️	✔️	–	–
`@@sql_mode`	–	–	✖️	✔️
`@x`	–	–	✖️	✔️
`current_setting('TimeZone')`	✖️	✔️	–	–
`pg_typeof(n)`	✖️	✔️	–	–
`ROW(n, s)`	Partial	Inline	–	–
`INTERVAL '1 hour'`	✖️	Inline	–	–
`'const'`	✔️	✔️	✔️	✔️
`42`	✔️	✔️	✔️	✔️
`COALESCE(NULL, 1)`	✔️	✔️	✔️	✔️

Column types

A column type is cached when queries that read it are cached. A column of a type Readyset cannot replicate keeps its whole table out of deep caching. For replication details see Supported Data Types.

Integers

Type	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
int2	✔️	✔️	–	–
int4	✔️	✔️	–	–
int8	✔️	✔️	–	–
serial	✔️	✔️	–	–
tinyint	–	–	✔️	✔️
smallint	–	–	✔️	✔️
mediumint	–	–	✔️	✔️
int	–	–	✔️	✔️
bigint	–	–	✔️	✔️
tinyint unsigned	–	–	✔️	✔️
int unsigned	–	–	✔️	✔️
bigint unsigned	–	–	✔️	✔️

Numbers

Type	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
numeric	✔️	✔️	–	–
numeric unbounded	✔️	✔️	–	–
real	✔️	✔️	–	–
double	✔️	✔️	✔️	✔️
money	✖️	Inline	–	–
decimal	–	–	✔️	✔️
float	–	–	✔️	✔️

Text

Type	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
text	✔️	✔️	✔️	✔️
varchar	✔️	✔️	✔️	✔️
char	✔️	✔️	✔️	✔️
citext	✔️	✔️	–	–
tinytext	–	–	✔️	✔️
mediumtext	–	–	✔️	✔️
longtext	–	–	✔️	✔️

Binary

Type	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
bytea	✔️	✔️	–	–
binary	–	–	✔️	✔️
varbinary	–	–	✔️	✔️
tinyblob	–	–	✔️	✔️
blob	–	–	✔️	✔️
mediumblob	–	–	✔️	✔️
longblob	–	–	✔️	✔️

Boolean

Type	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
boolean	✔️	✔️	–	–
bool	–	–	✔️	✔️

Dates and times

Type	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
date	✔️	✔️	✔️	✔️
time	✔️	✔️	✔️	✔️
timetz	✖️	Inline	–	–
timestamp	✔️	✔️	✔️	✔️
timestamptz	✔️	✔️	–	–
interval	✖️	Inline	–	–
datetime	–	–	✔️	✔️
datetime6	–	–	✔️	✔️
time6	–	–	✔️	✔️
year	–	–	✖️	✔️

Other types

Type	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
uuid	✔️	✔️	–	–
xml	✖️	Inline	–	–
domain	✖️	✔️	–	–
composite	✖️	Inline	–	–

JSON

Type	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
json	✔️	✔️	✔️	✔️
jsonb	✔️	✔️	–	–

Bit strings

Type	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
bit	✔️	✔️	✔️	✔️
varbit	✔️	✔️	–	–

Enumerations and sets

Type	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
enum	✔️	✔️	✔️	✔️
set	–	–	✖️	✔️

Arrays

Type	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
int array	✔️	✔️	–	–
text array	✔️	✔️	–	–
int array 2d	✔️	✔️	–	–

Network addresses

Type	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
inet	✖️	✔️	–	–
cidr	✖️	✔️	–	–
macaddr	✖️	✔️	–	–

Text search

Type	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
tsvector	✔️	✔️	–	–
tsquery	✖️	Inline	–	–

Ranges

Type	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
int4range	✖️	Inline	–	–
tsrange	✖️	Inline	–	–

Geometric

Type	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
point	✖️	Inline	✔️	✔️
polygon	✖️	Inline	✖️	✔️
geometry	–	–	✖️	✔️
linestring	–	–	✖️	✔️
multipoint	–	–	✖️	✔️
geometrycollection	–	–	✖️	✔️

PostGIS

Type	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
geometry point	✔️	Inline	–	–
geometry polygon	✔️	Inline	–	–
geography point	✖️	Inline	–	–

Table kinds

Type	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
generated	✖️	✔️	–	–
partitioned	✖️	✔️	–	–
generated_stored	–	–	✔️	✔️
generated_virtual	–	–	✔️	✔️

Schema and session conditions

How Readyset behaves with different table layouts and connection settings. ✖️ means Readyset passes the query to the upstream database instead of caching it.

Table and schema shapes

Setting or table shape	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
No primary key · eq `SELECT a, b FROM sc_nopk WHERE a = ?`	✔️	✔️	✔️	✔️
No primary key · count `SELECT COUNT(*) AS n FROM sc_nopk WHERE b = ?`	✔️	✔️	✔️	✔️
No primary key · all `SELECT a, b FROM sc_nopk`	✔️	✔️	✔️	✔️
Composite primary key · full `SELECT v FROM sc_compkey WHERE a = ? AND b = ?`	✔️	✔️	✔️	✔️
Composite primary key · prefix `SELECT b, v FROM sc_compkey WHERE a = ?`	✔️	✔️	✔️	✔️
Composite primary key · suffix `SELECT a, v FROM sc_compkey WHERE b = ?`	✔️	✔️	✔️	✔️
Unique index, no primary key · eq `SELECT id, v FROM sc_uniq WHERE id = ?`	✔️	✔️	✔️	✔️
Nullable indexed column · eq `SELECT id, v FROM sc_nullkey WHERE k = ?`	✔️	✔️	✔️	✔️
Nullable indexed column · is null `SELECT id, v FROM sc_nullkey WHERE k IS NULL`	✔️	✔️	✔️	✔️
Nullable indexed column · eq and null check `SELECT id FROM sc_nullkey WHERE k = ? AND v IS NOT NULL`	✔️	✔️	✔️	✔️
Unindexed column lookup · eq `SELECT id, v FROM sc_noidx WHERE k = ?`	✔️	✔️	✔️	✔️
Text primary key · eq `SELECT code, v FROM sc_vpk WHERE code = ?`	✔️	✔️	✔️	✔️
UUID primary key · eq `SELECT id, v FROM sc_uuidpk WHERE id = ?`	✔️	✔️	✔️	✔️
Bigint primary key · eq `SELECT id, v FROM sc_bigpk WHERE id = ?`	✔️	✔️	✔️	✔️
Bigint primary key · max `SELECT id, v FROM sc_bigpk WHERE id = ?`	✔️	✔️	✔️	✔️
Wide table (60 columns) · star `SELECT * FROM sc_wide WHERE id = ?`	✔️	✔️	✔️	✔️
Wide table (60 columns) · few columns `SELECT id, c1, c30, c60 FROM sc_wide WHERE id = ?`	✔️	✔️	✔️	✔️
Empty table · eq `SELECT id, v FROM sc_empty WHERE id = ?`	✔️	Inline	✔️	✔️
Empty table · count `SELECT COUNT(*) AS n FROM sc_empty WHERE v = ?`	✔️	✔️	✔️	✔️
Reserved words as identifiers PostgreSQL: `SELECT "select", "from" FROM "order" WHERE "group" = ?` MySQL: SELECT `select`, `from` FROM `order` WHERE `group` = ?	✔️	✔️	✔️	✔️
Mixed-case identifiers PostgreSQL: `SELECT "Id", "FirstName" FROM "MixedCase" WHERE "Id" = ?` MySQL: `SELECT Id, FirstName FROM MixedCase WHERE Id = ?`	✔️	✔️	✔️	✔️
Mixed-case identifiers · lower reference `SELECT Id, FirstName FROM mixedcase WHERE Id = ?`	–	–	✖️	✔️
Schema-qualified names · unqualified `SELECT id, v FROM sc_dup WHERE id = ?`	✔️	✔️	✔️	✔️
Schema-qualified names · primary PostgreSQL: `SELECT id, v FROM public.sc_dup WHERE id = ?` MySQL: `SELECT id, v FROM qs_mysql_sc.sc_dup WHERE id = ?`	✔️	✔️	✔️	✔️
Schema-qualified names · other PostgreSQL: `SELECT id, v FROM other.sc_dup WHERE id = ?` MySQL: `SELECT id, v FROM qs_mysql_sc_other.sc_dup WHERE id = ?`	✔️	✔️	✖️	✔️
Schema-qualified names · other only table `SELECT id, v FROM other.sc_only WHERE id = ?`	✔️	✔️	–	–
Schema-qualified names · join across schemas PostgreSQL: `SELECT a.v AS av, b.v AS bv FROM public.sc_dup a JOIN other.sc_dup b ON a.id = b.id WHERE a.id = ?` MySQL: `SELECT a.v AS av, b.v AS bv FROM qs_mysql_sc.sc_dup a JOIN qs_mysql_sc_other.sc_dup b ON a.id = b.id WHERE a.id = ?`	✔️	✔️	✖️	✔️
View · select `SELECT id, name FROM sc_view WHERE id = ?`	✔️	✔️	✔️	✔️
View · join `SELECT v.name, s.amt FROM sc_view v JOIN sess s ON s.id = v.id WHERE v.id = ?`	✔️	✔️	✔️	✔️
Materialized view · select `SELECT id, name FROM sc_matview WHERE id = ?`	✖️	✔️	–	–
Unlogged table · select `SELECT id, v FROM sc_unlogged WHERE id = ?`	✔️	✔️	–	–
Foreign keys · join `SELECT p.name, c.v FROM sc_parent p JOIN sc_child c ON c.parent_id = p.id WHERE p.id = ?`	✔️	✔️	✔️	✔️
Foreign keys · child only `SELECT id, v FROM sc_child WHERE parent_id = ?`	✔️	✔️	✔️	✔️
Storage engine · myisam `SELECT id, v FROM sc_myisam WHERE id = ?`	–	–	✖️	✔️
Storage engine · memory `SELECT id, v FROM sc_memory WHERE id = ?`	–	–	✖️	✔️
Table character set · latin1 table `SELECT id, v FROM sc_latin1 WHERE id = ?`	–	–	✔️	✔️
Table collation · bin table eq `SELECT id FROM sc_bincoll WHERE v = ?`	–	–	✔️	✔️
Partitioned table · hash `SELECT id, v FROM sc_part WHERE id = ?`	–	–	✖️	✔️

Session time zone

Setting or table shape	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
`Default session`	✔️	✔️	✔️	✔️
`SET TIME ZONE 'UTC'`	✔️	✔️	–	–
`SET TIME ZONE 'America/New_York'`	✔️	✔️	–	–
`SET TIME ZONE 'Asia/Kolkata'`	✔️	✔️	–	–
`SET TIME ZONE 'Asia/Tokyo'`	✔️	✔️	–	–
`SET time_zone = '+00:00'`	–	–	✔️	✔️
`SET time_zone = '+05:30'`	–	–	✖️	✖️
`SET time_zone = '-08:00'`	–	–	✖️	✖️
`SET time_zone = 'SYSTEM'`	–	–	✖️	✖️

Connection character set and encoding

Setting or table shape	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
`SET client_encoding = 'UTF8'`	✔️	✔️	–	–
`SET client_encoding = 'LATIN1'`	✖️	✖️	–	–
`SET client_encoding = 'WIN1252'`	✖️	✖️	–	–
`SET client_encoding = 'SQL_ASCII'`	✖️	✖️	–	–
`SET NAMES utf8mb4`	–	–	✔️	✔️
`SET NAMES utf8mb3`	–	–	✔️	✔️
`SET NAMES latin1`	–	–	✔️	✔️
`SET NAMES gbk`	–	–	✖️	✖️
`SET NAMES sjis`	–	–	✖️	✖️
`SET NAMES ascii`	–	–	✔️	✔️
`SET NAMES binary`	–	–	Partial	✖️
`SET NAMES big5`	–	–	✖️	✖️
`SET character_set_results = NULL`	–	–	✖️	✖️
`SET character_set_results = latin1`	–	–	✔️	✔️
`SET character_set_client = latin1`	–	–	✔️	✔️
`SET character_set_connection = latin1`	–	–	✔️	✔️

Output formatting settings (PostgreSQL)

Setting or table shape	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
`SET DateStyle = 'ISO, DMY'`	✖️	✖️	–	–
`SET DateStyle = 'SQL, MDY'`	✖️	✖️	–	–
`SET DateStyle = 'SQL, DMY'`	✖️	✖️	–	–
`SET DateStyle = 'German'`	✖️	✖️	–	–
`SET DateStyle = 'Postgres, MDY'`	✖️	✖️	–	–
`SET IntervalStyle = 'sql_standard'`	✖️	✔️	–	–
`SET IntervalStyle = 'postgres_verbose'`	✖️	✔️	–	–
`SET IntervalStyle = 'iso_8601'`	✖️	✔️	–	–
`SET extra_float_digits = 3`	✖️	✔️	–	–
`SET extra_float_digits = 0`	✖️	✖️	–	–
`SET extra_float_digits = -3`	✖️	✖️	–	–
`SET bytea_output = 'escape'`	✖️	✖️	–	–
`SET bytea_output = 'hex'`	✖️	✔️	–	–

`search_path` (PostgreSQL)

Setting or table shape	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
`SET search_path TO public`	✔️	✔️	–	–
`SET search_path TO other, public`	✔️	✔️	–	–
`SET search_path TO other`	✔️	✔️	–	–

Other session settings

Setting or table shape	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
`SET transform_null_equals = on`	✖️	✖️	–	–
`SET standard_conforming_strings = off`	✖️	✖️	–	–
`SET row_security = off`	✔️	✔️	–	–
`SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE`	✔️	✔️	–	–
`SET default_transaction_read_only = on`	✔️	✔️	–	–
`SET statement_timeout = '5s'`	✔️	✔️	–	–
`SET application_name = 'qs_probe'`	✔️	✔️	–	–
`SET lock_timeout = '2s'`	✔️	✔️	–	–
`SET work_mem = '8MB'`	✔️	✔️	–	–
`SET synchronous_commit = off`	✔️	✔️	–	–
`SET plan_cache_mode = force_custom_plan`	✖️	✖️	–	–
`SET default_text_search_config = 'pg_catalog.simple'`	✔️	✔️	–	–
`SET xmloption = document`	✔️	✔️	–	–
`SET lc_messages = 'C'`	✖️	✖️	–	–
`SET enable_seqscan = off`	✔️	✔️	–	–
`SET idle_in_transaction_session_timeout = '60s'`	✔️	✔️	–	–
`SET group_concat_max_len = 5`	–	–	✖️	✔️
`SET div_precision_increment = 2`	–	–	✔️	✔️
`SET sql_select_limit = 2`	–	–	✔️	✔️
`SET sql_auto_is_null = 1`	–	–	✔️	✔️
`SET lc_time_names = 'de_DE'`	–	–	✔️	✔️
`SET lc_time_names = 'fr_FR'`	–	–	✔️	✔️
`SET max_execution_time = 1000`	–	–	✔️	✔️
`SET @x = 1`	–	–	✖️	✖️
`SET foreign_key_checks = 0`	–	–	✔️	✔️
`SET unique_checks = 0`	–	–	✔️	✔️
`SET sql_safe_updates = 1`	–	–	✔️	✔️
`SET default_week_format = 3`	–	–	✔️	✔️
`SET sql_big_selects = 0`	–	–	✔️	✔️
`SET net_read_timeout = 60`	–	–	✔️	✔️
`SET optimizer_switch = 'index_merge=off'`	–	–	✔️	✔️
`SET sort_buffer_size = 524288`	–	–	✔️	✔️
`SET session_track_schema = ON`	–	–	✔️	✔️
`SET explicit_defaults_for_timestamp = ON`	–	–	✔️	✔️

Transactions

Setting or table shape	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
`BEGIN`	✖️	✖️	–	–
`BEGIN READ ONLY`	✖️	✖️	–	–
`BEGIN ISOLATION LEVEL REPEATABLE READ`	✖️	✖️	–	–
`BEGIN ISOLATION LEVEL SERIALIZABLE`	✖️	✖️	–	–
`SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED`	–	–	✔️	✔️
`SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE`	–	–	✔️	✔️
`SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED`	–	–	✔️	✔️
`SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ`	–	–	✔️	✔️
`SET SESSION TRANSACTION READ ONLY`	–	–	✔️	✔️
`SET autocommit = 0`	–	–	✖️	✖️
`SET autocommit = 1`	–	–	✔️	✔️
`START TRANSACTION`	–	–	✖️	✖️
`START TRANSACTION READ ONLY`	–	–	✖️	✖️
`START TRANSACTION WITH CONSISTENT SNAPSHOT`	–	–	✔️	✔️

Temporary tables

Setting or table shape	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
`CREATE TEMPORARY TABLE tmp_s (id int PRIMARY KEY, v varchar(10)); INSERT INTO tmp_s VALUES (1, 'mine')`	✖️	✖️	✖️	✔️

Connection collation

Setting or table shape	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
`SET NAMES utf8mb4 COLLATE utf8mb4_bin`	–	–	✔️	✔️
`SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci`	–	–	✔️	✔️
`SET NAMES utf8mb4 COLLATE utf8mb4_0900_as_cs`	–	–	✔️	✔️
`SET NAMES utf8mb4 COLLATE utf8mb4_general_ci`	–	–	✔️	✔️

`sql_mode` (MySQL)

Setting or table shape	PostgreSQL deep	PostgreSQL shallow	MySQL deep	MySQL shallow
`SET sql_mode = ''`	–	–	✖️	✖️
`SET sql_mode = 'ANSI'`	–	–	✖️	✖️
`SET sql_mode = 'PIPES_AS_CONCAT'`	–	–	✖️	✖️
`SET sql_mode = 'ANSI_QUOTES'`	–	–	✖️	✖️
`SET sql_mode = 'NO_BACKSLASH_ESCAPES'`	–	–	✖️	✖️
`SET sql_mode = 'TRADITIONAL'`	–	–	✖️	✖️
`SET sql_mode = 'STRICT_ALL_TABLES'`	–	–	✖️	✖️
`SET sql_mode = 'NO_ZERO_DATE'`	–	–	✖️	✖️
`SET sql_mode = 'REAL_AS_FLOAT'`	–	–	✖️	✖️
`SET sql_mode = 'HIGH_NOT_PRECEDENCE'`	–	–	✖️	✖️
`SET sql_mode = 'NO_ENGINE_SUBSTITUTION'`	–	–	✖️	✖️
`SET sql_mode = 'STRICT_TRANS_TABLES'`	–	–	✖️	✖️

Query Support

On this page