Core SQLite Functions and Features

​

Scalar functions

SELECT iif(stock > 0, 'in stock', 'sold out') FROM products;
SELECT concat_ws('-', 'a', NULL, 'b');           -- a-b
SELECT format('%05.2f|%s|%d', 3.14159, 'x', 42); -- 03.14|x|42

​

Aggregate functions

SELECT count(*) FILTER (WHERE status = 'paid') AS paid_orders FROM orders;

​

Window functions

SELECT order_id, total,
       sum(total) OVER (ORDER BY created_at) AS running_total
FROM orders;

​

Date and time functions

SELECT date('2024-02-29', '+1 year');                    -- 2025-03-01
SELECT unixepoch('2024-01-01');                          -- 1704067200
SELECT datetime('now', 'start of month', 'weekday 1');   -- first Monday of the month
SELECT datetime('now', 'subsec');                        -- with milliseconds

​

Statement features

• STRICT tables. A value of the wrong type is rejected with a constraint error.
• RETURNING clauses on INSERT, UPDATE, and DELETE
• UPSERT (INSERT ... ON CONFLICT DO UPDATE)
• Generated columns, virtual and stored
• Common table expressions, including RECURSIVE and MATERIALIZED
• RIGHT JOIN, FULL OUTER JOIN, and IS [NOT] DISTINCT FROM
• Savepoints and transactions on the same connection
• IF NOT EXISTS on CREATE VIEW, CREATE TRIGGER, and CREATE INDEX
• EXPLAIN and EXPLAIN QUERY PLAN
• The BINARY, NOCASE, and RTRIM collations

​

Not available

• Features added in SQLite 3.46 or later. This includes json_pretty(), the 'ceiling' and 'floor' date modifiers, and the %G, %g, %U, and %V strftime() formats. The modifiers and formats return NULL.
• sqlite_offset()