SQLite Core & Scalar Functions

Passing the minimum 64-bit integer -9223372036854775808 to abs() throws an integer overflow error because there is no positive 64-bit equivalent. The random() function deliberately avoids generating this value so its output can always be safely passed to abs().

SELECT id, amount, abs(amount) AS abs_amount FROM ledger WHERE amount < 0;

In PostgreSQL, abs() raises an error for numeric overflow too, but the overflow boundary differs by type. In SQLite, the only dangerous value is the single 64-bit minimum integer -9223372036854775808; passing a non-numeric string returns 0.0 instead of an error, unlike PostgreSQL which raises a cast error.

changes() only counts rows modified by the outermost statement, not by any triggers that statement fires. If you need to include trigger-induced changes, use total_changes() instead.

UPDATE products SET price = price * 0.9 WHERE category = 'clearance';
SELECT changes() AS rows_discounted;

PostgreSQL uses GET DIAGNOSTICS ROW_COUNT or the libpq PQcmdTuples() API to retrieve affected row counts — there is no equivalent scalar SQL function. SQLite's changes() is a first-class scalar function callable anywhere in a query.

char() is the inverse of unicode(). It accepts any number of arguments and is useful for injecting special characters such as tabs (char(9)), newlines (char(10)), or non-ASCII glyphs into string expressions without needing escape sequences.

SELECT group_concat(char(unicode(c) + 1), '') FROM (SELECT substr(word,n,1) AS c FROM words, generate_series(1,length(word)) WHERE id=1) WHERE c BETWEEN 'a' AND 'z';

PostgreSQL uses chr(N) (single argument only) for the same purpose. SQLite's char() is variadic and can produce a multi-character string in one call, whereas PostgreSQL chr() always returns a single character.

SQLite evaluates coalesce() arguments left to right and stops as soon as it finds a non-NULL value, so expensive subqueries placed later in the argument list are skipped when an earlier argument is non-NULL.

SELECT user_id, coalesce(nickname, first_name, 'Anonymous') AS display_name FROM users;

The behaviour and signature of coalesce() are the same in SQLite and PostgreSQL — both require at least 2 arguments and return the first non-NULL value. However, PostgreSQL also offers the COALESCE expression as part of standard SQL syntax with identical semantics.

In the GLOB operator you write 'Y GLOB X', but in the glob() function the order is glob(X, Y) — pattern first, string second. The function form exists so you can override the GLOB operator by replacing glob() with sqlite3_create_function().

SELECT name FROM attachments WHERE glob('*.{jpg,jpeg,png,gif}', lower(name));

PostgreSQL has no built-in glob() function; it uses LIKE or SIMILAR TO for pattern matching, or the ~ operator with POSIX regular expressions. SQLite's GLOB is always case-sensitive, whereas LIKE is case-insensitive for ASCII letters by default.

Value arguments that correspond to false Booleans are never evaluated, so expensive subqueries or functions in later branches incur no cost when an earlier condition is true. This makes iif() more efficient than equivalent CASE expressions in theory, though they compile to identical bytecode for the three-argument form.

SELECT order_id, total, iif(total >= 100, 'Free', '$5.99') AS shipping FROM orders;

PostgreSQL has no iif() or if() function; the equivalent is a CASE expression: CASE WHEN B THEN V1 ELSE V2 END. SQLite's iif() is syntactic sugar for exactly that CASE expression and generates identical bytecode. The two-argument form and the if() alias were added in SQLite 3.48.0 (2025-01-14); support for more than three arguments was added in 3.49.0 (2025-02-06).

ifnull(X,Y) is exactly equivalent to coalesce(X,Y). Use ifnull() when you have exactly two operands for clarity; switch to coalesce() when you need a chain of three or more fallback values.

SELECT product_id, ifnull(discount_pct, 0) AS discount FROM products;

PostgreSQL uses COALESCE(X,Y) for the same purpose and has no ifnull() function. SQLite supports both ifnull() and coalesce() with identical semantics. MySQL also has IFNULL(), so code using ifnull() is not portable to PostgreSQL without substitution.

last_insert_rowid() returns the rowid of the last successful INSERT on the connection, even if that INSERT was later rolled back. If you need the rowid of a committed row, read it before rolling back.

INSERT INTO orders(customer_id, total) VALUES(42, 99.99);
INSERT INTO order_items(order_id, product_id) VALUES(last_insert_rowid(), 7);

PostgreSQL uses RETURNING id or the sequence function lastval() / currval() to retrieve auto-generated primary keys. SQLite has no RETURNING clause before version 3.35.0 (2021-03-12); last_insert_rowid() is the traditional way to retrieve the new rowid and is connection-scoped, not statement-scoped.

likelihood() is a no-op at runtime — the code generator removes it entirely from the bytecode. It exists solely to carry a probability annotation to the query planner. Use likely(X) and unlikely(X) as convenient shorthands for likelihood(X,0.9375) and likelihood(X,0.0625).

SELECT * FROM events WHERE likelihood(event_type = 'pageview', 0.92);

PostgreSQL has no likelihood() function. The closest equivalent is the statistics stored by ANALYZE and adjustable via ALTER TABLE ... ALTER COLUMN ... SET STATISTICS, but there is no inline SQL-level probability hint function. SQLite's likelihood() has zero runtime overhead because it is compiled away.

Misapplying likely() on a predicate that is actually rarely true can degrade query performance. Only use it when ANALYZE cannot capture selectivity, such as on functions or expressions over non-indexed columns.

SELECT * FROM users WHERE likely(is_active = 1);

PostgreSQL has no likely() function. SQLite's likely() is a no-op at runtime — it is compiled away and incurs zero CPU overhead during query execution.

Extension loading is disabled by default for security reasons. It must be enabled via sqlite3_enable_load_extension() in the C API, the .load dot-command in the CLI, or a similar mechanism in your language binding. An extension that tries to modify or delete existing SQL functions must be loaded via the C API instead.

SELECT load_extension('./spellfix.so');

PostgreSQL uses CREATE EXTENSION to install server-side extensions from a registry; there is no dynamic runtime loading function. SQLite's load_extension() loads shared libraries at runtime from the filesystem, which is a fundamentally different — and more flexible but less sandboxed — mechanism.

max() with two or more arguments is the scalar greatest() function. max() with a single argument is the aggregate MAX(). PostgreSQL names these separately: greatest() for the scalar form and max() only for aggregation. In SQLite, both share the same name but differ by argument count.

SELECT id, max(price, min_price) AS effective_price FROM products;

PostgreSQL separates greatest(X,Y,...) (scalar) from max(X) (aggregate). SQLite overloads the name max(): one argument triggers aggregate mode, two or more arguments trigger scalar mode. Unlike PostgreSQL's greatest(), SQLite's scalar max() returns NULL if any argument is NULL, whereas PostgreSQL's greatest() skips NULLs.

SQLite's scalar min() returns NULL if any argument is NULL. PostgreSQL's least() ignores NULLs and returns the minimum of the non-NULL arguments. Wrap arguments in ifnull() if you need PostgreSQL-style NULL-skipping behaviour.

SELECT id, min(stock, reorder_qty) AS units_to_order FROM inventory;

PostgreSQL names the scalar form least(X,Y,...) and the aggregate form min(X). SQLite uses min() for both, disambiguated by argument count. PostgreSQL's least() skips NULLs; SQLite's scalar min() does not — any NULL argument returns NULL.

COALESCE returns a value when it finds a non-NULL; nullif() does the reverse — it returns NULL when the arguments are equal. The classic safe-division pattern is 10 / nullif(denominator, 0), which yields NULL instead of a divide-by-zero error.

SELECT sum(revenue) / nullif(count(*), 0) AS avg_revenue FROM campaigns WHERE cohort = 'B';

nullif() behaves identically in SQLite and PostgreSQL. Both return NULL when the two arguments are equal and X otherwise. The function uses the same collation resolution rules as other binary comparison functions in SQLite.

quote() is used internally by the .dump CLI command to produce INSERT statements that correctly re-insert all data types, including BLOBs and strings with special characters. Use unistr_quote() instead if you also need control characters (U+0001–U+001F) to be escaped.

SELECT 'INSERT INTO backup VALUES(' || quote(name) || ',' || quote(value) || ');' FROM config;

PostgreSQL uses the quote_literal() and quote_ident() functions for similar escaping tasks. SQLite's quote() handles all scalar storage classes in one function and encodes BLOBs as X'...' hex literals, whereas PostgreSQL's quote_literal() only handles text and uses $ quoting for BYTEA differently.

Unlike many languages where random() returns a float in [0.0, 1.0), SQLite's random() returns a 64-bit signed integer. Divide by 9223372036854775807.0 and take the absolute value to obtain a uniform float in [0.0, 1.0]. The PRNG has used Chacha20 since SQLite 3.40.0.

SELECT * FROM survey_responses ORDER BY random() LIMIT 10;

PostgreSQL's random() returns a double precision float in [0.0, 1.0). SQLite's random() returns a 64-bit signed integer in [-9223372036854775807, +9223372036854775807]. The two functions have completely different return ranges and types, so code using random() is not directly portable between the two databases.

The SQLite documentation itself suggests hex(randomblob(16)) or lower(hex(randomblob(16))) as a simple way to generate a globally unique 128-bit identifier. For a proper UUID v4 format, use the uuid extension available in the CLI and many SQLite builds.

INSERT INTO api_keys(user_id, token) VALUES(42, lower(hex(randomblob(16))));

PostgreSQL provides gen_random_bytes(N) from the pgcrypto extension and gen_random_uuid() for UUID generation. SQLite has no built-in UUID function in the core, but randomblob(16) combined with hex() provides a functionally equivalent random identifier. PostgreSQL returns BYTEA; SQLite returns BLOB.

Unlike PostgreSQL where round() preserves the numeric type (returning INTEGER for round(3) and NUMERIC for round(3.5, 0)), SQLite's round() always returns a REAL value. The result of round(3) is 3.0, not 3. Cast to INTEGER explicitly if you need an integer result.

SELECT product_id, round(avg(rating), 1) AS avg_rating FROM reviews GROUP BY product_id;

PostgreSQL's round(X) returns a value of the same numeric type as X, so round(3.5) returns 4 as a NUMERIC, not 4.0 as a REAL. SQLite's round() always returns REAL. Additionally, PostgreSQL supports round(X, Y) with negative Y to round to the left of the decimal point; SQLite treats negative Y as 0 (rounds to whole number only).

sign('3') returns 1 because '3' can be losslessly converted to the integer 3. However sign('3.5abc') returns NULL because that string cannot be losslessly converted. This differs from abs(), which returns 0.0 for any non-convertible string rather than NULL.

SELECT id, amount, sign(amount) AS direction FROM transactions;

PostgreSQL has a sign() function with the same semantics for numeric types. The key SQLite difference is that sign() returns NULL for strings that cannot be losslessly converted to a number, whereas PostgreSQL raises a type error. SQLite's sign() was added in version 3.35.0 together with the math functions.

soundex() is not compiled into SQLite by default. It is only available when the library is built with the -DSQLITE_SOUNDEX compile-time option. Check sqlite_compileoption_used('SOUNDEX') to determine if it is available in the current build.

SELECT * FROM contacts WHERE soundex(last_name) = soundex('Smith');

PostgreSQL offers soundex() via the fuzzystrmatch extension (CREATE EXTENSION fuzzystrmatch). In SQLite, soundex() is a compile-time option (SQLITE_SOUNDEX) built directly into the library rather than loaded as a separate extension. Both implement the classic American Soundex algorithm.

PRAGMA compile_options is usually easier to use than iterating sqlite_compileoption_get(N) manually. The function form is useful when you need to access options programmatically in a subquery or CTE.

SELECT sqlite_compileoption_get(/* see description */);

PostgreSQL has no equivalent function; compile-time options are embedded in the server binary and surfaced via pg_config or the pg_config() function. SQLite's compile-time options directly affect which features are available at runtime and are queryable through both a function and a pragma.

Wrap calls to optional functions such as soundex() or sqlite_offset() inside an iif(sqlite_compileoption_used('SOUNDEX'), ..., NULL) to write code that degrades gracefully on builds that omit the feature.

SELECT sqlite_compileoption_used(/* see description */);

PostgreSQL has no equivalent single-function capability check. PostgreSQL features are controlled at extension installation time (CREATE EXTENSION) rather than compile time. In SQLite, many features are baked in at compile time and cannot be added at runtime, making this function important for writing portable SQLite code.

sqlite_offset() is primarily a debugging and low-level tool. Its main practical use is to locate a row's storage position before opening it with the sqlite3_blob_open() C API for incremental reading or writing of large BLOB values without loading them fully into memory.

SELECT sqlite_offset(/* see description */);

PostgreSQL has no equivalent function. This is a SQLite-specific introspection tool for the underlying B-tree file format. It only works when SQLite is compiled with -DSQLITE_ENABLE_OFFSET_SQL_FUNC, which is not the default.

While sqlite_version() gives the release version string like '3.52.0', sqlite_source_id() gives the SHA3-256 hash of the exact source tree used, which is useful for filing bug reports or verifying that a specific bug fix is included in the build.

SELECT sqlite_source_id(/* see description */);

PostgreSQL uses version() to return a combined version and build info string, and has no separate source-ID function. SQLite keeps these separate: sqlite_version() for the human-readable release number and sqlite_source_id() for the exact source revision hash.

Comparing version strings lexicographically only works reliably when all components have the same number of digits. For robust feature detection, use sqlite_compileoption_used() to check specific capabilities rather than comparing version numbers.

SELECT sqlite_version() AS version, sqlite_source_id() AS build;

PostgreSQL uses version() which returns a longer string including the OS and compiler. SQLite's sqlite_version() returns only the version number (e.g. '3.52.0'). There is no PostgreSQL equivalent to SQLite's sqlite_source_id() for the exact source hash.

Unlike changes() which excludes lower-level trigger changes, total_changes() counts every row modification since the connection opened, including those caused by triggers. Reset by opening a new connection.

SELECT total_changes() AS baseline;
DELETE FROM logs WHERE created_at < date('now','-30 days');
INSERT INTO archive SELECT * FROM logs WHERE created_at < date('now','-60 days');
SELECT total_changes() - 0 AS ops_done;

PostgreSQL has no equivalent cumulative change-count function accessible as a scalar SQL expression. The PostgreSQL C API provides PQcmdTuples() for the most recent command only. SQLite's total_changes() is useful for progress monitoring and auditing within a long-lived connection.

typeof() returns one of exactly five values: 'null', 'integer', 'real', 'text', or 'blob'. These are storage classes, not declared column types. Because SQLite uses dynamic typing, a column declared as TEXT can store integers, and typeof() will return 'integer' for those rows.

SELECT col, typeof(col), count(*) FROM config_values GROUP BY typeof(col);

PostgreSQL uses pg_typeof(X) to return a data type name such as 'integer' or 'text'. SQLite's typeof() returns one of only five storage class names regardless of the column's declared type. This reflects SQLite's fundamental dynamic typing (type affinity) model, which is very different from PostgreSQL's strict static type system.

The two-argument form unhex(uuid_string, '-') is ideal for converting UUID strings to compact 16-byte BLOBs for storage. The Y argument lists separator characters to silently skip, so hyphens between UUID groups are ignored without any prior string manipulation.

SELECT unhex(/* see description */);

PostgreSQL has no unhex() function; the equivalent is decode(string, 'hex') from the encode/decode functions, which returns BYTEA. SQLite's unhex() was added in version 3.41.0 (2023-02-21) and is therefore absent from older SQLite installations. The Y separator argument has no PostgreSQL equivalent.

unicode(X) and char(N) are inverses: unicode(char(N)) = N for any valid code point, and char(unicode(X)) = the first character of X. Use them together to perform arithmetic on characters.

SELECT char, unicode(char) AS codepoint FROM (SELECT substr(text,n,1) AS char FROM data, generate_series(1,length(text)) WHERE id=1);

PostgreSQL uses ascii(X) to return the code point of the first character (limited to Latin-1 range in older versions) and unicode is available as ascii() for multi-byte characters in modern versions. SQLite's unicode() works correctly with all Unicode code points including supplementary characters above U+FFFF.

The SQLite CLI's .dump command uses unistr() to safely encode strings containing control characters (U+0001–U+001F) into portable SQL that can be re-executed without terminal rendering issues. unistr_quote() wraps this into a quoting function suitable for inclusion in SQL literals.

SELECT unistr(/* see description */);

unistr() was added in SQLite 3.50.0 (2025-05-29) specifically to match the unistr() function in PostgreSQL, SQL Server, and Oracle. The PostgreSQL version accepts the same \uXXXX and \UXXXXXXXX escape sequences. Code using unistr() should be largely portable between SQLite 3.50.0+ and PostgreSQL.

unistr_quote() is preferred over quote() when generating SQL scripts that may be displayed on terminals that interpret ANSI escape codes, because embedded control characters (like ESC, U+001B) would otherwise be rendered as terminal commands. The .dump CLI command uses unistr_quote() for this reason.

SELECT unistr_quote(/* see description */);

PostgreSQL has no unistr_quote() function. The closest PostgreSQL equivalent is quote_literal() combined with regexp_replace() to escape control characters. SQLite's unistr_quote() was added in 3.50.0 alongside unistr() to support safe serialization of strings containing control characters in .dump output.

unlikely() is best applied to predicates that filter out rare exception rows — for example, error conditions, soft-deleted records, or administrative flags. It encourages the planner to scan first and filter later rather than seeking by index on a very selective predicate.

SELECT * FROM events WHERE unlikely(event_type = 'error');

PostgreSQL has no unlikely() function. SQLite's unlikely() is a no-op at runtime with zero CPU overhead; it exists solely to pass a selectivity hint to the query planner during compilation.

SQLite stores zeroblob() data very compactly in the file format — it does not write N actual zero bytes. The zero bytes are materialized on read. The intended workflow is: INSERT with zeroblob(N) to reserve space, capture the rowid, then write content incrementally using sqlite3_blob_open() and sqlite3_blob_write() in the C API.

INSERT INTO uploads(user_id, data) VALUES(1, zeroblob(1048576)) RETURNING rowid;

PostgreSQL has no zeroblob() function. The PostgreSQL equivalent for pre-allocating large object space is the lo_create() / lo_open() large object API, which is fundamentally different. In PostgreSQL, BYTEA columns store actual bytes in-place. SQLite's zeroblob() is efficient precisely because of SQLite's on-disk format treating zero-byte BLOBs as a special case.

unixepoch() was added specifically as a faster alternative to CAST(strftime('%s', ...) AS INTEGER). For high-frequency timestamp operations or large-scale datetime comparisons, unixepoch() provides a measurable performance improvement and produces an integer directly without a string parsing step.

SELECT unixepoch('now') - unixepoch(created_at) AS age_seconds FROM events;

SQLite-specific function added as a faster alternative to CAST(strftime('%s',...) AS INTEGER). Not available in PostgreSQL; use EXTRACT(EPOCH FROM ...) instead.

The string produced by timediff() can be passed directly as a modifier to datetime(), date(), or similar functions to add the duration to another datetime. This makes it possible to do relative date arithmetic using computed intervals.

SELECT datetime('2024-01-01', timediff('2024-03-15', '2024-01-01'));

SQLite-specific function added in 3.43.0. Returns ISO 8601 duration format. For elapsed seconds, unixepoch() subtraction is simpler. Not available in PostgreSQL; use AGE() or timestamp subtraction instead.

For data that will be stored in external systems with byte-level limits (HTTP headers, file names, database fields with byte-based size limits), octet_length() gives you the true storage cost in bytes. For pure character counting (e.g., tweet length), use length() instead.

SELECT name FROM products WHERE octet_length(description) > 255;

Added in SQLite 3.43.0. For BLOB, octet_length() and length() return the same value. For TEXT, octet_length() measures UTF-8 bytes while length() measures Unicode code points.

concat() propagates NULL like the || operator. When you are building strings from nullable columns (like first_name, middle_name, last_name where middle_name may be NULL), use concat_ws() instead — it skips NULL arguments and only includes non-NULL values separated by the delimiter.

SELECT concat_ws(' ', first_name, middle_name, last_name) FROM users;

Added in SQLite 3.44.0. Propagates NULL (unlike MySQL's CONCAT which skips NULLs). The || operator also propagates NULL. Use concat_ws() for NULL-skipping concatenation.

concat_ws() is the cleanest way to build delimited strings from a mix of nullable columns. It avoids the boilerplate of wrapping every column in COALESCE() and produces clean output with no leading, trailing, or doubled separators when values are NULL.

SELECT concat_ws(' ', title, first_name, last_name) FROM people;

Added in SQLite 3.44.0. The separator (first argument) must not be NULL — a NULL separator returns NULL. String arguments that are NULL are skipped. Similar to MySQL/MariaDB concat_ws().