Redis ADBC driver reference¶
Detailed setup, SQL, architecture, configuration, performance and development reference. Start with the README for an overview, the Apache Arrow feature matrix and a quick start. Historical test and benchmark results retain their original server distributions, dates and settings.
Choose a table approach¶
Managed tables are the default for SQL/Arrow workflows. Application-owned data can better fit direct Redis access, existing keys and TTL-driven lifecycles. See the use-case comparison for benefits and costs of both, including combining application data with managed imports. Imports default to private copies; explicit unmanaged mode provides live SQL reads and source writes. The snapshot contract below applies to managed data.
Managed-table snapshot format¶
Managed tables now have always-on statement snapshots. The linked
page defines the current guarantee, limits and supported deployments and takes
precedence over the historical layout and feature descriptions below. New
catalogs normally require an empty isolated database; explicit imports may
initialize alongside application data after rejecting legacy driver metadata.
Storage protocol version 16 rejects legacy catalogs and prior draft formats 1–15 without
modification; no upgrade path is provided. ALTER and temporary objects are
supported. By default, AdoptHashes creates a managed copy; repeating Apply explicitly refreshes
it. Managed imports retain the original application key as a queryable __key
system column, excluded from SELECT * and public schemas; SQL-added rows have
NULL provenance. Refresh replaces the private copy's rows and inferred schema.
Foreign indexes default to managed copies after enabling foreign_indexes;
set connection option adbc.redis.foreign_indexes_refresh=true (or call Go API
RefreshForeignIndexes) for subsequent refreshes. Capturing external data is not
atomic with application writers; published copies have normal snapshot guarantees.
Source HASHes/indexes remain unchanged. Live partition sets retain one producer generation;
retained production pins one managed generation and publishes independent Arrow
results. Retained reader-only connections do not initialize a managed catalog.
Snapshot-aware HASH inspection/copy and reference-aware sweep are available.
Each write copies a metadata manifest, shares unchanged data and appends changed row versions/tombstones. Automatic background compaction starts at four segments; a hard eight-segment bound applies retryable backpressure instead of foreground copying. Jobs persist bounded-batch checkpoints and preserve newer deltas when published. Segmented reads merge visibility in the driver and disable unsafe Search pushdowns. Fixed manifest, reader, staging and time limits apply. Primary proxy and Cluster API connections are supported with RESP3 and TLS. Active-Active remains unsupported and verified noeviction is required. Rename/rekey allocates a new private identity without moving pinned rows. Metadata copies use immutable key directories instead of SCAN cursors. See the recovery evidence. It is not a multi-statement transaction implementation.
Contents¶
- Server requirements
- Existing HASH collections:
redis-adbc - Architecture: hybrid index-row layout
- Supported SQL
- PostgreSQL catalogs and session discovery
- Options
- Performance
- Building and testing
Server requirements¶
Recommended configuration: Redis Search (Query Engine) enabled on the database and query performance factor (QPF) set to 4× where supported. Search is required to connect; 4× QPF is a performance recommendation, not a connection requirement. The driver creates its own indexes, but you enable Search and configure QPF on the Redis server.
For Redis Cloud or Redis Software deployments that offer QPF, enable the Search capability and select 4× in the database's Query performance factor setting. Provision enough CPU capacity for that setting; see Redis's QPF configuration and sizing guidance. Deployments without QPF can still use the driver if Search is available. The QPF benchmarks show the measured benefits and workload limits.
The ADBC driver requires commands and functions unavailable in Valkey and Redis 7.2 derivatives, including AWS ElastiCache and Google Cloud Memorystore. Valkey, AWS ElastiCache, and Google Cloud Memorystore are outside both the support and compatibility-testing scope of this project.
Prerequisites¶
- Redis with the Query Engine (RediSearch). The default local setup uses
Redis Software with Search enabled. Redis Cloud and Redis 8.x Open Source
with Search are also supported; older versions need Redis Stack. The driver
runs
FT._LISTwhen it connects and refuses to connect if search isn't available. - A standalone server, Redis Cloud / Redis Software, or a Redis Cluster.
The driver picks its client automatically (
adbc.redis.cluster=auto): - If the server reports
cluster_enabled:1(OSS Cluster API), it uses a cluster client. Rows spread across shards, and all search commands for a table go through one node, which coordinates the query across shards. - Otherwise it uses a single endpoint: standalone Redis, or Redis Cloud / Redis Software through their proxy.
Driver metadata shares one hash slot (adbc:{meta}:…), so the driver's
multi-key transactions never cross slots. A cluster only has database 0;
on standalone servers only database 0 has been tested.
- ACL permissions, if the database has ACL users: the commands and keys
under ACL users below.
Driver-managed setup¶
For the proposed common storage format and automatic topology transition, see the Active-Active migration design. This is a design proposal with a deliberate format break, not available migration support.
CREATE TABLEand bulk ingest create the table's index (FT.CREATE idx:<schema>:<table> …) and its metadata keys.DROP TABLEremoves the index and all of the table's rows, andTRUNCATEmoves the table to a new, empty index and key prefix and then deletes the old rows the same way (a statement still writing to the table then deletes what it writes; see "Key prefixes" in the architecture section).- Every filterable column is indexed by default. Narrow this with
NOINDEXinCREATE TABLEoradbc.redis.ingest.index_columnson ingest. Queries still work on columns that aren't indexed: the driver scans the table's rows and filters them itself, which is slower on large tables but correct. - Every query passes
TIMEOUT 0, so the server's default search timeout (which can return partial results) doesn't apply. You don't need to change anysearch-*settings or enable keyspace notifications. The client's own timeouts still apply; see Timeouts below.
Timeouts¶
The driver's client waits up to 5 minutes for each reply from Redis, and
to send each command, on standalone servers and clusters alike. Some
commands take longer as the data grows: an FT.AGGREGATE page that sorts or
groups a big table, FT.DROPINDEX … DD of a big table, a pipeline of 1,000
rows. And a busy or briefly blocked server (a fork for a snapshot, a
failover, a slow cluster shard) delays every reply. When a reply takes
longer than the timeout, the statement fails with an error that names the
setting:
[redis] FT.AGGREGATE failed: read tcp 127.0.0.1:57705->127.0.0.1:7103: i/o timeout (Redis did not reply within the read timeout, 5m0s: raise adbc.redis.read_timeout, or set it to 0 for no timeout)
| Setting | Default | What it limits |
|---|---|---|
adbc.redis.read_timeout (database or connection option), or the URI's read_timeout |
5m |
waiting for each reply |
adbc.redis.write_timeout (database or connection option), or the URI's write_timeout |
the read timeout | sending each command |
the URI's dial_timeout |
5s |
connecting |
- Values are durations such as
30s,10mor1h, or a number of seconds.0means no timeout. The URI's parameters are parsed by go-redis (redis://host:6379/0?read_timeout=30m), where0and-1also mean no timeout. - Precedence: the options override the URI's parameters. A connection's option overrides the database's, for the statements that the connection runs after it is set (the connection gets a new client). Connecting uses the database's.
- Retries: go-redis sends most commands again after a reply timed out
or the connection broke (up to 3 times; see
max_retriesunder URI parameters), so a statement can wait a few times the read timeout before it fails. The driver turns that off for the commands where a second run isn't safe: search commands (a repeatedFT.CURSOR READwould return the next page, and the rows of the lost one would be missing), andSET … NXandSADDwhere the driver acts on the reply. Transactions (MULTI…EXEC) aren't sent again once they were sent. - Earlier versions (v0.0.7 and before) used go-redis's defaults: 5 seconds, in effect 10 seconds on a standalone server.
URI parameters¶
go-redis parses the URI's query parameters
(redis://host:6379/0?pool_size=20&client_name=etl). The single-endpoint
client and the cluster client both get them. An unknown parameter is an
error.
- Both clients:
client_name,max_retries,min_retry_backoff,max_retry_backoff,dial_timeout,read_timeout,write_timeout,pool_fifo,pool_size,pool_timeout,min_idle_conns,max_idle_conns,max_active_conns,max_concurrent_dials,conn_max_idle_time,conn_max_lifetime,conn_max_lifetime_jitter,skip_verifywithrediss://, anddb(as the path; a cluster only has database 0). - On a cluster, every node's client gets them.
pool_sizeandmax_active_connsare per node.max_retriesis per node too: a node's client sends a command again that many times (by default none), and the cluster client tries again up tomax_redirectstimes (3). - Cluster only:
addr(another seed node,host:port; repeat it for more),max_redirects,dialer_retries,dialer_retry_timeoutandfailing_timeout_seconds. On a single endpoint they're an error. The seeds help when the URI's node is down:adbc.redis.cluster=autoasks that node what it is, so also setadbc.redis.cluster=true. - Not supported:
read_only,route_by_latencyorroute_randomlyset to true is an error. They send reads to replicas, which may not have the driver's latest writes yet. - The driver's settings win: the
usernameandpasswordoptions, noCLIENT SETINFO, and the timeout options (see Timeouts). RESP3 is required;protocol=3may be set explicitly. Other protocol values, includingprotocol=2, are rejected. Every cluster node must negotiate RESP3; failed negotiation is an error. client_namemakes go-redis name every connection withCLIENT SETNAME. An ACL user then needs+client|setname(see ACL users).- Earlier versions (v0.0.12 and before) gave the cluster client only the URI's credentials, TLS and timeouts, and refused the cluster-only parameters as unexpected.
Cancelling a statement¶
Cancelling a statement while it executes (AdbcStatementCancel;
cursor.adbc_cancel() in Python, from another thread), or cancelling the
context passed to ExecuteQuery or ExecuteUpdate in Go, makes it fail
with CANCELLED:
[redis] the query was cancelled
- The work the driver does in memory (filtering, joins, aggregates,
window functions, sorting, set operations, recursive queries, subqueries,
generate_series) checks for the cancel at every row, so it stops within milliseconds for ordinary scalar rows. Cardinality-dependent query sorts check every 256 comparisons and unwind before publishing results. Cancellation latency still includes the current key comparison and intervening swaps; per-value JSONB canonicalization and metadata ordering keep their existing checks. - Redis commands: cancellation closes execution-owned sockets, interrupting blocked reads, writes and pipelines even when read timeouts are disabled. SQL, ingestion, partition discovery and partition readers own separate pools. Other statements and maintenance traffic keep their own connections. Streamed results retain their execution pool until completion, cancellation or release.
- Writes:
INSERT,UPDATE,DELETEandMERGEcompute every change before they write one, so cancelled until then they change nothing. There are no transactions, so one cancelled while it writes keeps the rows it has written, as doesCREATE TABLE … AS(whose table is created first). A rename withadbc.redis.rename_rekeyis rolled back. - A Go context's deadline fails the statement the same way, with TIMEOUT. The deadline now also bounds in-flight socket reads/writes for single-endpoint and Cluster node clients, even when the read timeout is disabled. Explicit cancellation interrupts socket waits without requiring a caller deadline.
- SQLSTATE
57014(query_canceled) in both cases, as Postgres reports a cancel and a statement timeout (see Error handling). - Earlier versions (v0.0.11 and before) noticed a cancel only at the statement's next Redis command, which then failed with IO.
Socket deadline failures discard the affected pooled connection. The driver does not retry a timed-out cursor page or turn an uncertain write into rollback. Redis may finish a command after the client has stopped waiting; a write whose reply was lost has an unknown outcome, and earlier autocommit writes remain. Server-side work termination is not promised. Closing the socket bounds client waiting; Redis may already have accepted a pipeline or started a long query. Cancellation does not imply rollback of autocommit writes. Execution clients disable retries for uncertain replies, including cursor reads. Lease renewal and rename/type-rewrite rollback use the maintenance pool and can finish after execution interruption. Known cursor IDs are deleted using the maintenance pool on their original coordinator. If the first reply is lost before its cursor ID arrives, the client cannot delete that unknown cursor; server idle expiry is the remaining cleanup mechanism. Known-cursor deletion makes one best-effort attempt with a one-second cleanup deadline, independent of configured read timeouts. If deletion cannot complete, server idle expiry remains the fallback.
The local fault matrix covers command/pipeline/cursor interruption, finite and disabled read timeouts, client Cluster routing and owner/context cancellation. Live multi-node/TLS deployment evidence must be assessed separately. RESP3 is required; RESP2 requests are rejected under the current protocol policy.
ADBC 1.1 statistics discovery is described in statistics and constraints.
Error handling¶
An error that Postgres has a SQLSTATE for carries the one Postgres 16
reports for the same statement: adbc.Error.SqlState in Go,
AdbcError.sqlstate in C, and exc.sqlstate in Python, whose
adbc_driver_manager also ends the message with SQLSTATE: 22P02. The
SQLSTATE sets the ADBC status, as it does in adbc-driver-postgresql, and so
the Python exception:
| Error | SQLSTATE | Status | Python |
|---|---|---|---|
A value that doesn't convert or doesn't fit (CAST('abc' AS INTEGER), '2024-02-30'::date, 1 / 0, a value too long for its column) |
class 22 |
InvalidData |
DataError |
NOT NULL and CHECK |
23502, 23514 |
Integrity |
IntegrityError |
A table, view, column, function or schema that doesn't exist; an operator for a quoted literal that doesn't exist (b + '1') |
42P01, 42703, 42883, 3F000; 42883 |
NotFound |
ProgrammingError |
A table, view, column, constraint or schema that already exists; a column named twice (in INSERT, a PRIMARY KEY, a table or a view) |
42P07, 42701, 42710, 42P06 |
AlreadyExists |
ProgrammingError |
A syntax error; a column name that several columns have (column reference "x" is ambiguous); a table definition Postgres refuses (two primary keys, DROP NOT NULL of a key column, a column of type record); a type that never converts (DATE to BOOLEAN); a record where another type is needed (argument of WHERE must be type boolean, not type record); values without a common type (CASE, COALESCE, UNION, …; see "Quoted string literals"); an operator of a quoted literal that could be one of several (d + '1', '1' + '1') |
42601; 42702; 42P16; 42846 in a cast, 42804 written to a column or by ALTER COLUMN … TYPE; 42804; 42804; 42725 |
InvalidArgument |
ProgrammingError |
What Postgres doesn't support either (DATE_BIN by months, EXTRACT(day FROM time), ALTER COLUMN … TYPE of a column a view reads) |
0A000 |
NotImplemented |
NotSupportedError |
| A cancel, or a Go deadline | 57014 |
Cancelled, Timeout |
OperationalError |
- Data exceptions have Postgres's code for each case:
22P02for text that isn't a number, boolean, JSON or array;22003for a number out of its type's range (also an integer overflow, and NaN or infinity cast toNUMERIC, which in Postgres overflow aNUMERIC(p, s));22007for text that isn't a date, time, time with time zone, timestamp or interval, or doesn't match aTO_DATEpattern;22008for date and time fields or results out of range ('25:00'::time,MAKE_DATE(2024, 2, 30));22009for a time zone offset out of range ('10:00+16'::timetz);22012for division by zero;22001for a value too long;22023for an argument out of its domain (an unknown time zone orEXTRACTfield,SPLIT_PARTposition 0, a zerogenerate_seriesstep,VARCHAR(0));2201Bfor a regular expression that doesn't compile; and the codes of logarithms (2201E), powers and square roots (2201F), substrings (22011), window function arguments (22013,22014,22016), NULL object keys (22004) and SQL/JSON paths (2203A,22033,22039). - Every path gives them: queries (also while a streamed result is
read),
INSERT,UPDATE,MERGE,ALTER COLUMN … TYPE,DEFAULTs, bound parameters and bulk ingest. A value written to a column has the column's name in front:[redis] column "n": invalid input syntax for type integer: "x".TRY_CASTstill returns NULL for a value that doesn't convert. - Messages of a value that doesn't convert or doesn't fit are
Postgres's: text that isn't a number, boolean, date, time or interval
(
invalid input syntax for type numeric: "abc",date/time field value out of range: "2024-02-30", see Text as an integer and Text as a number, a boolean, a date or a time under Casts), and a number past its type's range (integer out of range,numeric field overflow). Postgres's details and hints are left out, but fornumeric field overflow's, which follows a;. So are those of operators of quoted literals (see Quoted string literals). Many other messages are the driver's, with only the SQLSTATE Postgres's:CAST(DATE '2024-01-01' AS BOOLEAN)iscannot convert date value "2024-01-01" to BOOLEAN, where Postgres sayscannot cast type date to boolean. - No SQLSTATE yet: the errors of the driver's own limits, and a few
others:
more than one row returned by a subquery used as an expression(21000in Postgres), an operator that doesn't exist (42883; those of rows, records and quoted literals have it),missing FROM-clause entry for table "t"(42P01), and aSETof an invalid value (22023). They keep their status, mostlyInvalidArgument. - In Go,
adbc.Error.Error()ends with the SQLSTATE in parentheses:Invalid Data: [redis] division by zero (22012). - Upgrading from v0.0.12 and earlier: errors had no SQLSTATE, and a
value that didn't convert failed with
InvalidArgument(ProgrammingErrorin Python), unlike Postgres; only a value too long wasInvalidData. An unknown column or function wasInvalidArgument, as was a column named twice inINSERT; they areNotFoundandAlreadyExistsnow.DATE_BINby months andEXTRACTof a field the type doesn't have wereInvalidArgument. The messages of text that didn't convert were the driver's:invalid decimal "abc",invalid number "1e",cannot convert "x" to DOUBLE PRECISION(andBOOLEAN), andinvalid date "x",invalid time "x"andinvalid timestamp "x", also for a field out of range; so were those of numbers past their type's range:value 3000000000 out of range for INTEGER,cannot convert float64 value "1e+300" to BIGINTandvalue 123456 out of range for NUMERIC(5,2).
SQL grants¶
Opt-in GRANT SELECT ON [TABLE] table TO role and REVOKE SELECT ON [TABLE]
table FROM role manage dedicated Redis Software ACLs. Configure the database-only,
write-only JSON option adbc.redis.rbac on a trusted administrator handle. Other
SQL privilege forms reject. See provisioning, behavior, dbt scope and interrupted
update recovery.
ACL users¶
Format 16 stores row HASHes under
<table-access-id>~<escaped-schema>:{meta}:snapshot:16:rows:<generation-id>:.
The access identity survives DML, TRUNCATE and rewrites; DROP/recreate allocates
a new identity. Schema adbc is escaped as %61dbc. Existing storage formats
are refused without migration. See snapshots.
The command profile for trusted writers is:
+ping +info +ft._list +ft.create +ft.alter +ft.info +ft.dropindex +ft.aggregate +ft.cursor +ft.search +ft.profile +get +set +del +exists +incrby +hset +hget +hmget +hgetall +hdel +hincrby +hexists +sadd +srem +smembers +sismember +multi +exec +watch +unwatch +eval +evalsha +copy +dbsize +time +type +unlink +pexpireat +hlen +hstrlen +hscan +scard +memory|usage +zadd +zrem +zcard +zrange +zrangebylex +zrangebyscore +zremrangebyscore +zscore +zmscore +zlexcount
# Cluster API clients also need
+cluster|slots +command
+evalsha permits cached control-script execution without ACL audit refusals.
Existing EVAL-only writers remain compatible through the explicit command-refusal
fallback, which can record an EVALSHA denial before selecting EVAL.
Writers need ~adbc:* for metadata, ~adbc_snapshot16_{meta}_* for
their versioned Search index namespace, and
~*~<escaped-schema>:{meta}:snapshot:16:rows:* for each permitted schema.
Temporary tables use ~*~pg_temp_*:{meta}:snapshot:16:rows:*. Keep external HASH
patterns separate. Writers are trusted to mutate the internal catalog. Atomic
scripts and their hash/set/sorted-set commands support publication, retention
and bounded reclamation. Snapshot rename_rekey uses the same writer profile;
the old mutable recipe's INCR, DUMP and RESTORE are no longer required.
For example, a trusted writer for public and temporary tables uses:
ACL SETUSER dbt on >password resetchannels ~adbc:* ~adbc_snapshot16_{meta}_* ~*~public:{meta}:snapshot:16:rows:* ~*~pg_temp_*:{meta}:snapshot:16:rows:* +ping +info +ft._list +ft.create +ft.alter +ft.info +ft.dropindex +ft.aggregate +ft.cursor +ft.search +ft.profile +get +set +del +exists +incrby +hset +hget +hmget +hgetall +hdel +hincrby +hexists +sadd +srem +smembers +sismember +multi +exec +watch +unwatch +eval +evalsha +copy +dbsize +time +type +unlink +pexpireat +hlen +hstrlen +hscan +scard +memory|usage +zadd +zrem +zcard +zrange +zrangebylex +zrangebyscore +zremrangebyscore +zscore +zmscore +zlexcount
HELLO and AUTH are always allowed. Add +select for a nonzero URI database,
and +client|setname if it sets client_name. RESP2-only deployments are
unsupported. Apply per-node ACLs to every primary when using Cluster API;
Redis Software manages users and roles through its management API.
A read-only user needs a registered backend role/ACL domain and the generated lease policy. Generate it without connecting to Redis:
redis-adbc reader-acl -domain <backend-role-or-acl-identity>
Using administrator credentials, register the returned namespace in its registry.
Users sharing one backend ACL share that domain. Its selectors permit only
writes to its lease HASH and additive active-directory registration; they grant
no catalog/row writes or removal of other domains. Keep each parenthesized ACL
selector as one argument to ACL SETUSER. See SQL RBAC for
Software role configuration. Keep its positive metadata grants: %R~adbc:*
would expose import journals containing row values.
The ordinary query command profile alongside these lease controls is:
+ping +info +ft._list +ft.info +ft.aggregate +ft.cursor +ft.search +ft.profile +get +exists +hget +hmget +hgetall +smembers +sismember +zmscore
Add %R~<table-access-id>~* per SELECT-authorized logical table, or deliberately
grant a whole schema with %R~*~<escaped-schema>:{meta}:snapshot:16:rows:*.
Managed copies need permission on their private rows, separate from external
source grants needed for import/refresh. Cluster clients also need
+cluster|slots +command.
Queries, views, information_schema, GetObjects and GetTableSchema use
immutable metadata and the caller's row grants. Broad snapshot reads need
FT.INFO for readiness; finite direct-HASH partitions validate their exact row
directory and pinned descriptor without Search commands. Denying HMGET still
denies their values.
Restricted DML/DDL, including temporary-table creation, is refused at admission
with NOPERM restricted snapshot reader cannot acquire a writer lease before
published data or schema changes. The old SQL MULTI/WATCH errors no longer
represent this boundary; raw transactions still preserve their backend errors.
Initialize a new database as administrator first. Restricted Open inspects
capability without mutation and leaves recovery, retirement and temporary
cleanup to authorized writers.
Search commands and key patterns¶
Search ACL checks vary with server version and deployment. The Software 8.8
writer validation requires the versioned index namespace grant above for
FT.CREATE. Keep Search DDL permissions restricted:
FT.DROPINDEX … DDdeletes every row of a table, even for a user that may only read the table's keys.FT.CREATE,FT.ALTERand theFT.ALIAS*commands can change what the driver's indexes cover. So a user that mustn't change data must not getFT.CREATE,FT.ALTER,FT.DROPINDEXorFT.ALIAS*(the read-only rules above leave them out).- Queries do check keys in Redis 8.6:
FT.SEARCHandFT.AGGREGATEon an index whose key prefix the user can't read fail withNOPERM User does not have the required permissions to query the index, so per-schema key patterns keep a user out of other schemas' tables.FT._LISTstill lists every index, so such a user sees the other schemas' table names. Check this on other Redis versions.
Permission errors¶
A command the user may not run fails with Redis's NOPERM error. SQL
snapshot writes use the writer-lease boundary above. For raw backend
transactions, a refused queued command is reported with its command and key
while preserving EXECABORT for the client, so the transaction cannot appear
to have succeeded.
Limitations¶
Existing HASH collections need redis-adbc¶
A table exists for the driver only if its metadata key
(adbc:{meta}:table:<schema>:<table>) exists. The metadata names the
table's key prefix and index. HASHes and indexes created some other way
(for example your own row:* / idx:rows layout) don't appear as tables
until redis-adbc makes them one: copy bulk-ingests them into a new
table, and adopt makes the HASHes themselves a table, so that SQL reads
what the application writes (see
Existing HASH collections).
Adopting writes nothing into the HASHes, whatever their keys
(user:42, user:alice), and HASHes the application writes later are rows
too. It reads the rows through the application's own search index when
that can serve, and otherwise creates a second one, and an adopted
table refuses INSERT and TRUNCATE. The driver doesn't touch
indexes it didn't create, even one with the name a new table's index would
take (see "Indexes the driver didn't create are left alone" below).
Redis Flex experimental stopgap¶
On this always-on statement-snapshot branch, managed Flex source execution is currently rejected. The upstream stopgap below does not yet implement the snapshot index/publication contract. Its integration remains a feature-parity gate; independent reader-only retained-result replay is separate.
Use adbc.redis.flex=true for the experimental bounded-reader stopgap.
The following compatibility discussion describes default mode and historical
probes; it does not describe the opt-in stopgap.
Redis Flex (formerly Auto
Tiering) keeps most of a database on SSD, for datasets of a terabyte and
more. Search on Flex can't index tables the way the driver needs. Every
table's index has a NUMERIC SORTABLE row-ID field, and every query reads
rows through FT.AGGREGATE cursors. Older Flex builds with Search enabled
fail with Redis Flex databases are not supported, before writing anything.
Software 8.2.6-25 / Redis 8.8 passes the connection guard and writes metadata,
but table creation fails because SORTABLE is unsupported.
A database without Search fails earlier with an error that the Redis server
does not provide the search (RediSearch) module.
| Flex database | Search on Flex | Missing for the driver |
|---|---|---|
| Redis Cloud Essentials | Not available | Search itself (the driver reports that search is missing) |
| Redis Cloud Pro without Search enabled, Redis 8.6 | Unavailable on the newly created test database | FT._LIST, FT.CREATE, FT.SEARCH, and FT.AGGREGATE were not registered. The driver reports that search is missing. |
| Redis Cloud Pro Search on Flex beta | Opt-in beta; not exercised in the live Cloud tests | FT.AGGREGATE and NUMERIC fields (per Redis's docs) |
| Redis Software 8.2.6-25, Redis 8.8.0, Search 8.8.8 | Included (search_on_bigstore); tested 2026-11-04 |
SORTABLE, aggregate cursors, FT.ALTER, FT.DROPINDEX … DD |
| Redis Software 8.2.0-78, Redis 8.6.2, Search 8.6.11 | Included (search_on_bigstore) |
FT.AGGREGATE, FT.ALTER, FT.DROPINDEX … DD; NUMERIC, GEO and SORTABLE fields. FT.SEARCH returns only keys, and indexes need SKIPINITIALSCAN (tested, see Building and testing) |
A 2026-11-04 retest of Redis Software 8.2.6-25, Redis 8.8.0, and
Search 8.8.8 confirmed NUMERIC, GEO, basic FT.AGGREGATE, and field
retrieval now work. SORTABLE, aggregate cursors, FT.ALTER, and
FT.DROPINDEX ... DD remain unsupported. The connection guard now passes
and writes metadata, but table creation fails on SORTABLE. Consequently
TestFlexRefused fails on this release: connecting does not establish driver
compatibility. See the report for exact commands and results.
The historical local and Cloud tests ran on 2026-10-06 against commit 40da0bf.
See the compatibility report or its
2026-10-06 Word version for the settings, live
command results, and missing driver and server functions.
Existing HASH collections: redis-adbc¶
The driver only sees tables that have its metadata. HASHes that an
application wrote (user:1, user:2, …, maybe with the application's own
search index) can still be queried through the driver, made a table one of
two ways.
redis-adbc checks whether each one works, guesses the table's structure,
and says what is missing:
- Copy.
redis-adbc copyreads the HASHes and bulk-ingests them into a new driver table, through the driver. The originals are left as they are. The table is a snapshot: copy again with-mode replaceto refresh it. It needs the Query Engine, a database that isn't Redis Flex, and memory for a second copy of the data. - Adopt.
redis-adbc adoptmakes the HASHes themselves a driver table: SQL reads what the application writes, including HASHes it writes later, without a copy. It writes nothing into the HASHes, and reads them through the application's own search index when that can serve (otherwise it creates one in the driver's format). Its columns are only as typed as the stored text allows (see Adopt below for what an adopted table can't do).
redis-adbc sweep is for the driver's own tables rather than an
application's HASHes: it deletes the rows that writers left under the key
prefixes of dropped and truncated tables (see Sweep
below).
Build and connect¶
Build it from go:
make adbc-cli
Or download redis-adbc-<version>-<os>-<arch>.tar.gz from a
release after
v0.0.10. The Linux binaries are statically linked, and each release runs
them on the same distributions as the driver. On macOS, a binary from a
tarball downloaded with a browser is quarantined, and macOS refuses to run
it; download with gh release download or curl instead, or clear the flag
with xattr -d com.apple.quarantine redis-adbc.
redis-adbc links the driver as a Go library, so it needs neither cgo nor
the shared library. It connects with -uri, else $REDIS_URI, else
redis://localhost:6379/0. Set a password in $REDIS_URI rather than in
-uri, where other users of the machine can see it in the process list.
-option key=value (repeatable) sets a database option, such as
-option adbc.redis.read_timeout=30m. Flags go before anything else.
Check collections¶
Without -prefix, check lists collections from a 10,000-key sample, Search
index prefixes and the driver's tables. With a prefix it samples HASHes and
reports two ways to import them: copy, including its optional key column,
and adopt, which defaults to a private managed copy. Explicit
unmanaged mode instead registers live source HASHes.
For managed copies, application changes
become visible only after an explicit refresh.
build/redis-adbc check -prefix user: -table users
An illustrative, shortened checklist is:
SQL, copied into a driver table (redis-adbc copy): READY
...
run: redis-adbc copy -prefix user: -table users
SQL, imported as a managed copy (redis-adbc adopt): READY
[ok] capture source HASHes directly; SQL uses a private managed index
[ok] 501 HASHes under user: are rows
[missing] a private managed copy and its own Search index
fix: Apply publishes the observed source rows atomically
[ok] source HASHes and application indexes remain unchanged
[warn] capture is a scan of observed data, not an atomic snapshot of application writers
fix: pause source writes if a point-in-time source image is required
run: redis-adbc adopt -prefix user: -table users -apply
Items are ok, warn, missing (the suggested command provides it), or
blocker. check exits successfully whenever inspection completes, including
when a checklist is blocked. It does not change application data, initialize a
catalog or publish metadata; reading an existing catalog uses a metadata pin.
-json returns the same report. The existing sql_in_place checklist identifier
and in_place_type JSON field remain for API compatibility; their current
meaning is managed import. Text output labels the type column IMPORT TYPE.
- Copy checks Search availability, source keys and inferred columns,
sampled conversion errors, destination collisions, indexable column names,
and estimated memory. It allocates private generation storage; external keys
under an old logical destination prefix do not merge into it.
replaceandappendoperate on the managed destination. The noeviction requirement remains. - Managed import uses the same source-decoding and readiness rules as
adopt. Only keys with no further:after the source prefix qualify as rows. Nested HASHes and copy's synthetic key column do not influence import type inference. It checks the target schema, view/table collisions and source identity for explicit refresh. A compatible application index may supply capture; it is never reused as the destination's live index.
The two checklists reuse the existing sample without another source scan.
Partial samples carry a warning; -sample -1 inspects all HASHes. Apply validates
its complete captured input before atomic publication. Inspection does not make
arbitrary application writers participate in the snapshot protocol.
Column inference¶
Every field becomes a column (-rename field=column renames one), plus, in
a copy, _key with each HASH's key
(-key-column names it; empty for none). A column's type is the narrowest that fits the
text of every sampled value, read as a SQL cast reads it:
| Values | Type |
|---|---|
true, false, t, f, yes, no (any case) |
BOOLEAN |
| integers that fit in 64 bits | BIGINT (only 0 and 1 too, with a note) |
fixed-point numbers such as 12.50 (at most 9 decimals), and longer integers |
NUMERIC(38, s), s the most decimals seen |
other numbers: exponents, more decimals, NaN, Infinity |
DOUBLE PRECISION |
YYYY-MM-DD |
DATE |
ISO 8601 dates and times (T or a space, optional fraction and offset) |
TIMESTAMP(p), or TIMESTAMP(p) WITH TIME ZONE if any has an offset (the rest are read as UTC); p is 6, or the most fractional digits seen if that is more |
| not UTF-8 | VARBINARY |
anything else, mixed kinds, and integers with leading zeros (007) |
VARCHAR |
An empty value doesn't count, and reads as NULL in a column that isn't a
string. A field that some HASHes lack is NULL there. When at most 5% of a
column's values don't fit the type the rest share, the column is VARCHAR,
and check suggests -type col=TYPE -on-error null. -type col=TYPE
sets any column's type (SQL type names, as in CREATE TABLE). The values
are read as a cast reads them, so with -type col=TIMESTAMP an offset is
ignored (2024-01-15T10:00:00+02:00 is 10:00), as in Postgres.
Copy HASHes into a table¶
copy takes check's -prefix, -schema, -table, -sample,
-key-column, -type and -rename, and bulk ingest's -mode (create,
the default, append, replace or create_append) and -index-columns:
build/redis-adbc copy -prefix user: -table users -type age=BIGINT -on-error null
501 rows copied into public.users
- Columns are guessed from the first
-sampleHASHes (1,000 by default,-1for all of them) before the copy starts. A field that only later HASHes have isn't a column:copyskips it and names it at the end (skipped fields that the sample didn't have …). Every column is nullable. - A value that doesn't convert to its column's type stops the copy with
the key, the field and the value. With
-mode createthe table it made is dropped again; with the other modes the rows copied before stay. With-on-error nullthe value is copied as NULL instead, and counted at the end. - Consistency: keys come from
SCAN(on every primary of a cluster) and each HASH fromHGETALL, so this isn't a snapshot. A HASH that exists for the whole copy is copied once (SCANcan return a key twice;copyremembers the keys it has read, which takes memory for every key). One created or deleted meanwhile may or may not be copied. - Memory: the HASHes are read and ingested in batches of up to 10,000 rows, whatever the collection's size.
Import or refresh a managed copy¶
Current redis-adbc adopt and Go AdoptHashes read application HASHes into a
private managed table. A dry run reports what Apply would do. Repeating Apply
for the same source and destination explicitly refreshes the copy; application
HASHes and indexes remain unchanged. SQL writes affect the copy. Queries can
read its original key explicitly:
SELECT __key, name FROM users WHERE __key = 'user:42';
__key is provenance rather than a Redis address for the managed row. It stays
out of SELECT * and table schema discovery, survives compaction/DDL/partitions,
and is NULL for private rows inserted through SQL. Source changes remain invisible
until refresh. Refresh recaptures the source rows/schema and replaces private
SQL changes. See current snapshot semantics and the
source-key evidence.
SQL, MERGE and Arrow writes modify the private table; imported __key is
read-only provenance and new rows have NULL __key. Qualified joins can read
it. Arrow replace changes the public schema/data while preserving the import
origin, so a later explicit refresh can still recapture the application source.
See the managed-write evidence.
The application index is used only during capture. The report's source_index
identifies it; SQL uses the managed copy's private index. -own-index captures
HASHes directly. -index-columns controls the private index independently of
-index, including renamed columns and decoded text values. Missing or
incompatible requested source indexes produce a blocker checklist; Apply returns
that report plus an error without replacing the copy. See the
index-policy evidence.
Adopt HASHes in place¶
This subsection records historical in-place behavior. Current managed-copy imports follow the section above; the ownership and live-read rules below do not describe always-on snapshot catalogs.
adopt reads every HASH under the prefix, runs the in-place checklist on
all of them, and prints what it would do. Nothing changes
without -apply:
build/redis-adbc adopt -prefix user: -table users
build/redis-adbc adopt -prefix user: -table users -apply
Adopted: public.users now reads the HASHes under user:, and those the application writes later (index idx:public:users:adopted).
- What it does, in order, when it creates the index (see "The application's index" below for when it doesn't):
- Reserve the key prefix and the index name
idx:<schema>:<table>:adopted(no driver table's index can have that name). The transaction checks again that no driver table's prefix overlaps the prefix. - Create the index: the driver's format (each column as a table's), over
the existing keys, with a
FILTERon@__keyso that it only holds the HASHes that are rows (no:after the prefix). The fields that would set a document's language, score or payload are renamed to ones no application uses (__adbc_language, …). - Wait until the index has indexed every HASH. If it couldn't index one
(a value written meanwhile that a NUMERIC column can't hold), adopt
drops the index (without
DD) and gives the names back. - Write the table's metadata, marked
adopted.
Adopt writes nothing into the HASHes. A HASH the application writes later is a row as soon as the index has it.
An adopt that stopped half-way (the process was killed, say) left a
record in adbc:{meta}:adopting. Running it again with the same -table
finishes it. Adopting HASHes that are an adopted table already does
nothing.
- The application's index. If a search index of the application's
can serve, adopt reads the rows through it, and creates no index of its
own (Adopted: public.users now reads … (through the application's index
idx:users)). The driver never changes or drops that index. It can serve
if it is on HASHes, its only prefix is the table's, it has no FILTER, no
indexing failures and has finished indexing, and it holds a document for
every HASH under the prefix (one made with SKIPINITIALSCAN may not).
Its documents that aren't rows (user:42:prefs) are left out by a
FILTER step in each query. Of several, adopt picks the one with an
attribute for the most columns; -index idx:users names one, and
-own-index creates the driver's index instead. Each statement reads
the index's attributes (FT.INFO), and what runs in the index depends on
them:
| Attribute on the column's field | What runs in the index |
|---|---|
NUMERIC SORTABLE, on a numeric column |
filters, sorts, and aggregates (as on a table's own index) |
NUMERIC, on a numeric column |
filters; the driver sorts and aggregates |
TAG, on a string column |
=, IN and LIKE 'x%' find the rows that may match, and the driver checks them: the TAG's separator splits values into tags, and without CASESENSITIVE it ignores case. = '' needs INDEXEMPTY. The driver sorts and groups |
TEXT, GEO, VECTOR, NOINDEX, one named with AS, or none |
nothing: filters on the column read every row |
check lists which columns get which. -index-columns picks the columns
of an index adopt creates, so it needs -own-index when the
application's index can serve. If the application drops its index, or
changes its prefixes or FILTER, statements on the table fail
("public"."users" reads the application's search index idx:users, which
is gone: …) until it is made again as it was; DROP TABLE still works,
and leaves the index alone.
- Rows are identified by their keys, which the system column __key
holds: like __rowid in other tables, SELECT * leaves it out, but
SELECT __key reads it, and WHERE __key = 'user:42' reads that HASH
directly. Without ORDER BY, rows come in the index's order (there is
no insertion order).
- Columns. Every field is a column, as in a copy. Booleans and ISO dates,
times and timestamps infer their SQL types with per-column text storage
encoding. Reads use the same conversions as SQL casts; empty text reads as
NULL. These columns are stored without a numeric index and are filtered,
sorted and aggregated by the driver. Updates preserve each row's boolean
vocabulary/case and timestamp separator/offset; a previously absent field
uses the format sampled at adoption. No fields are rewritten during adoption.
-type col=TYPE overrides the inferred SQL type and selects the compatible
encoding. -encoding col=text or -encoding col=native explicitly selects
storage (repeatable); native BOOLEAN expects 0/1, and native date/time types
expect the driver's integer representation. Invalid overrides are rejected.
Finite numeric columns retain NUMERIC indexes; a numeric field that is empty
in some HASH remains VARCHAR. -index-columns picks indexed columns; text
encoded boolean/date/time columns always use driver evaluation. Every column
is nullable. Metadata written before text encoding support keeps its types;
drop the adopted table and adopt again to infer typed text columns.
- Strings. The application's later writes don't tell the driver which
strings the index doesn't hold exactly (see "Strings in the index"
below), so an adopted table's indexed string columns count as
truncated. The driver then re-checks string filters, and sorts, groups
and reads string columns itself: always right, but slower.
-trust-strings records the levels measured now instead, for HASHes the
application no longer writes, or never writes with a string the index
doesn't hold exactly.
- Adding and removing adopted rows. Supply the complete Redis key in
an explicit __key column for INSERT, INSERT … SELECT, MERGE … INSERT,
or Arrow bulk ingest (append or create_append mode). For example:
sql
INSERT INTO users (__key, name) VALUES ('user:42', 'Alice');
The key must be under the adopted prefix with no further :. NULL keys,
existing keys of any Redis type, duplicate keys in a batch, and rows with
no non-NULL fields are refused. Only non-NULL column fields are written,
using the application's sampled text formats; neither __key nor
__rowid is stored in the HASH. RETURNING * excludes __key; request
it explicitly when needed. There is no automatic key allocation or key
pattern expansion. Existing restrictions on system columns in joins still
apply to the MERGE match condition.
TRUNCATE is refused by default. To delete an adopted collection's row
HASHes, set the statement option adbc.redis.truncate_adopted to
"true" before executing TRUNCATE users. This SCANs each primary and
UNLINKs row HASHes in place; nested keys and non-HASH keys stay, as do the
metadata and index. RESTART IDENTITY has no effect on application keys.
A statement mixing adopted and managed tables is refused before deletion.
Pause application writers for a complete emptying: this operation is not
atomic across rows or tables, and cancellation or failure can leave a
partially emptied collection. Opted-in TRUNCATE additionally needs the
+scan +type +unlink ACL commands (and the adopted-write script permissions
listed below). Likewise, an application key collision during
a multi-row insert can leave earlier rows inserted; existing keys are never
overwritten. Ingest validates one Arrow batch at a time.
- What an adopted table refuses. Its keys are the application's:
TRUNCATEwithout the statement opt-in,ALTER TABLE … ADD COLUMN, andRENAME TOwithadbc.redis.rename_rekeyfail with… isn't supported on "public"."users", whose rows are HASHes adopted in place (redis-adbc adopt) under "user:".DROP TABLEdrops the adopted index (withoutDD), or none if the table reads the application's, and the metadata. The HASHes stay as they are.DROP COLUMNforgets the column. The field stays in the HASHes.- No new table may have its rows under an adopted prefix: a schema named
userwould put a table's rows underuser:.CREATE TABLEthere fails. SELECT,UPDATE,DELETE, views, joins andALTER TABLE … RENAMEwork as on any table.UPDATEandDELETEchange the application's HASHes.- An
UPDATEthat would set every field of a HASH to NULL fails withUPDATE would leave the HASH user:42 of "public"."users" without fields, and Redis deletes a HASH without fields (and so the row) …, before it changes anything. - Hazards, which
checkandadoptlist too: - An application write of a value that a numeric column can't hold (text,
an empty value,
NaN) takes that HASH out of the index, and so out of the table.FT.INFOcounts it as an indexing failure, andcheckreports it. With the application's index, a value that one of itsNUMERICattributes can't hold does the same.
Tables adopted by redis-adbc v0.0.11¶
v0.0.11 identified an adopted table's rows by a __rowid field, which it
wrote into each HASH whose key
ended in an integer, and its adopt -refresh wrote it into HASHes added
later. The driver now refuses such a table:
"public"."users" was adopted by redis-adbc v0.0.11, whose adopted tables this driver no longer reads: DROP TABLE "public"."users" (its HASHes stay) and run redis-adbc adopt again
DROP TABLE still works, and check and adopt say the same. The
__rowid fields stay in the HASHes; they aren't columns, and do no harm.
ACL permissions for redis-adbc¶
check needs read access to the collection's keys (%R~user:*) and to the
driver's metadata (%R~adbc:*), and these
commands:
+ping +info +scan +hkeys +hmget +ft._list +ft.info +ft.aggregate +memory|usage
+get +exists +smembers +sismember +hget +hexists +hgetall
# A cluster (OSS Cluster API) also
+cluster|slots +command
check leaves out the search indexes on keys the user may not read (Redis
refuses FT.INFO on them), and says how many. Without MEMORY USAGE, it
warns that it can't estimate the memory a copy needs, and goes on.
copy and adopt need the driver's own rules (see
ACL users). copy and adopt also need
+scan +hkeys and read access to the collection's keys (%R~user:*):
+scan +hkeys
HMGET is already in the driver's rules; the tools use HKEYS followed by
HMGET so inferred column order is deterministic.
A user that only queries an adopted table needs the driver's read-only
rules, with read access to the collection's keys (%R~user:*), and
+ft.info if the table reads the application's index (each statement
reads its attributes). One that
inserts, updates or deletes its rows needs the driver's rules, write access to
the keys (~user:*), and the row scripts' permissions:
+hlen +eval +evalsha +script|load
Sweep leftover rows¶
Current snapshot catalogs use registered reference ownership. redis-adbc sweep
previews currently eligible rows without reclaiming them. -apply advances
bounded metadata/data cleanup, protects active readers/writers and reports
confirmed deletions. JSON sets managed_snapshots=true; metadata_pending and
more distinguish deferred work from a completed pass. Repeat Apply when more
work remains. A failed pass returns an error plus its partial report. Unknown
objects and application HASHes/indexes are not selected by a broad prefix scan.
See snapshot cleanup semantics and the
sweep validation.
The remainder of this subsection records historical legacy-prefix behavior; those registry, reuse and permission recipes do not describe current snapshot GC.
DROP TABLE, TRUNCATE and a re-keying rename release the table's key
prefix, and no table takes it again (see "Key prefixes are
never reused" below). Rows can still be left under it, where no table or
query reads them: those of a writer whose process exited before it found
out, and of a write of 1,000 rows or fewer that read the metadata within
100 ms of the release and reached Redis after it. sweep finds them, and
with -apply deletes them. Nothing is deleted without -apply:
build/redis-adbc sweep
Released key prefixes on Redis 8.6.2 (standalone): 4
PREFIX HASHES
public:events: 1
public:orders~2: 12
Left alone:
user: an adopted table had it: the HASHes are the application's
Nothing was deleted. With -apply, sweep deletes the 13 HASHes under 2 prefixes.
build/redis-adbc sweep -apply
Released key prefixes on Redis 8.6.2 (standalone): 4
PREFIX HASHES DELETED
public:events: 1 1
public:orders~2: 12 12
Left alone:
user: an adopted table had it: the HASHes are the application's
Deleted 13 HASHes under 2 prefixes.
- What it deletes: the HASHes whose key is a released prefix and a row
id (
public:orders~2:42), found withSCAN … TYPE hash(on every primary of a cluster). The prefix is the part of the key up to its last:, so the rows of a live table whose name starts with the dropped one's (public:orders2:), or of the table's next prefix (public:orders~3:), are never touched, nor are keys under the prefix that aren't a row id or aren't HASHes. - What it leaves alone, listed with the reason:
- Prefixes that a table has. Up to v0.0.7, the driver gave a dropped
table's prefix to the next table of its name.
sweepchecks again before each pipeline of 1,000 deletions. - The prefixes of a re-keying rename that is still moving rows: a
DROP TABLEduring one releases the table's prefix early. Runsweepagain once the rename has finished. The next connection to open settles one whose connection went away. - The prefixes of adopted tables, also of dropped ones (listed in
adbc:{meta}:adopted:dropped), and the prefixes that overlap them: those HASHes are the application's. v0.0.11 and v0.0.12 didn't record dropped adopted tables, sosweepalso leaves alone every prefix that doesn't have the form of a driver table's,<schema>:<table>:(user:,app:user:profile:). An application's prefix that has that form (app:users:), adopted and then dropped with those versions, can't be told apart: check the dry run before-apply. - Prefixes whose keys the user may not delete (
NOPERM). - Temporary tables' prefixes, which aren't counted as released: a connection that opens drops the temporary tables of connections that went away, rows and all.
- Cost:
SCANgoes through every key of the database (it returns those under the released prefixes' longest common prefix, such aspublic:), which takes a while on a large one. So nothing sweeps when a connection opens. - Writers still running may leave keys after
sweephas passed them. Running it again later deletes those. The counts inadbc:{meta}:releasedstay, so no new table takes the prefixes. -jsonwrites the result as JSON.sweepexits 0 when it could sweep, whether or not it found HASHes, and 1 on an error, after printing what it found and deleted until then.- ACL users:
sweepneeds the driver's own rules, with+scan, and write access to the keys of the schemas it sweeps (~public:*). - Earlier versions (v0.0.12 and before) have no
sweep. It works on databases they wrote, except for the prefixes that v0.0.5 and earlier released: they didn't record them.
Architecture: hybrid index-row layout¶
┌───────────────────────────────────────────────┐
│ Secondary index (RediSearch) idx:<s>:<t> │
│ only filter/sort/aggregate columns, SORTABLE │
└───────────────┬───────────────────────────────┘
finds matching rows │ computes GROUPBY/REDUCE without
(filter, sort, limit), │ opening the HASHes
LOAD reads their fields ▼
┌──────────────────────────────────────────────────────────────────┐
│ Primary data store: one flat HASH per row (every column) │
│ public:sales:1 ─► { __rowid: 1, country: "USA", amount: 45, ... }│
└──────────────────────────────▲───────────────────────────────────┘
│ direct HMGET (full-row lookup)
Rows and indexes¶
- Rows are flat HASHes
<schema>:<table>:<rowid>holding every column (NULL = field absent, except for missing values, below; a hidden__rowidfield keeps all-NULL rows alive). A table that isn't the first of its name, or that has been truncated, has the prefix<schema>:<table>~N:and the indexidx:<schema>:<table>~N(see "Key prefixes" below). - Selective index:
FT.CREATE idx:<schema>:<table> ON HASH PREFIX 1 <schema>:<table>:covering only filterable columns: numeric, boolean, decimal, date/time and timestamp columns asNUMERIC SORTABLE, strings asTAG SEPARATOR "\x1f" CASESENSITIVE INDEXEMPTY SORTABLE UNF. Binary,INTERVALandTIME WITH TIME ZONEcolumns, and columns declaredNOINDEX, are stored in the HASH only. - Strings in the index: a TAG doesn't hold every string as it is.
RediSearch cuts a value at its first NUL byte, splits it at the separator,
trims ASCII whitespace (space,
\t,\n,\v,\f,\r) from both ends of each piece and cuts each piece to 4,096 bytes: so'ab ',' ab'and'ab'all have the tagab, and' 'has the tag''(Unicode spaces are kept). The sort vector holds the whole value, except that it also stops at a NUL byte. So each indexed string column's metadata records whether the index holds all its values exactly, or some only as other tags (tag_values: normalized), or some cut at a NUL byte (truncated). Every write (INSERT,UPDATE,MERGE, bulk ingest,ADD COLUMN … DEFAULT) raises it before writing the rows, and it never goes down (not even onDELETEorTRUNCATE). Then: - On a column held exactly,
col = 'v'is the exact TAG query@c:{v}, soCOUNT, aggregates andLIMITstill run in the index. - Otherwise, and for a constant that isn't held exactly, the query looks
up the constant's tag (
@c:{ab}for'ab ') and the driver re-checks the rows, so the index no longer counts, aggregates or limits on its own for that predicate.INlists, index lookup joins andLIKEprefixes look tags up the same way (and are always re-checked). - A truncated column is also sorted, grouped and read (
LOAD *) by the driver. A constant with an ASCII control character other than whitespace can't be written in a TAG query, so it is checked by the driver. - Missing
tags_checkedfacts count as truncated. Native catalogs retain those conservative facts in their immutable generation; they do not run the legacy in-place background checker. An explicit converting rewrite, such asALTER TABLE t ALTER COLUMN s TYPE VARCHAR USING s, validates the values into fresh storage. Earlier storage formats remain rejected, with no migration path. - TAG facts and visible row images share the statement's pinned generation.
A concurrent writer raising a level cannot change that reader's values
or optimizer facts. A private rewrite whose input changes refuses
publication with
40001; the driver does not replay its SQL. - NaNs in the index:
REALandDOUBLE PRECISIONcolumns holdNaN,Infinityand-Infinity, as in Postgres. RediSearch doesn't index a HASH whoseNUMERICfield isNaN, so up to v0.0.8 a row holding one was written and never seen again: no index query found it (onlyWHERE __rowid = Ndid), soSELECT,COUNT(*),UPDATEandDELETEskipped it, andDROP TABLEandTRUNCATEleft its HASH behind. A NaN is now stored asinfinity, a spelling of +∞ that RediSearch indexes as+infbut that is neither what the driver writes forInfinity(+Inf) nor what RediSearch replies with (inf), so it reads back as NaN (as does theNaNof earlier versions). Postgres puts NaN above every number,Infinityincluded, and NaN equals NaN; in the index a NaN is+inf, which is the right place against every value butInfinity. So: - A comparison with a finite number or
-Infinityruns in the index exactly, as before:x > 5finds the NaNs,x < 5doesn't. One withNaNorInfinitygets the inclusive bound+inf(x = 'NaN'is@x:[+inf +inf]) and is re-checked by the driver, as areINlists, index lookup joins and semi-joins with those values. Index queries write infinite bounds as+infand-inf:x = 'NaN'used to fail withSyntax error … near NaN. - Each indexed float column's metadata records whether it may hold a NaN
(
nans). Writes set it as they raise string levels, in the same transaction before the rows are written, and it is never cleared (not even byDELETEorTRUNCATE). On such a column the driver sorts (ORDER BY), groups (GROUP BY,DISTINCT) and, withadbc.redis.aggregate_pushdownall, computesMIN,MAX,SUMandAVGitself, since the index takes a NaN forInfinity;COUNT(x)still runs in the index. As with string levels, a statement that read the metadata before another connection's first NaN may sort or aggregate that connection's new NaNs asInfinity. - A hash join now matches a NaN with a NaN (it matched it with nothing),
and
INsets and semi-joins look NaNs up by key, asDISTINCTand set operations did, instead of falling back to comparing values one by one. - Rows written before: a row that v0.0.8 or earlier wrote with a NaN
in an indexed float column is still not in the index, and its HASH stays
behind when the table is dropped or truncated. A table has such rows
when its
COUNT(*)is less than the number of keys under itskey_prefix(redis-cli --scan --pattern '<prefix>*' | wc -l, on every primary of a cluster);FT.INFO <index>reports them ashash_indexing_failures(Invalid numeric value: 'NaN'). Writing the row again by its id indexes it:UPDATE t SET x = 'NaN' WHERE __rowid = N. - Ties in the sort vector: the index sorts
NUMERICattributes as doubles, which hold every integer below 2^53 and every decimal of up to 15 significant digits apart from its neighbours, but not largerBIGINTvalues,TIMEandTIMESTAMPvalues as large in their unit (aTIMESTAMP(9)after April 1970, aTIMESTAMPafter 2255 or before 1685) orNUMERICvalues with more digits (from 10⁶ on forNUMERIC, which isNUMERIC(38,9)): 2^53 and 2^53 + 1 are the same double. The index returns rows that tie in the order it indexed them (reversed forDESC), so up to v0.0.12ORDER BY idcould return 9007199254740993 before 9007199254740992, andLIMIT 1pick the wrong one. Rounding never swaps two values, so only rows that tie can be out of order: - The driver reads the sort keys the index returns with each row
(
__sort0, …). Rows that follow each other with the same keys, up to the first one that may round, are a run. A run of two or more rows with a key from 2^53 on (10^(15-s) forNUMERIC(p,s)) is sorted by the rows' values, read withHMGETfor columns the query doesn't select. The rows still stream, a run at a time. - With
LIMIT/OFFSETin the index, the driver asks for one row more on each side. If a run with such a key goes on past them, it reads the rest of the run: by value when the firstORDER BYkey is aBIGINT,TIMEorTIMESTAMPbelow 2^63 (@c:[v v], and anFT.SEARCH … LIMIT 0 0that counts the rows before it if the run started before the rows read), otherwise by asking again with more rows on that side. The rows of theLIMITare read before the first is returned. - Without such ties nothing changes but the two extra rows: the same
commands run, and
LIMITstill runs in the index. Microsecond timestamps between 1685 and 2255 never tie, and nanosecond ones only within about 256 ns of each other.
Discover application indexes without adopting¶
The default creates managed read-only copies, with explicit refreshes for source
changes. To register live source tables that permit SQL writes instead, set
adbc.redis.foreign_indexes_mode=unmanaged before enabling discovery. See
unmanaged tables for current registration, schema,
consistency and write restrictions. The following dynamic-discovery description
describes the historical path, not managed-copy freshness semantics.
Set adbc.redis.foreign_indexes=true on a database or connection to expose
existing HASH search indexes as read-only tables in redis_foreign. It is
disabled by default. Each table has the index's exact name; quote punctuation,
for example SELECT * FROM redis_foreign."idx:users". __key holds each row's
full Redis key. Information-schema and ADBC metadata include these tables.
Discovery reads the index definition and uses FT.SEARCH NOCONTENT to sample
up to 128 byte-exact document keys, then fetches their HASH fields. Indexed
attributes supply columns even for empty indexes, and sampled fields add
columns and refine types. The definition and sample are refreshed for each
statement; no table metadata or application data is written. Types inferred
from a sample can fail on incompatible values elsewhere, as with an adopted
table. Only HASH indexes are included; unindexed HASH collections still need
copy or adopt. Driver-owned search indexes are excluded.
Queries respect the application's prefixes and FILTER, including indexes with
multiple prefixes. Numeric and TAG pushdown follows the same attribute checks
as adopted tables; other predicates run in the driver. INSERT, UPDATE, DELETE,
TRUNCATE, ALTER and DROP cannot change these tables or their indexes. Readers
need the documented read-only ACL commands plus +ft.info and access to the
application key prefixes. An index the ACL user cannot inspect is omitted.
Unbounded foreign-index queries require an unrestricted aggregate result cap.
Metadata and key naming¶
- Metadata, all in one hash slot:
adbc:{meta}:table:<schema>:<table>(column types, defaults, missing values, string levels, NaN flags, comments,CHECKconstraints and the primary key as JSON),adbc:{meta}:seq:*(row ids),adbc:{meta}:tables:<schema>,adbc:{meta}:schemas, and for viewsadbc:{meta}:view:<schema>:<view>(the SELECT text, its columns and comments) andadbc:{meta}:views:<schema>. Tables and views share one namespace. Each table's metadata records its row key prefix and index name, andadbc:{meta}:prefixes/adbc:{meta}:indexesreserve them, so a renamed table's rows can never be shared with a new table of the old name.adbc:{meta}:names:nextholds the last~Nhanded out for each table name (see "Key prefixes" below). New objects store stable catalog OIDs in their metadata, allocated withadbc:{meta}:oid:next; custom schema OIDs useadbc:{meta}:namespace:<schema>. Legacy metadata remains readable without a migration write, using stable IDs derived from its physical identity until those IDs are saved by a change.adbc:{meta}:cleanuplists tables with a dropped-column cleanup in progress.adbc:{meta}:rekeyrecords renames that are moving a table's rows (adbc.redis.rename_rekey), andALTER COLUMN … TYPEs rewriting them (markedrewrite), keyed by the old prefix, andadbc:{meta}:rekey:alive:<id>(30-second TTL, renewed by the renaming connection) shows that the connection doing one is alive. A record taken over from a connection that went away is markedrecovering.adbc:{meta}:releasedcounts how often each key prefix was released (byDROP TABLE,TRUNCATE, a re-keying rename orALTER COLUMN … TYPE), one field per prefix that was ever released; a statement checks it andadbc:{meta}:prefixesto tell when its table's rows have moved away. Metadata written by v0.0.1 (adbc:meta:*,adbc:schemas, …) is migrated automatically on the first connection. - Key prefixes are never reused. A table takes the first of
<schema>:<table>:,<schema>:<table>~2:, … that no table has had before, with the index name of the same N (idx:<schema>:<table>~2). So a table created again afterDROP TABLEgets new keys, and so do a table emptied byTRUNCATE, a table whose rowsALTER COLUMN … TYPErewrote, and a re-keying rename onto a name that a table had before. The reason is writes still running when a table is dropped: a statement that read the table's metadata before theDROPgoes on writing rows under the old prefix until it finds out, and those rows outlive theDROP, which only deletes the rows its index knows about. Up to v0.0.7 the next table of that name took the same prefix, and its row ids started at 1 again, so its rows were written into those leftover HASHes: a column that should have been NULL read the dropped table's value. Now: TRUNCATEmoves the table to new names, as if it were dropped and created again with the same metadata (seeTRUNCATEin "Supported SQL"), so the checks below apply to statements still writing to it. Up to v0.0.7 it kept the prefix: rows such a statement wrote after theTRUNCATEstayed in the table (some not indexed, until anADD COLUMNindexed them), and afterRESTART IDENTITYthe row ids started at 1 again, so later inserts were written into those rows' HASHes. Rows were lost, and new rows read the writer's values for their NULL columns.- An insert reads the table's metadata again in the
MULTIthat allocates its row ids (each Arrow batch, in bulk ingest), at no extra cost. If the table was dropped, truncated, renamed or created again since the statement started, it writes nothing. - A write of more than 1,000 rows (
CREATE TABLE … AS,INSERT … SELECT, bulk ingest, a largeUPDATEorMERGE) also checks that the table still has its keys: between pipelines of 1,000 rows once 100 ms have passed since the last check, and always after its last pipeline (one round trip each). Smaller writes check at the end only when 100 ms have passed since they read the metadata, as before, and so do deletes of any size, which make no key. - A statement whose table was dropped or truncated (or whose rows a
re-keying rename or
ALTER COLUMN … TYPEmoved away) stops and fails: "table … was renamed with its rows moved to new keys, rewritten by ALTER COLUMN … TYPE, truncated or dropped, while this statement was writing to it; some of its changes may be lost or have gone to another table". If no table has the old prefix any more, it first deletes every key it wrote there (one pipelinedDELper 1,000 keys). - Keys can still be left under the old prefix of a dropped or truncated
table, where no table or query reads them: when the writing process
exits before it finds out, and when a write of 1,000 rows or fewer that
read the metadata within 100 ms of the
DROPorTRUNCATEreaches Redis after it (it doesn't check, and succeeds, as if it had run before).redis-adbc sweep -applydeletes them (see Sweep). Nothing deletes them by itself: a connection that opens doesn't look for them, since that means reading every key. - Upgrading: tables created by earlier versions keep their names
(until a
TRUNCATEgives them new ones) and work as before. Prefixes released by v0.0.6 and v0.0.7 are recorded inadbc:{meta}:releasedand are skipped too, so rows they left behind can't reach a new table; v0.0.5 and earlier didn't record releases. Upgrade every client that writes to the database: an earlier version still gives a dropped table's prefix to the next table of that name, and empties a table in place, so itsTRUNCATE … RESTART IDENTITYcan still lose rows that another connection is writing. - Indexes the driver didn't create are left alone. Before it creates a
table's index (
CREATE TABLE, bulk ingest,TRUNCATE, a re-keying rename), the driver drops an index that already has the new name withFT.DROPINDEX … DD: one left over from aCREATEorDROPthat was interrupted. It now first checks withFT.INFOthat the index is one: a HASH index on just the new prefix, without a filter, with no documents. Any other index of that name (an application's ownidx:public:usersonuser:, say) makes the driver take the next names (public:users~2:,idx:public:users~2). Up to v0.0.10 it dropped that index, andDDdeleted every HASH the index covered. - Missing values:
ALTER TABLE … ADD COLUMN c … DEFAULT vdoesn't rewrite the existing rows. As with Postgres's "missing value", the column's metadata recordsvand the row id high-water mark (theadbc:{meta}:seq:*value, read in the transaction that adds the column), and a row with__rowidup to the mark and nocfield readsv. Rows inserted later have higher ids, so for them an absent field is NULL as usual. To keep that true: - A statement that read the metadata before the
ALTERallocates its row ids in oneMULTIwith a read of the metadata, sees the new column, and writesvfor it. - Setting an old row's
cto NULL (UPDATE,MERGE) also writes a hidden marker field,__null_<field>, which setting a value removes. - The index has no entry for an old row's absent field, so a filter on
cthatvsatisfies is widened to(@c:[…] | @__rowid:[-inf mark])and re-checked by the driver (one thatvdoesn't satisfy stays an exact index query), andORDER BYand aggregates oncrun in the driver. TRUNCATEdrops the missing values with the rows.- Temporary tables and views use the same layout in a private schema
per connection,
pg_temp_<id>(rowspg_temp_<id>:<table>:<rowid>, indexidx:pg_temp_<id>:<table>), which is never added toadbc:{meta}:schemas. The connection id comes from the counteradbc:{meta}:temp:nextwhen the connection creates its first temporary object.adbc:{meta}:temp:ownerslists the connections that have a temporary schema, andadbc:{meta}:temp:alive:<id>shows that the owner is still open: it has a 2-minute TTL that the connection refreshes every 30 seconds. Closing the connection drops its temporary objects and these keys. A new connection drops the temporary objects of any owner whose alive key has expired, re-checking the key before each object.
Indexed ADD COLUMN can wait in proportion to Search backfill work, rather
than completing in constant time. Caller cancellation/deadline stops the wait;
an unused index attribute may remain if metadata was not committed, as with an
existing failed ALTER. While dropped-column cleanup is pending, ordinary scans
and aggregates use authoritative HASH input with full residual/global SQL
semantics. Managed tables use allocated-ID pages of at most 1000 keys, so this
fallback adds no SCAN permission. Very sparse ID domains can require more work.
These checks do not provide a transaction or shared snapshot under concurrent
or external DDL. See #178.
Query execution¶
The driver executes SQL as follows:
| Query shape | Execution |
|---|---|
WHERE __rowid = N |
Direct HMGET of the row HASH, index bypassed |
Queries that can't return rows: WHERE false, LIMIT 0, … |
Their rows aren't read. A query with LIMIT 0 (or FETCH FIRST 0 ROWS ONLY, or a HAVING or QUALIFY that is never true) returns without a command. One whose WHERE is never true doesn't read its FROM items, and the rest of it runs over no rows: COUNT(*) without GROUP BY still returns one row, 0. A condition is never true when it is FALSE or NULL whatever the rows hold: its parts that read no column, parameter or subquery are evaluated once, and AND is never true if one side is, OR if both are (false, 1 = 0, NULL, x > 0 AND false, NOT true, …). This applies at every level (derived tables, CTEs, views, join items, UNION branches, subqueries) and to INSERT … SELECT, UPDATE, DELETE, MERGE and CREATE TABLE … AS. The derived tables, CTEs and views such a query reads are planned but not run, so its columns and their types are those of the full query, and unknown tables, columns and functions and wrong argument counts are still errors; ExecuteSchema and CREATE VIEW plan their query the same way. This is how dbt asks for a query's columns (select * from (…) as __dbt_sbq where false limit 0, for model contracts, snapshots and unit tests), and what dbt run --empty reads ((select * from t where false limit 0)) |
| Filter / sort / limit | FT.AGGREGATE <idx> "<pushed-down query>" [SORTBY …] [LIMIT …] LOAD … WITHCURSOR COUNT 10000: the rows come back in the cursor pages, up to 10,000 at a time (fewer for wide tables), and are streamed as the result is read |
COUNT(*), GROUP BY + COUNT/SUM/AVG/MIN/MAX, BOOL_OR/BOOL_AND/EVERY of a boolean column |
FT.AGGREGATE … APPLY exists(@c) … GROUPBY … REDUCE … over SORTABLE fields, HASHes never opened (BOOL_OR / BOOL_AND are MAX / MIN of the stored 0 and 1). On a cluster, a shard whose rows of a group all lack the value sends NaN as its partial SUM, which made the group's SUM and AVG NaN up to v0.0.7 (AVG of an integer column also in the default mode). So when a SUM comes back NaN for a group that has values, the driver runs the command again with case(exists(@c), @c, 0) in place of @c (about 2.5 times as long; a real NaN, from adding +Infinity and -Infinity, stays NaN), and a server without case() aggregates the query in the driver. An amd64 server writes that NaN -nan, which up to v0.0.9 the driver didn't read, so it aggregated such queries itself |
COUNT(DISTINCT col[, …]) |
Eligible queries use exact server-side tuple deduplication, then count tuples in the driver; NULL tuples are excluded. See Aggregate pushdown below |
Other aggregates (STRING_AGG, STDDEV, percentiles, ANY_VALUE, …), other DISTINCT aggregates and FILTER (WHERE …) |
Reduced by the driver over the rows fetched from the HASHes (the index still filters them). An aggregate's FILTER is evaluated before its arguments, so SUM(1 / x) FILTER (WHERE x <> 0) never divides by zero. Ordered-set aggregates keep each group's values and sort them once |
GROUP BY ROLLUP / CUBE / GROUPING SETS |
Each grouping set runs as its own grouped query: an FT.AGGREGATE … GROUPBY when a plain GROUP BY of its columns would be one, otherwise in the driver, where the sets share one read of the rows. The driver combines the groups of all sets (UNION ALL, NULL for the columns a set doesn't group), then applies HAVING, window functions, QUALIFY, DISTINCT, ORDER BY and LIMIT to the combined rows |
col LIKE 'abc%' on an indexed string column |
TAG prefix query @c:{abc*} on the prefix's tag, re-checked by the driver (other patterns are checked by the driver alone). RediSearch expands a prefix into at most search-max-prefix-expansions tags (200 by default, per shard) and silently ignores the rest, so the driver first runs the prefix query under FT.PROFILE … LIMIT 0 1 (one more round trip, about 0.3 ms) and, unless every shard's profile reports no warning, checks the prefix on every row the rest of the WHERE clause selects instead. The server's configuration is never changed |
col IN (…), col IN (SELECT …), col = a OR col = b on an indexed column |
Index union query ((@c:[a a] \| @c:[b b]) or @c:{a \| b}), for up to 1,000 values. Above that (or on an unindexed column) the driver checks each row against a hash set of the values, built once per statement: always for IN (SELECT …), and for a literal list of 16 or more constants when the column's type can't fail to compare with them (otherwise value by value, as = would) |
| Subqueries | Uncorrelated: run once per statement, results reused. Correlated [NOT] EXISTS / [NOT] IN over one table, CTE or derived table, whose only references to the outer query are inner = outer conditions in its WHERE: a hash semi-join (anti-join for NOT), which runs the subquery once without those conditions. A small inner side (≤ 1,000 rows, counted by the index) is read first, and then EXISTS / IN also filter the outer table in its index (nothing matches if the subquery has no rows; a union of ≤ 1,000 keys on an indexed outer column); a larger one is read only once running per outer row has cost about as much. Other correlated subqueries: run per outer row with the outer values as constants (so they still use the index), memoised |
(a, b) [NOT] IN (SELECT …) (and = ANY, <> ALL) |
The subquery runs once, and each row is checked against a hash set of its rows, built once per statement. Telling NULL from false (for NOT IN) costs one lookup per pattern of NULLs among the subquery's rows. If the subquery has no row without a NULL, IN reads no row. For each indexed column among the items with ≤ 1,000 distinct values in the subquery's rows, an index union fetches only the rows with those values. Correlated, with only inner = outer conditions: the hash semi-join below, with a set of rows per key. A literal list, (a, b) IN ((1, 'x'), (2, 'y')), is an OR of ANDs; each indexed column that every row of the list sets gets an index union (for up to 1,000 rows). A list of 16 or more rows of constants is checked against a hash set of its rows, as IN (SELECT …) is, when the items' types can't fail to compare with the values in their position (otherwise row by row, as the =s would) |
(a, b) > (x, y), >=, <, <=, and row BETWEEN |
When a is an indexed numeric, date or time column, the index takes the bound the comparison implies on it, a >= x (a <= x for < and <=), and the driver checks the whole comparison on the rows the index finds. So keyset pagination, WHERE (ts, id) > (?, ?) ORDER BY ts, id LIMIT n, reads the rows from the last page's ts on, not every row before them too. Strings are only looked up by equality, so a string a is checked on every row |
x op ANY / ALL (SELECT …) |
= ANY is IN and <> ALL is NOT IN, with the same index unions, hash sets and semi-joins. Other operators: an uncorrelated subquery runs once, and <, <=, >, >= compare with its smallest or largest value (when all the values are of one type class); otherwise each row is compared with every value |
UNION / INTERSECT / EXCEPT |
Each branch runs as its own query (using its own index); the driver combines, de-duplicates and sorts the results |
| CTEs, derived tables | Run once, unless the query that reads them can't return rows (see above); the outer query filters, sorts and groups them in memory. One that reads an enclosing query's columns runs when the query that reads it does instead, once per distinct set of the values it reads (memoised like a correlated subquery, and shared by a CTE's references) |
WITH RECURSIVE |
Working-table iteration in memory: the non-recursive term runs once, then the recursive term runs against the rows the last iteration added (its joins with tables still use their indexes, such as an index lookup join on the new rows' keys) until it adds none. UNION drops rows produced before. At most 10,000 iterations and 1,000,000 rows. With SEARCH / CYCLE, the recursive term runs once per row of the working table, to know each row's parent |
LATERAL, GENERATE_SERIES |
Join items computed while the join runs. One that reads earlier FROM items runs for each row joined so far (a LATERAL subquery memoised on the values it reads, like a correlated subquery); otherwise it runs once and is joined like a derived table. A join with such an item keeps its written order. GENERATE_SERIES makes its rows in memory, at most 1,000,000 per call |
SELECT DISTINCT |
Over plain expressions it is the same as GROUP BY them, so it runs in the index (FT.AGGREGATE … GROUPBY) when they're indexed columns. Otherwise (stars, aggregates, window functions, DISTINCT ON) the driver removes duplicate rows after the rest of the query, NULLs counting as equal. LIMIT / OFFSET apply afterwards and never run in the index. As in Postgres, ORDER BY must use the select list, and DISTINCT ON keys must match the leading ORDER BY expressions |
| Window functions | Computed by the driver once the rows are known: after WHERE, GROUP BY and HAVING (which still run in the index when they can), before QUALIFY, ORDER BY and LIMIT. Rows are hashed into partitions and each partition is sorted once per distinct PARTITION BY / ORDER BY; frame aggregates add and remove rows as the frame slides (O(1) amortized per row; sums, averages and variances add and subtract exact sums, also of doubles). With EXCLUDE CURRENT ROW / GROUP / TIES the frame has a hole, so each row's frame is aggregated afresh (O(frame) per row), as PostgreSQL does; FIRST_VALUE / LAST_VALUE / NTH_VALUE stay O(1), also with IGNORE NULLS. A query with window functions never pushes its LIMIT into the index |
CREATE TEMP TABLE / VIEW |
Same as a permanent table or view, in the connection's pg_temp_<id> schema. Unqualified names are looked up there first (in memory, no extra round trip) |
TRUNCATE |
Like DROP TABLE and CREATE TABLE with the same metadata: reserves a new key prefix and index name and runs FT.CREATE for each table, switches the metadata of every table of the statement to the new names in one WATCH/MULTI (which also releases the old names), then runs FT.DROPINDEX <old> DD, which deletes every row the old index knows about. Statements still writing to the table find out and fail (see "Key prefixes are never reused") |
ALTER TABLE |
Metadata only (optimistic WATCH/MULTI on the table's metadata, one for all the actions of a statement), plus FT.INFO and one FT.ALTER for the added columns, waiting for Search backfill before metadata publication, and a background HDEL pass for DROP COLUMN. ADD COLUMN … DEFAULT records a missing value instead of writing the rows. Adding a CHECK (ADD COLUMN … CHECK, ADD CONSTRAINT … CHECK) or a NOT NULL (ALTER COLUMN … SET NOT NULL, ADD COLUMN … NOT NULL without a default, and the columns of ADD PRIMARY KEY) first reads the columns they use from every row, with one FT.AGGREGATE cursor scan for all the statement's new constraints, to check them, and when it commits, the rows inserted since (by __rowid). With adbc.redis.rename_rekey, RENAME TO also creates the new name's index, copies every row the old index lists with pipelined DUMP / RESTORE … REPLACE (a cursor page at a time; one key per command, so it works on a cluster), switches the metadata in one transaction, then runs FT.DROPINDEX <old> DD. ALTER COLUMN … TYPE that converts values does the same under the table's own name: FT.CREATE of the new names' index with the new attributes, pipelined HGETALL of every row the old index lists and HSET of each converted row to its new key, one transaction to switch the metadata, then FT.DROPINDEX <old> DD; it also scans the views (SMEMBERS and GET of each one) to find those that read the column |
| Views | CREATE VIEW plans the body without running it, which checks its tables, columns and function calls. Single-table views without GROUP BY/aggregates/window functions/LIMIT are expanded in place: the outer query's filters are rewritten over the base table and run in its index. Other views are computed once per query, like a derived table |
| Joins | Each table's own WHERE/ON filters run in its index (except on the NULL-supplying side of an outer join). Inner joins are reordered to start from the table with the fewest matches (counted by the index). Equality conditions drive a hash join; when the next table's key is indexed and there are ≤ 1,000 distinct keys, only matching rows are fetched with an index union. The joined rows are then grouped/sorted in memory. NATURAL JOIN is an equality join on the common columns, like USING. An item whose ON can never be true (ON false), or that has no rows, isn't read when that settles the result: an inner join is then empty and reads no item, a LEFT JOIN keeps the left rows with NULLs for the item, and a RIGHT JOIN returns the item's rows without reading the items before it. A FROM-list item after a comma that has a RIGHT or FULL join is joined on its own, then cross joined with the rows of the items before the comma (see "Commas"), and WHERE conditions that read both are applied after that. An ON condition with a correlated subquery waits until every item it may read is joined (up to v0.0.12, in an inner join of three or more items it could run before, and from a join b on true join c on exists (select … where … = c.x) returned no rows) |
UPDATE … FROM, DELETE … USING, MERGE |
The target is joined with the other items as above: its own filters (in WHERE, or in MERGE's ON) run in its index, and an equality on an indexed target column is an index lookup join, also through a no-op cast like dbt's s.id::text = t.id::text. MERGE is source LEFT JOIN target, or FULL JOIN with WHEN NOT MATCHED BY SOURCE clauses (which need every target row); ON FALSE reads the target only for those. Changes are then written by row key with pipelined HSET/HDEL/DEL, and new rows like INSERT does |
| Anything the index can't answer exactly | Evaluated by the driver on rows fetched from the HASHes |
For ordered managed-table queries with a residual predicate and LIMIT, the
scan can defer output columns: it reads predicate inputs and ordering keys,
checks the exact SQL predicate, corrects rounded sort ties, and applies
OFFSET/LIMIT before fetching output fields from the selected HASHes. Deleted
rows found during that fetch do not consume LIMIT. This also works with
adbc.redis.stream_results=false. It does not add indexes or convert SQL
substring LIKE into tokenized text search. Windows, correlated subqueries,
adopted rows, and driver-side sorting retain their existing scan path.
Driver-side aggregates share pure integer input subexpressions within each
row. A typed evaluator handles integer columns, literals, and checked
+, -, and *, preserving aggregate order and per-row overflow errors.
Each aggregate still updates its own exact accumulator. Decimal/float inputs,
filters, DISTINCT, functions, casts, subqueries, and unsupported expressions
use the existing evaluator; expressions are not rewritten into affine sums.
Streamed results¶
For native Arrow IPC stream/file export and ingestion through standard ADBC statement options, see Arrow IPC interoperability.
For distributed reads across independent workers, see ADBC partitioned reads.
A query that reads one table through its index, row by row, is streamed:
its rows are read from the FT.AGGREGATE cursor a page at a time as the
result is read, so the driver holds about one batch of them, whatever the
table's size. Eligible UNION ALL trees, described below, also stream. Other
results are built whole before ExecuteQuery returns.
- What streams:
- a single
SELECT(not a script), run without parameters or with one row of them; - on a table, not a view, CTE, derived table or join;
- matched through the index, rather than by row id (
WHERE __rowid = N); - without aggregates,
GROUP BY,DISTINCT, set operations, window functions or correlated subqueries; - in insertion order, or ordered by indexed columns the index sorts
exactly. Nullable numeric
NULLS FIRSTordering can use the index when a pinned visibility proof finds no NULL candidates. Other driver-side ordering (such as an expression) reads the rows whole. Rows that tie on a large number in the index are sorted again as they stream (see "Ties in the sort vector" above).
LIMIT and OFFSET that can't run in the index (because the driver
re-checks part of the WHERE clause) are applied as the rows stream, and
the cursor is deleted once the limit is reached.
- UNION ALL. Trees without ORDER BY stream when every nonempty leaf is
an eligible row-local scan of a permanent managed table through its index,
in insertion order. The driver opens one child scan at a time, preserves
duplicates and pairwise common-type conversions, and applies each branch's
and the combined query's LIMIT/OFFSET at its own level. No SQL ordering is
promised without ORDER BY. A LIMIT can stop before opening later children;
errors in a child that is reached can arrive during fetching. Key lookups,
HASH fallbacks, views/CTEs, subqueries, joins, windows, DISTINCT set operations, record
outputs and value-dependent decimal schemas retain materialization. The row
and accounted-byte batch settings apply after coercion; one active scan page
can overshoot those output limits. See the measurements and limits.
- Row count. ExecuteQuery reads the first batch before it returns, so
a result confirmed complete during that fill is returned whole, with its row
count. Other results are streams, whose row count is -1.
- Errors. One in the first batch comes from ExecuteQuery. A later one
(division by zero on row 100,000, say) ends the stream: Next returns
false, and Err reports it.
- Cursors. A stream keeps its FT.AGGREGATE cursor open until it is
read to the end, released, or its connection is closed (reading it then
fails). Cancelling the context passed to ExecuteQuery (in Go) also
deletes it, as does cancelling the statement (AdbcStatementCancel;
cursor.adbc_cancel() in Python), from any thread: reading the stream
then fails with CANCELLED (see
Cancelling a statement). A read under
way first waits for the cursor page it asked for. Redis limits cursors:
- It deletes one that isn't read for search-cursor-max-idle (300
seconds by default). Reading the stream then fails, with a message
naming adbc.redis.stream_results.
- An index has at most search-index-cursor-limit open cursors (128 by
default), across every client. A query on a table with that many open
streams fails until one ends.
- Options. adbc.redis.stream_results=false (database or statement)
turns streaming off. adbc.redis.stream_batch_rows sets the rows per
batch (65,536 by default). adbc.redis.stream_batch_bytes adds an opt-in
width-sensitive accounted-byte ceiling (default 0: disabled), on both APIs
and generic streamed expressions. Each projected value accounts 256 bytes
plus twice its string/binary payload, decimal magnitude plus 32 bytes, and
recursive array/record children. NULL slots and duplicate outputs count.
The first row can exceed the budget; subsequent rows start a new batch before
crossing it. The row ceiling still applies. Redis still fetches a cursor page
at a time, so page input and completed batches from that page can coexist.
This setting bounds accounted bytes per output batch, not process RSS,
allocator capacity, retained consumer batches or a single oversized row.
Partition workers set it on their database; planner statement settings are
not carried inside partition descriptors. See the
byte-budget measurements and tuning guidance.
Eligible simple column projections on managed tables build Arrow batches
without buffering a projected Value row for every result. Regular and
partition readers share this path. It keeps one reusable scratch row and
bounded Arrow batches; cursor-page overshoot and variable-width values still
affect memory, so the row setting is not a byte limit. Expressions, residual
filters and other unsupported shapes retain the generic path. See the
direct Arrow scan measurements
for allocation, memory and elapsed-time tradeoffs.
The later matched RESP3 parser comparison
records transfer-speed measurements after removing redundant Search field maps.
The wide HASH-fetch comparison
records the subsequent guarded retrieval change, including matched native
references and its higher peak-memory tradeoff.
The direct HASH-to-Arrow follow-up
removes per-row raw maps from simple wide projections while retaining the
generic and missing-default paths.
Pushed down into the index: numeric range/equality predicates on indexed
columns (@c:[lo hi]), string equality on indexed columns (@c:{value},
re-checked by the driver once the column holds a value the index trims,
splits or cuts; see "Strings in the index" above), ORDER BY on indexed
columns, LIMIT/OFFSET, and aggregates. A constant
that doesn't fit the column's type exactly (int_col > 1.5, numeric_col =
1.249, date_col < TIMESTAMP '… 12:00:00') is pushed as an inclusive bound
at its rounded value and re-checked by the driver, as is a NaN or
Infinity compared with a float column (see "NaNs in the index" above). A
TIMESTAMP WITH TIME ZONE column compared with a local time (text without
an offset, a TIMESTAMP or a DATE) is pushed as that local time's
instant in the session time zone. A TIMESTAMP column compared with text
with an offset or a zone is pushed as the time written, without the
offset, as Postgres reads the text. A TIMESTAMP or DATE column compared
with a timestamp with time zone is pushed as the range of local times that
can be that instant (in a DST gap, 02:30 is the same instant as 03:30) and
re-checked.
Row values always come from the HASHes as stored, never from the index sort
vectors: LOAD @c on a SORTABLE numeric attribute returns the sort vector's
double printed with 12 significant digits, which would round 64-bit integers,
timestamps, decimals and doubles. So the driver names the columns it needs
(LOAD n @__key @__rowid @c …) only when each one is a string (unless the
column may hold a NUL byte, at which the sort vector stops), a 16/32-bit
integer, boolean, date or time, or not indexed. When a managed-table scan
needs at most half the table's columns, it loads the exact-safe fields and
keys through the cursor and reads the remaining fields from the HASHes
with pipelined HMGET (at most 1,000 commands per batch). This preserves
exact BIGINTs without transferring every column of a wide row. A managed scan
that would otherwise use LOAD * also uses key/row-ID discovery plus HMGET
when it needs at least 64 columns and more than half the table, has no residual
predicate, late materialization, tie sorting or pushed limit, and is unordered
or sorted by ascending internal row ID. Its cursor pages and pipelines hold up
to 4,096 rows. Simple streamed column projections decode positional HASH values
into prebound Arrow slots; other eligible wide execution adopts fetched maps.
Rows requiring missing-default semantics can still use a temporary raw map.
This reduces repeated field names in replies and round trips, at the cost of higher peak memory.
Other wide projections and adopted tables use LOAD *, which returns HASH fields
unchanged. Both two-stage paths can observe concurrent changes between the
cursor and HMGET; queries do not provide a snapshot. Sorts run on aliases
(LOAD @c AS __sort0 SORTBY @__sort0) so that on a cluster the coordinator
merges the shards' results on the numeric sort values rather than on the
strings LOAD * returns. Rows whose sort values tie as doubles are sorted
again by the driver (see "Ties in the sort vector" above).
The narrow-scan profile compares both paths on the official 100,000-row ClickBench fixture, including transferred bytes, allocations and exact aggregate query timings.
Aggregate pushdown¶
The adbc.redis.aggregate_pushdown option controls where aggregates run:
exact(default): pushes COUNT, plus SUM/AVG/MIN/MAX over 16/32-bit integer, boolean and date columns, which RediSearch returns exactly, and BOOL_OR/BOOL_AND/EVERY over boolean columns. Other aggregates are computed by the driver, except for the exact COUNT DISTINCT strategy below.all: also pushes floating-point, decimal and 64-bit aggregates, and groups on such columns (see below). RediSearch adds doubles in its own order (on a cluster, each shard's rows, then the shards' sums) and reports the result with 12 significant digits;AVGis that sum divided by the count. That is the exact sum when it has at most 12 significant digits, such as amounts with two decimals, and RediSearch's rounding errors stay below the 12th digit; otherwise it differs from it after the 12th digit (SUMof 1/3 and 1/7 is 0.47619047619, not 0.47619047619047616), and sums that cancel can lose their small values (1e16, 1, -1e16, 3, 1e16, 0.5, -1e16 came back as 4, not 4.5). A float column that may hold a NaN is grouped and reduced by the driver (see "NaNs in the index" above).- Groups are exact. The index groups (
GROUP BY,SELECT DISTINCT) on its doubles too, and replies with a group's value in full if it is an integer and with 12 significant digits otherwise. The driver takes the index's groups only if each one's value is exactly a value of the column, and otherwise groups the rows itself, after thatFT.AGGREGATE. That is aBIGINT,TIMEorTIMESTAMPvalue below 2^53 in its unit (aTIMESTAMPfrom 1685 to 2255), aNUMERIC(p,s)value of at most 12 significant digits (any for p ≤ 12, otherwise those below 10^(12-s): 1,000 forNUMERIC, which isNUMERIC(38,9)), and aREALbelow 2^63 or infinite.DOUBLE PRECISIONcolumns (5.0000000000001 is replied as 5) and nanosecondTIMESTAMPs (past 2^53 from April 1970) aren't grouped in the index at all. Up to v0.0.12 the driver took the index's groups as they came: 9007199254740992 and 9007199254740993 were one group, as wereTIMESTAMP(9)values of 2024 up to 256 ns apart;SELECT DISTINCTreturned 0.3 twice for 0.3 and 0.30000000000000004, and 9999-12-31 23:59:59.999999 as 10000-01-01. - So are
MINandMAXof aBIGINT,TIMEorTIMESTAMP, which come from the index on the same condition, andSUMs of integers. RediSearch's sum of integers is exact while the count times the largest magnitude is below 2^53: for aBIGINT, the driver asks the index for the group'sMINandMAXtoo; for anINTEGER(in theexactmode too), up to 4 million values. The driver computes the others. Up to v0.0.12,MINof -9007199254740993 was -9007199254740992, and theSUMof 9007199254740992, -9007199254740993 and 5 was 5.MINandMAXof decimals and doubles keep the index's 12 significant digits. none: always aggregates in the driver.
The other modes compute SUM and AVG of doubles exactly (see
Aggregates, below), so their results don't depend on the mode, the order of
the rows, or the server being standalone or a cluster.
Driver-side aggregates over indexed tables consume one cursor page at a
time, updating their accumulators before fetching the next page. Each group
retains only the representative fields used after reduction. Eligible
ORDER BY … LIMIT … OFFSET … queries select the best LIMIT + OFFSET
projected groups with a bounded heap, preserving stable ties; queries with
windows or DISTINCT keep their existing ordering path. Exact distinct sets,
group state and ordered-set inputs still grow with cardinality. In-memory
relations and the shared input for grouping sets retain their existing
materialization. The aggregation profile
reports latency and memory across input sizes and group cardinalities.
In exact and all modes, queries whose aggregates are all
COUNT(DISTINCT col[, …]) can deduplicate complete tuples in Redis and
count them in the driver, without transferring every source row or retaining
client distinct sets. Eligible grouping and argument columns are indexed
strings, integers (including BIGINT), booleans, dates, times and timestamps.
Safe sorting vectors need no load. For BIGINT, timestamps and NUL-containing
strings, LOAD * before GROUPBY uses the original HASH strings; named
loads still use rounded or truncated index values. This preserves values
above 2^53 and embedded NUL bytes without adding stored fields or indexes.
Complete tuples are merged across shards before counting, and any tuple
with a NULL argument is excluded. Redis GROUPBY uses a hash as its identity;
per-column TOLIST sets compare complete values, and MIN/MAX of presence
flags check NULL/value mixtures. A hash group containing different tuples
triggers the exact row-based fallback. For NUL-containing columns, the
strategy also verifies Search 8.6 or newer with MODULE LIST; older versions
or an ACL that denies that command retain the exact row path.
Aggregate filters use exact index predicates or pure comparisons over tuple
columns. The latter also recheck normalized string predicates, such as
SearchPhrase <> ''. A separate group query preserves zero-count groups
when every aggregate is filtered. Equivalent aggregate calls share a
pipeline; different calls use separate pipelines. This does not provide a
snapshot across concurrent writes. Adopted tables, unindexed columns,
virtual defaults, noncanonical stored values, expressions, mixed aggregates
and other predicates retain the exact row-based path. none disables tuple
deduplication. The distinct benchmark
records wire bytes, latency, driver allocations and server memory. Raw
loading moves work and tuple state to Redis; high cardinality can transfer
as many tuples as input rows, and there is no spill or new memory limit.
Other DISTINCT aggregates, filters outside that scope and other aggregates
run in the driver. RediSearch has reducers that look like some of them, but none
gives SQL's answer: STDDEV loses precision on values far from zero (150
values near 10⁹ came back wrong in the 8th significant digit, on a cluster
too) and is 0 instead of NULL for a single value, QUANTILE is an
approximate streaming estimate, TOLIST de-duplicates in no particular
order, and FIRST_VALUE returns NULL when the group's first row lacks the
field, even if later rows have it.
ADBC partitioned reads¶
By default, ExecutePartitions / ReadPartition split a SELECT on one permanent,
driver-managed table into portable descriptors for independent worker processes.
Planning returns an Arrow schema and disjoint internal row-ID ranges without
fetching result rows; workers use their own authenticated connections and stream
Arrow batches after the planning statement and connection have closed.
Set the statement option adbc.redis.partition_count to request up to 1–1024
partitions (default 8). This limits splits, not worker concurrency or rows per
partition; gaps and deletes can produce empty or uneven ranges. Supported SQL
includes one typed bound parameter row, arithmetic, casts, CASE and allowlisted
row-local functions. Joins, aggregates, ORDER BY, LIMIT, views, temporary tables
and adopted/foreign layouts remain unsupported. Reads use cap-bounded Search
ranges and row-ID sequences below 2^53; hidden caps require the explicit
adbc.redis.partition_scan_cap deployment contract.
adbc.statement.exec.incremental=true emits the immediately available descriptors
once, then empty terminal sets on repeated calls. Reset the query/binding or
option to begin another execution. This is finite partition discovery; it does
not provide continuous subscriptions or CDC.
Managed live descriptors retain one shared producer generation, including its
schema and values across later writes and DROP/recreation. Output order is
unspecified. adbc.redis.partition_result_ttl bounds admission of new readers;
active readers own independent renewable pins. Get statement option
adbc.redis.partition_snapshot_id after production, then use
DROP PARTITION SNAPSHOT '<id>' to prevent new readers before expiry. Producer
close and freeing descriptor bytes do not revoke worker copies. Close worker
Arrow readers promptly to release their pins and Search cursors. Descriptors
contain query data but no URI or password and do not grant access to Redis.
Legacy version-1 live descriptors must be regenerated for snapshot catalogs.
Opt-in adbc.redis.partition_result_mode=retained executes a bounded global SQL
query once and stores immutable final Arrow chunks. It adds ordering, aggregates,
windows, joins, CTEs/views/subqueries and supported application/legacy layouts,
including exact large row IDs. Workers configure the result namespace and
reader-only bootstrap to replay after source writes, DDL or producer death.
Fixed expiry, quotas and explicit revocation govern stored output. See the
retained-results contract for options,
limits, ordering, ACLs and costs; this freezes completed output, not an atomic
source snapshot.
Rebuild the native library from current source before using this API; it is not yet included in published release wheels. See the partitioned-read guide for the exact SQL subset, lifecycle and Python API, and the runnable process-pool example.
Supported SQL¶
See generated identifiers, UUIDs, modifying CTEs, named indexes and materialized views for the new statement contracts and limitations.
Division involving NUMERIC uses exact decimal arithmetic, preserving operand
scale and at least 16 significant digits. Floating-point division remains
floating-point. Unconstrained decimal expressions have value-dependent Arrow
precision and scale, so their results are materialized before publishing the
schema. An empty result uses the declared fallback Decimal256(76,16). Cast the
expression to NUMERIC(p,s) for a fixed schema, including empty results, and to
allow otherwise streamable queries to stream.
Schema objects and DDL¶
CREATE TABLE [IF NOT EXISTS] t (col TYPE [column_constraint …] [NOINDEX] [COMMENT 'text'], … [, table_constraint, …]) [COMMENT [=] 'text'], where a column constraint is[CONSTRAINT name] {NOT NULL | NULL | CHECK (expr) | DEFAULT expr | PRIMARY KEY | UNIQUE | REFERENCES …}and a table constraint is[CONSTRAINT name] {CHECK (expr) | PRIMARY KEY (cols) | UNIQUE (cols) | FOREIGN KEY (cols) REFERENCES …}(see Constraints below; theCOMMENTclauses, MySQL / Snowflake style, set the same comments asCOMMENT ON),CREATE TABLE [IF NOT EXISTS] t AS SELECT …(column names and types come from the query; every indexable column is indexed),DROP TABLE [IF EXISTS] t [, …] [CASCADE | RESTRICT],CREATE SCHEMA [IF NOT EXISTS] s,CREATE [OR REPLACE] VIEW [IF NOT EXISTS] v [(cols)] AS SELECT …,DROP VIEW [IF EXISTS] v [, …] [CASCADE | RESTRICT],ALTER VIEW [IF EXISTS] v RENAME TO w(ALTER TABLE v RENAME TO walso renames a view, as in Postgres; otherALTER TABLEactions on a view are rejected). Renaming changes metadata only; views that read the object by its old name stop working, as they do after a table renameCREATE {TEMP | TEMPORARY} TABLE [IF NOT EXISTS] t (…),CREATE TEMP TABLE [IF NOT EXISTS] t AS [(]SELECT …[)],CREATE [OR REPLACE] TEMP VIEW [IF NOT EXISTS] v [(cols)] AS SELECT …, and bulk ingest withadbc.ingest.temporary. Temporary tables and views work like permanent ones in every statement (joins with permanent tables, CTEs,ALTER,TRUNCATE,DROP, …), with these differences:- Scope: only the ADBC connection that created them sees them, so two connections can each have a temporary table with the same name.
- Lifetime: they are dropped when the connection is closed
(
AdbcConnectionRelease). If the process exits without closing it, the next connection to open after the 2-minute heartbeat TTL has expired drops them. A connection that can't reach Redis for longer than that may lose its temporary objects. - Names: an unqualified name means a temporary table or view first, so
it shadows a permanent one of the same name, as in Postgres.
schema.talways means a permanent table, andpg_temp.tthe temporary one (CREATE TABLE pg_temp.tcreates a temporary table). WithoutTEMP,CREATE TABLE/CREATE VIEWcreate permanent objects, and bulk ingest withoutadbc.ingest.temporarytargets the permanent table. The schema namespg_tempandpg_temp_<digits>are reserved. - Metadata:
GetObjectsandinformation_schemalist them under the schemapg_temp, only for the connection that owns them and only while it has any.information_schema.tablesreports temporary tables asLOCAL TEMPORARY, as Postgres does. - Views: a temporary view may read temporary and permanent tables. Its
unqualified names resolve like the connection's own statements:
temporary objects first, then the schema that was current when the view
was created. A permanent view can't refer to a temporary table or view
(
CREATE VIEWfails; useCREATE TEMP VIEW), and temporary objects never shadow the names inside a permanent view. A view keeps only its SQL text, so a temporary table created later with the name of a permanent table that a temporary view reads takes that table's place in the view. DROP SCHEMA [IF EXISTS] s [CASCADE | RESTRICT]:RESTRICT(the default) refuses a schema that still has tables or views;CASCADEdrops its views, then its tables (indexes and rows), then the schema. Dependencies between tables and views aren't tracked, soCASCADE/RESTRICTonDROP TABLE,DROP VIEWandDROP COLUMNare accepted but don't drop or check views that read the object (such a view fails when queried, as it does after a plainDROP)TRUNCATE [TABLE] [ONLY] t [, …] [RESTART IDENTITY | CONTINUE IDENTITY] [CASCADE | RESTRICT]removes every row and keeps the table: its columns, defaults,CHECKconstraints and comments. Row ids (__rowid) continue unlessRESTART IDENTITYis given. That also restarts owned SERIAL and IDENTITY sequences, in a separate operation. There are no foreign keys, soCASCADEandRESTRICTdo the same:- New keys: the table moves to a new key prefix and index,
<schema>:<table>~N:andidx:<schema>:<table>~Nwith a new N (see "Key prefixes are never reused" in the architecture section), as if it were dropped and created again. ThenFT.DROPINDEX … DDon the old index deletes the old rows.information_schema.tables.key_prefixandindex_nameshow the new names. - All or none: the tables of one statement switch to their new names in one transaction, so either every table is emptied or none is. A table named twice is emptied once.
- Concurrent writes: there are no locks, so another connection may
still be writing to the table (in Postgres,
TRUNCATEwould wait for it). Such a statement fails as afterDROP TABLE("… truncated or dropped, while this statement was writing to it …") and deletes what it wrote, so the table is empty afterwards. That includesUPDATEandMERGE, whose changes after theTRUNCATEwould leave partial copies of the rows they set under the old prefix. ADELETE(orMERGE … DELETE) leaves nothing there: one that ran for 100 ms or more fails all the same (it can't tell aTRUNCATEfrom a re-keying rename, which copied the rows it hadn't deleted yet), and a faster one succeeds, as if it had run first. A small write that doesn't check may succeed, with its rows left under the old prefix, where no table reads them. Up to v0.0.7 the rows such a statement wrote after theTRUNCATEstayed in the table, andRESTART IDENTITYcould lose them (see "Key prefixes" in the architecture section). - Concurrent reads and
ALTERs: a statement that reads the table for 100 ms or more across aTRUNCATEfails ("try again"), as across aDROP TABLE. AnALTER TABLE … ADD COLUMNthat commits while aTRUNCATEruns is added to the new index too; one that had changed the old index and commits after it fails ("changed concurrently; try again") ALTER TABLE [IF EXISTS] twithRENAME TO u, withRENAME [COLUMN] a TO b, or with one or more of these, separated by commas:ADD [COLUMN] [IF NOT EXISTS] c TYPE [column_constraint …] [NOINDEX] [COMMENT 'text'],DROP [COLUMN] [IF EXISTS] c [CASCADE | RESTRICT],ALTER [COLUMN] c {SET DEFAULT expr | DROP DEFAULT | SET NOT NULL | DROP NOT NULL},ALTER [COLUMN] c [SET DATA] TYPE type [USING expr],ADD table_constraint,DROP CONSTRAINT [IF EXISTS] name [CASCADE | RESTRICT]. All of them only change metadata, so they take the same time at any table size, exceptRENAME TOwithadbc.redis.rename_rekeyandALTER COLUMN … TYPE, which rewrite every row, and adding aCHECK, aNOT NULLor aPRIMARY KEY, which reads every row first:- Several actions (dbt's
on_schema_changeand snapshots sendalter table t add column "a" …, add column "b" …, drop column "c") are one change of the table's metadata, made in oneWATCH/MULTI, so other statements see the table before all of them or after all of them. They run in Postgres's order, each seeing what the ones before it did: first the drops (DROP COLUMN,DROP CONSTRAINT,DROP DEFAULTandDROP NOT NULL), thenALTER COLUMN … TYPE(and theCHECKs that read the columns it changes, bound again), thenADD COLUMN, thenSET NOT NULL, then the primary keys (ADD PRIMARY KEYandADD COLUMN … PRIMARY KEY), thenSET DEFAULT, the new columns'CHECKs andADD CONSTRAINT … CHECK, each group in the order written. SoADD CONSTRAINT c CHECK (x > 0), ADD COLUMN x … DEFAULT 1checks the rows withx's default,DROP COLUMN a, ADD COLUMN a …gives a new, empty column (also written the other way round),ALTER COLUMN a SET DEFAULT 1, ADD COLUMN a …gives the new column a default,ADD PRIMARY KEY (b), ADD COLUMN b …makes the new column the key, andADD PRIMARY KEY (b), DROP CONSTRAINT t_pkeyreplaces the key. An action that runs earlier can't name what the statement adds:ADD COLUMN a …, DROP COLUMN aandADD COLUMN a … DEFAULT 9, ALTER COLUMN a DROP DEFAULTfail withcolumn "a" of relation "t" does not exist, as in Postgres. Unnamed constraints are named in that order too: inADD CHECK (v > 0), ADD COLUMN b … CHECK (v > 1), the column's ist_v_checkand the othert_v_check1, and inADD CHECK (b > 0), ADD CONSTRAINT t_b_check PRIMARY KEY (b)the key takest_b_checkand theCHECKist_b_check1. Of several errors, the one reported is the first in that order. A table can't be left without columns (Postgres allows it), butDROP COLUMN a, ADD COLUMN b …works on a table whose only column isa.- Upgrading from v0.0.12 and earlier: the actions ran in the order
written, so
ADD COLUMN a …, DROP COLUMN aadded nothing,ADD COLUMN a … DEFAULT 9, ALTER COLUMN a DROP DEFAULTleftawithout a default (for the rows inserted later), and aCHECKon a column added later in the list was an error.ADD COLUMN … NOT NULLwithout a non-NULLDEFAULTwas an error also on a table without rows. InADD COLUMN,DROP COLUMNandALTER COLUMN, a column that already exists or doesn't wascolumn "a" already exists in table "t"orcolumn "a" does not exist in table "t".
- Upgrading from v0.0.12 and earlier: the actions ran in the order
written, so
- All or nothing: if any action fails (a column that exists or
doesn't, a
DEFAULTthat doesn't convert, aCHECKthat some row fails, …), none is applied: the metadata, the search index and the rows stay as they were. Every definition is checked first, then the rows against the newCHECKs (one scan for all of them), then the new indexed columns are added to the index (oneFT.ALTER, which adds all of them or none), and then the metadata is written. No row is rewritten before that: the only row writes are the removal of dropped columns' fields afterwards, in the background, which is recorded in the metadata and resumed by the next connection if it stops part-way (the table is already correct then: the fields are never read or reused). If writing the metadata fails (a concurrent change, "try again", or a lost connection), the index keeps attributes that no column uses, which is harmless, and a laterADD COLUMNdoesn't reuse their names. A statement with anALTER COLUMN … TYPEthat converts values writes new rows instead, under new keys, and switches to them in one transaction once every row is converted and checked (see below), so it is all or nothing too. RENAME TOandRENAME COLUMNtake no list, as in Postgres:ALTER TABLE t RENAME TO u, ADD …issyntax error at or near ",", andALTER TABLE t ADD …, RENAME …issyntax error at or near "RENAME". On a view, onlyRENAME TOis accepted.RENAME TOkeeps the table's row keys and index (fixed when the table was created), so no row is touched. A table that dbt builds ast__dbt_tmpand renames totkeeps the keyspublic:t__dbt_tmp:<rowid>and the indexidx:public:t__dbt_tmp(ort__dbt_tmp~N: names are never reused, so every build after the first takes a new N).- With a concurrent
DROP TABLE: there are no locks, soDROP TABLE tandALTER TABLE t RENAME TO umay overlap. In Postgres one of them waits for the other: either theDROPdrops the table and the rename fails, or the rename comes first and theDROPfails. Here a rename that commits before theDROPreads the table makes theDROPfail (table "public"."t" does not exist), and aDROPthat commits before the rename makes the rename fail the same way. A rename that commits while theDROPruns is too late to save the table: theDROPdrops the index, which deletes the rows, and the renamed table keeps that index. So theDROPdrops the table under its new name,u, and both statements succeed. A table created astmeanwhile is another table, and stays. Up to v0.0.12 theDROPfailed ("does not exist"), did nothing underIF EXISTS, or dropped that newtinstead, anduwas left without its index or rows, its key prefix and index name still reserved. -
With the option
adbc.redis.rename_rekeyset totrue(on the database, e.g. in dbt'sdb_kwargs, or on the connection),RENAME TOalso moves the rows and the index to the new name's:public:t:<rowid>andidx:public:t, or~Nif a table has had those before. In dbt's swap the previous build'stgives them up on every build, so from the second build on the keys arepublic:t~N:, with a new N each time. This reads and writes every row, so it takes time proportional to the number of rows (and about 100 ms more), and the table takes twice its memory until the copy is done (if Redis runs out, the rename fails and is rolled back). Every rename does it, including dbt's renames to__dbt_backup. The rows are copied withDUMP/RESTORE, every field included, then the metadata is switched in one transaction and the old index is dropped withDD, which deletes the old rows. Views are still renamed in the metadata only. While the rows move:- Readers see the table, complete, under its old name until the switch and under the new one after it. A statement that read the table's metadata during the move and was still running at the switch, or one that ran for 100 ms or more while the rows moved away, fails ("try again") rather than returning rows that are gone or now belong to another table.
- Statements that would change the table are refused ("is being
renamed"):
INSERT,UPDATE,DELETE,MERGE,TRUNCATE, bulk ingest that appends, andALTER TABLE. AnALTER TABLE … ADD,DROPorRENAME COLUMNthat had already started is taken into account when the metadata is switched.DROP TABLE(and so bulk ingest inreplacemode) is not refused: if it reaches the table before the switch, the rename fails. If the switch comes first, theDROPfails ("does not exist"), and the renamed table keeps its rows, which were copied to its new keys: unlike a plain rename, this one isn't followed. - A write statement that read the table's metadata before the move started may still be running, so the copy waits 100 ms first. A write statement that took longer than that, from reading the metadata to its last write, checks afterwards whether the table was being moved (or was moved, truncated or dropped), and if so fails with "some of its changes may be lost or have gone to another table": like a write that fails part-way, some of them may have been applied. Faster statements don't pay for the check, unless they write more than 1,000 rows (see "Key prefixes" in the architecture section).
- If the renaming process exits, the table is unchanged under its old name and refuses changes until the move's 30-second lease has expired. Until then the refusal says to try again when the rename has finished, and that a rename whose connection has gone away is rolled back once its lease has expired. Then the move is rolled back (its copies, its index and the names it reserved are removed), before anything else uses the table:
- A connection that opens rolls it back before it is open, even one that is closed straight away, as dbt's metadata connections are. Connections that open while another one rolls it back wait for that (up to 30 seconds).
- A connection that was already open rolls it back at its next statement that would change the table, or its next re-keying rename, and that statement then goes on.
- If the rollback fails (for example, the user lacks
FT.DROPINDEX), the connection gives it up at once, and the statement fails with the reason and says that the next connection tries again.
If it had already switched the metadata, the rename stands, and the same connections remove the old rows instead. A renaming connection that can't renew its lease for 15 seconds stops before its next step and fails; another connection then rolls the move back. - If the renaming connection is closed (
AdbcConnectionRelease) while the rename runs on another thread, the rename stops as if cancelled. Close returns once it is rolled back, or finished if it had switched the metadata. No lease is left, so other connections change the table at once. The rename fails with "the connection was closed while the table was being renamed", or with CANCELLED if its statement was released first (Python'sclose()closes the cursors first). Close waits up to 15 seconds. A rename still running then gives its lease up, and the next connection rolls it back at once. Up to v0.0.12, Close left the lease in place, so the table refused changes for 30 seconds, and the process could crash. -RENAME COLUMNkeeps the column's HASH field; only its SQL name changes. -ADD COLUMNadds the column to the index withFT.ALTER; existing rows read it as NULL, or as itsDEFAULT, which is computed once (soDEFAULT CURRENT_TIMESTAMPgives them all the time of theALTER) and recorded as the column's missing value rather than written to them (see the architecture section). A volatile default (RANDOM()) is not supported here. WithNOT NULLand noDEFAULT(or a NULL one), the existing rows would read NULL, so as in Postgres it works only on a table without rows (otherwisecolumn "c" of relation "t" contains null values). -DROP COLUMNhides the column immediately and removes its field from existing rows in the background (resumed by the next connection if the process exits first). A dropped column's values never reappear, even if a column with the same name is added later. -ALTER COLUMN … SET DEFAULT/DROP DEFAULTonly change what rows inserted later get; existing rows keep their values. -ALTER COLUMN … SET NOT NULLchecks every row first, reading the column as queries do (one added with aDEFAULTreads it in the rows that existed then), and fails with Postgres'scolumn "c" of relation "t" contains null valuesif one is NULL. Each row is checked againstNOT NULLbefore the statement's newCHECKs, as in Postgres.DROP NOT NULLonly changes the metadata. Either does nothing to a column that already is so.information_schema.columns.is_nullable,GetObjectsandGetTableSchemashow the change.DROP NOT NULLon a column of the primary key fails with Postgres'scolumn "id" is in a primary key, unless the statement drops the key first (DROP CONSTRAINT t_pkey, ALTER COLUMN id DROP NOT NULL; both are drops, which run in the order written). - Concurrent writes: there are no locks, so other connections may write while the rows are checked for a newNOT NULLorCHECK(in Postgres they would wait). The rows they insert meanwhile are checked too, when the change commits (which tries again while more keep coming, up to 20 times, then fails with "try again"). A statement that read the table before a newNOT NULLand inserts after it fails on a NULL in the column, or with "try again" if the column was added without a default. Rows still being written when the change commits, rows updated meanwhile, and rows inserted after a newCHECKby a statement that read the table before it aren't checked. -ALTER COLUMN c [SET DATA] TYPE type [USING expr]converts the column's values, as in Postgres: - WithoutUSING, the column's value is converted as anINSERTconverts it, which Postgres allows for these types: a number to any number type,DATEtoTIMESTAMP(either kind), aTIMESTAMPtoDATE,TIMEor the otherTIMESTAMP,TIMEandINTERVALto each other,TIMEandTIME WITH TIME ZONEto each other, aTIMESTAMP WITH TIME ZONEtoTIME WITH TIME ZONE, and any type to a string type. Otherwise it fails with Postgres'scolumn "c" cannot be cast automatically to type integer; you might need to specify "USING c::integer". A string that is too long for aVARCHAR(n)orCHAR(n)is an error (value too long for type character varying(2)), unless the rest is spaces;USING c::varchar(2)cuts it. -USING exprconverts an expression over the row instead, which may read any of the table's columns and call functions (RANDOM()gives each row its own value), but not use subqueries, parameters, aggregates or window functions (cannot use subquery in transform expression, …). Its result is converted as above (result of USING clause for column "c" cannot be cast automatically to type integerotherwise; a quoted string literal orNULLconverts to any type, a cast of one doesn't). It reads the row as it was before the statement:ALTER COLUMN a TYPE text USING b::text, ALTER COLUMN b TYPE int USING a::intswaps the values, andDROP COLUMN b, ALTER COLUMN a TYPE int USING breadsb. - The rest of the column stays: its name,NOT NULL(a row that the conversion makes NULL fails withcolumn "c" of relation "t" contains null values), primary key, comment andDEFAULT. TheDEFAULTmust convert too (default for column "c" cannot be cast automatically to type integer; drop it first, in the same statement, and set a new one after), and is checked as a new one is. TheCHECKconstraints that read the column are bound again for the new type (one that no longer works fails the statement,argument of CHECK must be type boolean, not type integer), and every converted row must pass them. - Refused, as in Postgres: a column that a view reads (cannot alter type of a column used by a view or rule; rule _RETURN on view v depends on column "c"), even to the same type, and a column whose type changes twice in one statement (cannot alter type of column "c" twice). Dependencies aren't recorded, so the views of every schema, and the connection's temporary views, whose text names the table are planned to find the columns they read. Other connections' temporary views aren't checked.COLLATEisn't supported (strings compare by their bytes, as with Postgres's"C"collation). - Only the metadata changes when the values stay as they are: the same type withoutUSING, or aVARCHARthat gets longer or loses its length (TEXT). Postgres doesn't rewrite the table for these either. - Otherwise every row is rewritten, as in Postgres, and likeRENAME TOwithadbc.redis.rename_rekey(above), to new keys: the table takes new names (<schema>:<table>~N:and its index), and every row the old index lists is read (HGETALL), converted, checked againstNOT NULLand theCHECKs (also those the statement adds), and written to its new key (HSET), pipelined, a cursor page at a time. The new index has the column's new attribute (NUMERICfor numbers, dates and times,TAGfor strings, none forBYTEA,INTERVALandTIME WITH TIME ZONE), so indexed queries on the column read the new type. Before switching, the old and new indexes' row counts are compared. If background dropped-column cleanup changed the old index during a cursor scan, missing rows are rescanned (up to three scans); already copied rows aren't converted again. A persistent mismatch fails and rolls back rather than committing missing rows. One transaction then switches the metadata to the new names, with every action of the statement, and the old index is dropped with its rows (DD). The new rows hold only the table's columns: the fields of dropped columns aren't copied (and no cleanup is left), and a column added in the same statement with aDEFAULT, or an altered column that had a missing value, gets its value written into every row. This takes time proportional to the number of rows (and 100 ms more), and the table takes twice its memory until the old rows are dropped. - All or nothing: a value that doesn't convert, a row that fails a constraint, a cancel (AdbcStatementCancel, CANCELLED), the connection's close or a lost connection rolls the change back: the new index is dropped with its rows, and the table is as it was. - Concurrent statements are those of a re-keying rename: while the rows are rewritten, writes are refused ("is being altered: ALTER COLUMN … TYPE is rewriting its rows to new keys; try again when that has finished"), as are otherALTERs andTRUNCATE; readers see the table as it was until the switch; a statement that read the table before and writes, or reads for 100 ms or more, across the rewrite fails ("was renamed with its rows moved to new keys, rewritten by ALTER COLUMN … TYPE, truncated or dropped, while this statement was …"). AnALTER(orCOMMENT ON) that had already started and changes the table meanwhile makes the rewrite fail ("was changed while ALTER COLUMN … TYPE rewrote its rows; try again"), and so does aDROP TABLE. If the connection goes away, or can't renew its lease, the next connection to open, or the next statement that changes the table, rolls the rewrite back, as for a rename. It needs no commands beyond the basic ones (see ACL users). - Not supported: conversions that the driver'sCASTdoesn't make either, such asTIMEtoINTERVAL(cannot convert time value …), and tables adopted withredis-adbc adopt, whose HASHes are the application's. Values convert as the driver'sCASTconverts them, which differs from Postgres in a few places: aDOUBLE PRECISIONhalf rounds away from zero to an integer (Postgres rounds to even), andBYTEAkeeps its text as written. A conversion error has Postgres's message (invalid input syntax for type integer: "abc", see Error handling). As for the other defaults, aDEFAULTthat the new type can't hold is an error at theALTER(Postgres fails when a row gets it). - Upgrading from v0.0.12 and earlier:ALTER COLUMN … TYPEwasunsupported ALTER COLUMN action near "TYPE". The error of a statement whose table moved away while it was writing or reading now namesALTER COLUMN … TYPEamong the causes. -ADD COLUMN … CHECK (…)andADD [CONSTRAINT name] CHECK (…)check every existing row first (see Constraints below).ADD [CONSTRAINT name] PRIMARY KEY (cols)andADD COLUMN … PRIMARY KEYmake the key's columnsNOT NULL, checking every row first asSET NOT NULLdoes (see "Primary keys" under Constraints below).DROP CONSTRAINTremoves aCHECKor the primary key;UNIQUEandFOREIGN KEYconstraints aren't kept, so it can't name them. -COMMENT ON {TABLE | VIEW | COLUMN} name IS 'text' | NULL, as in Postgres (dbt'spersist_docsruns it). A column is namedt.col,schema.t.colorredis.schema.t.col, and names resolve as elsewhere (temporary objects first).NULLor''removes the comment. - Storage: the comment is part of the table's or view's metadata (changed withWATCH/MULTI), so it stays with the object throughRENAME TOandRENAME COLUMN, and is dropped with the object, or with its column byDROP COLUMN.TRUNCATEkeeps it. Withadbc.redis.rename_rekeythe comments move with the metadata, andCOMMENT ONisn't refused while the rows move.CREATE OR REPLACE VIEWkeeps the view's comment and those of the columns that keep their names, as Postgres does.CREATE TABLE … ASandCREATE VIEWcopy no comments from what they read. - Reading them back:information_schema.tables.commentandinformation_schema.columns.comment;GetObjects'remarksfor columns (theGetObjectsschema has no field for a table's comment); andGetTableSchema, as the field metadataARROW:FLIGHT:SQL:REMARKS(the key Flight SQL uses for column remarks) of each column that has one. Postgres'sobj_description/col_descriptionaren't supported: they take OIDs, which the driver doesn't have. - Errors are Postgres's:"v" is not a table(COMMENT ON TABLEon a view),"t" is not a view,relation "t" does not exist,column "c" of relation "t" does not exist.
Inserts, parameters, defaults, and constraints¶
INSERT INTO t [(cols)] VALUES (…), (…)with literals,?/$nparameters orDEFAULT,INSERT INTO t DEFAULT VALUES, andINSERT INTO t [(cols)] SELECT …(the query may be parenthesized:INSERT INTO t (SELECT …))- Parameters.
- Placeholders:
$1,$2, … and?. A?is numbered in order of appearance, separately from$n: inSELECT $2, ?the?is the first parameter. - Binding: with
BindorBindStream; the statement runs once per row. GetParameterSchemadescribes the parameters without running the statement: one unnamed, nullable field per parameter, in order, with the type the parameter is used as:- Compared with an expression (
=,<>,<, …,IS [NOT] DISTINCT FROM, and soIN (…)andBETWEEN): that expression's type. In$1 IN (SELECT …), the subquery's column type. - An
INSERTvalue, or anUPDATESETvalue: the target column's type. This also covers-$1and the select items ofINSERT … SELECT. CAST($1 AS t)or$1::t:t.- Arithmetic (
+,-,*,/,%): the type a quoted literal has there (see Quoted string literals), as in Postgres. With a number, that number's type;ts + $1is an interval,ts - $1a timestamp,d - $1a date andiv * $1a double; none ford + $1, which could be either, orb + $1. ||,LIKE,ILIKE,SIMILAR TOand the regex operators: text.AND,OR,NOT, andCASE WHENconditions: boolean.COALESCE,NULLIF,GREATESTandLEASTarguments, andCASEresults: the type of the others.
- Compared with an expression (
- Which use wins: the first use that gives a type.
- Null type: a parameter gets the null type when it is used nowhere a
type can be taken from (
SELECT $1), or only in a statement that needs what an earlier statement of the script makes.MERGE's parameters get it too. - Errors: a statement that can't be planned, such as one on an unknown table, is an error.
- Column defaults (
DEFAULT expr), as in Postgres: - A row gets a column's default when it is inserted without a value for
it: a column left out of the column list of
INSERT … VALUES,INSERT … SELECTor MERGE'sINSERT,DEFAULTin place of a value,INSERT … DEFAULT VALUES, and columns missing from the Arrow data of a bulk ingest. An explicit NULL stays NULL (and fails on aNOT NULLcolumn).UPDATE … SET col = DEFAULTis not supported. - The default is checked when it is defined: it may not read columns or
use subqueries, parameters, aggregates,
GROUPINGor window functions, its function calls are checked as in a query (DEFAULT nosuchfunc(NULL)isfunction nosuchfunc(unknown) does not exist), and it must convert to the column's type and fit its length (INTEGER DEFAULT 'abc'andVARCHAR(3) DEFAULT 'abcd'are errors then).DEFAULT NULLis the same as no default. - It is computed once per statement (or bulk ingest), so all the rows of
a statement get the same
CURRENT_TIMESTAMP/NOW()/CURRENT_DATE, the statement's time. A volatile default (RANDOM()) is computed for every row. information_schema.columns.column_defaultandGetObjects'xdbc_column_defshow the default as written, except that a local-time literal is stored as its instant (see Session time zone).CREATE TABLE … AScopies values, not defaults- String lengths, as in Postgres:
- Types:
VARCHAR(n)(alsoCHARACTER VARYING(n),CHAR VARYING(n)andNVARCHAR(n)) holds at mostncharacters.CHAR(n)(alsoCHARACTER(n),NCHAR(n)andBPCHAR(n)) holds exactlyn: shorter values are padded with spaces.CHARwithout a length isCHAR(1), andBPCHARwithout one is blank-padded with no limit.VARCHARwithout a length,VARCHAR(MAX),TEXT,STRINGandCLOBhave no limit. Lengths count characters, not bytes ('日本語'fitsVARCHAR(3)), and go from 1 to 10485760. - Writes: a value longer than its column's length is an error,
value too long for type character varying(3)(character(3)forCHAR(3)), with ADBC statusInvalidDataand SQLSTATE22001(see Error handling), unless the characters beyond the length are all spaces, which are cut ('ab 'is written to aVARCHAR(3)as'ab '). Every write checks it:INSERT … VALUES,INSERT … SELECT,INSERT … DEFAULT VALUES,UPDATE(also withFROM), both branches ofMERGE, and bulk ingest. It is checked whereNOT NULLandCHECKare, before them, so a statement with a failing row writes nothing (a bulk ingest is checked one Arrow batch at a time). A column'sDEFAULTis checked when it is defined. - Casts:
CAST(x AS VARCHAR(n))andx::char(n)cut the text toncharacters (CAST('abcdef' AS VARCHAR(3))is'abc'), andCHAR(n)pads it (CAST('ab' AS CHAR(3))is'ab '). Only an explicit cast cuts; writing a longer value is an error. CHARvalues are stored and returned padded, and their trailing spaces don't count:LENGTHof'ab 'in aCHAR(3)is 2, it equals'ab', and joins,IN,GROUP BY,DISTINCTand set operations treat the two as the same value. Converting one to text (functions,||, aVARCHARcolumn) drops the padding (c || 'x'is'abx'), butLIKE,ILIKE,~,~*andSIMILAR TOsee it (c LIKE 'ab'is false,c LIKE 'ab%'true), as in Postgres. One difference: a comparison with aCHARvalue ignores trailing spaces on both sides, as Postgres does for a literal (c = 'ab 'is true), so a text column holding'ab 'also equals aCHARholding'ab'; Postgres compares aCHARwith a text column as text, which keeps them.- Result types keep the length where Postgres does: a column, a cast,
a scalar subquery, and
CASE,COALESCE,NULLIF,GREATEST,LEASTor a set operation whose inputs all have the same type. Function, aggregate and window results have none, nor does a mix of lengths or a string literal (COALESCE(s, 'x')). SoCREATE TABLE … ASand views keep the length of a column or cast they select, and a computed column isVARCHAR. - Reading them back:
GetObjectsreports the type asVARCHAR(n)/CHAR(n)inxdbc_type_name, withxdbc_column_sizenandxdbc_char_octet_length4n(UTF-8);information_schema.columnshascharacter_maximum_lengthandcharacter_octet_length(NULL and 1073741824 for a string without a length, as in Postgres) and the same type indata_type. - Upgrading: v0.0.7 and earlier didn't keep the length, so the tables
they created have none and aren't checked; their values stay as they
are. Only tables created and columns added from this version on are
checked.
CHARwithout a length was unbounded, and is nowCHAR(1). Views,CHECKs and defaults that cast toVARCHAR(n)/CHAR(n)now cut. Clients on v0.0.7 and earlier ignore the length (an optional part of the column type in the metadata), so they don't check it, and a change they make to a table's metadata (anALTER TABLE, aCOMMENT ON, and some writes) removes it, so upgrade every client of a database together. - Constraints, written as in Postgres.
NOT NULL,CHECKand the lengths ofVARCHAR(n)/CHAR(n)(see String lengths above) are enforced.PRIMARY KEYis kept and makes its columnsNOT NULL, but its uniqueness isn't enforced.UNIQUEandFOREIGN KEYare not enforced: - Accepted: on a column (in
CREATE TABLEandADD COLUMN), any number ofNOT NULL,NULL,CHECK (expr),DEFAULT expr,PRIMARY KEY,UNIQUEandREFERENCES t [(col)] [MATCH {FULL | PARTIAL | SIMPLE}] [ON DELETE action] [ON UPDATE action], in any order, each optionally named withCONSTRAINT name. As a table constraint (inCREATE TABLEandALTER TABLE … ADD),[CONSTRAINT name]followed byCHECK (expr),PRIMARY KEY (cols),UNIQUE (cols)orFOREIGN KEY (cols) REFERENCES t [(cols)] …. A columnCHECKmeans the same as a table one: a condition on the whole row, which may read any column. AfterPRIMARY KEY (cols), Postgres's index parameters (INCLUDE (cols),WITH (…),USING INDEX TABLESPACE name) andDEFERRABLE/INITIALLY DEFERREDare accepted and mean nothing. - Not enforced:
UNIQUEandFOREIGN KEY/REFERENCESare accepted and ignored. Nothing is kept, so duplicate values and rows without a parent are inserted, the referenced table needn't exist, andGetObjectsdoesn't list them. Nor is the uniqueness of aPRIMARY KEYenforced (see "Primary keys" below). CHECK, when written: the expression is checked as in Postgres. It may read the table's columns (col,t.colorschema.t.col, see "Names" below) but not__rowidor another table, may not use subqueries, parameters, aggregates,GROUPINGor window functions, and must be boolean. Its function calls are checked as in a query (see "Function calls" below). The errors arecolumn "x" does not exist in table "t",system column "__rowid" reference in check constraint is invalid,missing FROM-clause entry for table "u",cannot use subquery in check constraint,cannot use parameter in check constraint,aggregate functions are not allowed in check constraints,grouping operations are not allowed in check constraints,window functions are not allowed in check constraints,function nosuchfunc(integer) does not exist,UPPER expects 1 argumentandargument of CHECK must be type boolean, not type integer. A value the expression can't compute for a row (LOG(0)) is an error when that row is written.- Names:
CONSTRAINT nameis used as written. Without one, the name is Postgres's:<table>_<column>_checkif the expression reads exactly one column (t_amount_check, also for a table constraint such asCHECK (id > 0)), otherwise<table>_check, with1,2, … appended to a name the table already uses (t_amount_check1). Two constraints of a table can't have the same name (check constraint "c" already exists;ADD:constraint "c" for relation "t" already exists). - Enforcement: every write checks each new or changed row:
INSERT … VALUES,INSERT … SELECT,INSERT … DEFAULT VALUES,UPDATE(also withFROM), both branches ofMERGE, and bulk ingest. As in SQL, a row fails only if the expression is FALSE; NULL passes. The row is checked after its defaults are filled in and afterNOT NULL, with the errornew row for relation "t" violates check constraint "t_amount_check". LikeNOT NULL, this happens while the statement computes its changes, so a statement with a failing row writes nothing; a bulk ingest is checked one Arrow batch at a time, so the batches before a failing one stay written. As in Postgres, the constraints are checked in name order, so a row that fails several reports the first, and anUPDATEchecks every constraint, also those that don't read the columns it sets (it reads the columns they use). - Existing rows:
ADD COLUMN … CHECKandADD [CONSTRAINT name] CHECKcheck every row first, with the new column at its default, and fail withcheck constraint "c" of relation "t" is violated by some row. Rows that other connections insert while that runs are checked when the change commits, but not all the rows they write (see "Concurrent writes" underALTER TABLE). - Lifetime: the constraints are part of the table's metadata (an
optional
checksfield holding each name and expression, so older metadata reads unchanged).RENAME TO(also withadbc.redis.rename_rekey) andTRUNCATEkeep them, with their names; errors name the table as it is now.RENAME COLUMNrewrites the expressions that read the column.DROP COLUMNdrops the constraints that read it, also those that read other columns too, andDROP CONSTRAINT [IF EXISTS] namedrops one. Temporary tables have them in the same way;CREATE TABLE … AScopies none. - Primary keys, as in Postgres 16:
- Definition: a table has at most one (
multiple primary keys for table "t" are not allowed), of its own columns (inALTER TABLE, also those the statement adds), each named once:column "x" named in key does not exist(ALTER TABLE:column "x" of relation "t" does not exist) andcolumn "a" appears twice in primary key constraint. NOT NULL: the key's columns becomeNOT NULLas if declared so (also a column declaredNULL), so every write checks them, with Postgres'snull value in column "id" of relation "t" violates not-null constraint.information_schema.columns.is_nullable,GetObjectsandGetTableSchemashow it.DROP NOT NULLon a key column fails (column "id" is in a primary key), and the columns stayNOT NULLwhen the key is dropped.- Names:
CONSTRAINT nameas written, otherwise<table>_pkey, with1,2, … appended to a name one of the table'sCHECKs uses (t_pkey1). ACHECKadded later avoids the key's name in the same way. InCREATE TABLEtheCHECKs are named first, so a key named like one of them fails withconstraint "c" for relation "t" already exists, as in Postgres. Postgres names the key's index the same, so it also avoids the names of the schema's other tables, indexes and constraints: where another table's key ist_pkeyalready (it keeps that name when its table is renamed), Postgres names a new tablet's keyt_pkey1and the drivert_pkey. - Uniqueness isn't enforced: rows with the same key are inserted,
and
UPDATEcan give two rows the same key. Enforcing it needs a check that is atomic with the write against every other writer, and the rows are separate HASHes (on different shards of a cluster), written without a transaction across them: looking the key up in the search index first would let two statements each find no row and both insert one. Soinformation_schema.table_constraints.enforcedisNOfor a primary key, and the dbt adapter declaresprimary_keynot_enforced(dbt warns about it in a contract, as for Snowflake or BigQuery). - Existing rows:
ADD PRIMARY KEYandADD COLUMN … PRIMARY KEYcheck every row for NULLs in the key's columns asSET NOT NULLdoes, with the same error (column "id" of relation "t" contains null values) and the same treatment of concurrent writes (see "Concurrent writes" underALTER TABLE). Duplicate keys aren't checked (Postgres'scould not create unique index "t_pkey"). - Lifetime: the key is part of the table's metadata (an optional
primary_keyfield: its name and the HASH fields of its columns, in key order).RENAME COLUMNkeeps it on the renamed column.RENAME TO(also withadbc.redis.rename_rekey) andTRUNCATEkeep it with its name, as Postgres keeps its index's.DROP COLUMNof one of its columns drops it, asDROP CONSTRAINT namedoes. Temporary tables have one in the same way;CREATE TABLE … ASand a bulk ingest that creates its table don't.
- Definition: a table has at most one (
- Reading them back:
GetObjects'table_constraintslists the primary key, with typePRIMARY KEYand its columns in key order, and eachCHECK, with typeCHECKand the columns its expression reads.information_schema.table_constraintslists both, with Postgres's columns (is_deferrableandinitially_deferredareNO;enforcedisYESfor aCHECKandNOfor a primary key), andinformation_schema.key_column_usagethe key's columns, with theirordinal_positionin the key. Postgres 16 also lists eachNOT NULLintable_constraints, as aCHECKnamed with object ids (2200_16384_1_not_null); the driver doesn't (seeinformation_schema.columns.is_nullable). - Upgrading: clients on v0.0.6 and earlier ignore the new metadata.
They don't check the constraints, and a change they make to a table's
metadata (an
ALTER TABLE, aCOMMENT ON, and some writes) removes them, so upgrade every client of a database together. Earlier versions accepted table-levelCHECKs without storing them, so tables they created don't enforce theirs; add them again withALTER TABLE … ADD CHECK (…). Earlier versions also accepted a constraint that calls an unknown function; every write to its table now fails withcheck constraint "c" of relation "t" can't be evaluated (function nosuchfunc(integer) does not exist); drop it with ALTER TABLE … DROP CONSTRAINT. - Upgrading from v0.0.12 and earlier:
PRIMARY KEYwas accepted and not kept, so the tables those versions created have no key, and its columns are nullable unless they were declaredNOT NULL; add it withALTER TABLE … ADD PRIMARY KEY (…). A table can no longer have twoPRIMARY KEYclauses. Clients on those versions ignore theprimary_keyfield (they still check theNOT NULLof its columns, which is part of each column), and a change they make to a table's metadata removes the key. A NULL in aNOT NULLcolumn wasNULL value in column "a" violates not-null constraint; it is now Postgres'snull value in column "a" of relation "t" violates not-null constraint.
Queries and relational features¶
[WITH [RECURSIVE] name [(cols)] AS (SELECT …), …] SELECT [ALL | DISTINCT | DISTINCT ON (…)] … FROM item {, item | [INNER | LEFT | RIGHT | FULL] [OUTER] JOIN item ON … | USING (…) | CROSS JOIN item | NATURAL [INNER | LEFT | RIGHT | FULL] [OUTER] JOIN item} [WHERE …] [GROUP BY …] [HAVING …] [WINDOW w AS (…), …] [QUALIFY …] [ORDER BY …] [LIMIT n] [OFFSET m [ROW | ROWS]] [FETCH {FIRST | NEXT} [n] {ROW | ROWS} ONLY], where an item is a table, a CTE,[LATERAL] (SELECT …)or[LATERAL] GENERATE_SERIES(…), each with an optional alias and column aliases (AS a(x, y);t.colqualifies a column, andt.*selects one item's columns, also asschema.table.colandschema.table.*; see "Names" below), with aggregates (below),CASE(simple and searched), arithmetic,CAST(x AS type)/x::type(andTRY_CAST, see below),IS [NOT] NULL,IS [NOT] DISTINCT FROM(NULLs count as equal), row constructors ((a, b) = (1, 'x'),(a, b) IN (SELECT …),SELECT (a, b); see below),[NOT] LIKE/ILIKE(withESCAPE), subqueries (scalar(SELECT …),EXISTS,[NOT] IN (SELECT …),x op ANY | SOME | ALL (SELECT …), also with a row on the left, correlated or not, in SELECT/WHERE/HAVING and inUPDATE/DELETE/MERGE),BETWEEN,IN,COALESCE,LOWER/UPPER/LENGTH/ABS,CONCAT(a, …)andCONCAT_WS(sep, a, …)(NULL arguments are skipped, as in Postgres;||returns NULL if either side is NULL),from_hex- Names resolve as in Postgres, at every query level: subqueries
(
EXISTS,IN,ANY/ALL, scalar),LATERAL, derived tables, CTEs, views, set operations, and the subqueries ofUPDATE,DELETE,UPDATE … FROM,DELETE … USINGandMERGE: - A FROM item with an alias is visible only by its alias. In
select id from t where exists (select 1 from t x where x.v > t.v),t.vis the outer row's, since the innertis visible only asx. Up to v0.0.7, a level with a single FROM item also knew it by its table's name, sot.vwas the inner row's and the query compared each row with itself (#45) - A FROM item without an alias is visible by its name. A table or
view without an alias is also visible as
schema.table.col(orredis.schema.table.col), with its own schema only (pg_tempfor a temporary table): inselect s2.t.v from s1.t,s2.tnames no FROM item. Up to v0.0.8 the schema wasn't checked, so that query reads1.t'sv(#50). CTEs, derived tables and items with an alias aren't visible schema-qualified - Two tables or views with the same name from different schemas can
be FROM items together without aliases, as in Postgres:
select s1.t.v, s2.t.v from s1.t join s2.t on s1.t.id = s2.t.id, in any join,USINGandNATURALincluded, and inUPDATE … FROMandDELETE … USING. Up to v0.0.8 that wastable name "t" specified more than once; use aliases. Any other two items of a level with one name still are: the same table twice, or an item and an alias, CTE or derived table of its name. AMERGEsource can't have the target's name even then:name "t" specified more than once; the name is used both as MERGE target table and data source - A derived table or CTE reads the queries around it, as in
Postgres: a derived table the columns of the queries around the one
whose FROM list holds it (not that query's other FROM items, which
need
LATERAL), and a CTE those around the query whose WITH list holds it, wherever it is read (not that query's FROM items, nor the queries between).select * from t where exists (select * from u, (select t.v) s)readst.vof each row oft, and so doesselect (with c as (select t.v) select (select v from c)) from t; that holds for recursive CTEs too. Up to v0.0.12 they couldn't read an enclosing query (missing FROM-clause entry for table "t"), and a CTE's body saw the WITH lists of the queries that read it: inwith c as (select * from d) select * from (with d as (select 1) select * from c) s,dwas the inner CTE (Postgres: no such table) t.colis a column of the innermost level with an item visible ast. If that item has no columncol, it is an error, even when an outerthas one; if two items of that level are visible ast(s1.tands2.t), it istable reference "t" is ambiguous. An unqualifiedcolis a column of the innermost level that has one, andcolumn reference "col" is ambiguousif two of that level's items have it- Errors are Postgres's:
missing FROM-clause entry for table "t"when no level has an item visible ast, or that reads the tabletinvalid reference to FROM-clause entry for table "t"; perhaps you meant to reference the table alias "x"(Postgres's hint) when a level readstasx, as inselect t.id from t x,select s.t.id from s.t xorupdate t x set … where t.id = 1. For an unqualifiedt, Postgres gives it only whentis on the search path, the driver whenever an item's table, view or CTE is namedtinvalid reference to FROM-clause entry for table "t"; there is an entry for table "t", but it cannot be referenced from this part of the query(Postgres's detail) when a level has an item namedtthat the reference can't name:select s2.t.v from s1.t,select s.t.v from s.t t, or a CTEtread ass.t.vcross-database references are not implemented: other.s.t.colfor a catalog other thanredis, andimproper qualified name (too many dotted names): a.b.c.d.efor more than four names (also for*)- The same error with Postgres's hint,
…; to reference that table, you must mark this subquery with LATERAL, when a derived table refers to an earlier FROM item withoutLATERAL:select * from t, (select t.v) s, also from deeper in the derived table. The detail names the item,xfortread asx. An unqualified column of such an item iscolumn "v" does not exist; there is a column named "v" in table "t", but it cannot be referenced from this part of the query; to reference that column, you must mark this subquery with LATERAL, or, if several items have it,…; there are columns named "v", but they are in tables that cannot be referenced from this part of the query; try using a table-qualified name. As in Postgres, there is no hint whereLATERALcan't help: in the arguments of a FROM function (which areLATERALalready), and on the right of aRIGHTorFULLjoin. Up to v0.0.12 these weremissing FROM-clause entry for table "t"andcolumn "v" does not exist - The same error without a hint when a FROM item of
UPDATE t … FROM,DELETE FROM t USINGor aMERGE INTO tsource reads the target, whichLATERALcan't make visible:update t set v = 0 from u, (select t.v) s. An unqualified column of the target iscolumn "v" does not exist; there is a column named "v" in table "t", but it cannot be referenced from this part of the query. As in Postgres, aLATERALitem or a FROM function ofUPDATEorDELETEthat reads the target isinvalid reference to FROM-clause entry for table "t"; there is an entry …even for an unqualified column that an earlier item has too. Up to v0.0.12 these weremissing FROM-clause entry for table "t", andupdate t set v = 0 from u, lateral (select v) sreadu.v - The same errors for a join's
ONclause among the FROM items ofUPDATE … FROMandDELETE … USING, which, as in Postgres, reads only that join's two sides, not the target:update t set v = 0 from u join w on w.id = t.id, also from a subquery in it.WHEREreads the target, and so doesMERGE'sON, which joins it. Up to v0.0.12 such anONread the target - The same errors for a join's
ONclause that reads a FROM item outside its join, in any query: as in Postgres, it reads only the join's two sides, the items from the last comma up to it (see "Commas" below).select * from a, b join c on c.x = a.xisinvalid reference to FROM-clause entry for table "a"; there is an entry …(from a cross join b join c on c.x = a.xworks), an unqualified column that onlyahas iscolumn "y" does not exist; there is a column named "y" in table "a", …, andselect * from a join b on b.x = c.x join c on trueismissing FROM-clause entry for table "c", also from a subquery in theON. Up to v0.0.12 anONread every FROM item of its query
- Commas separate FROM-list items, as in Postgres:
a, b join c on …isaandb join c on …, whilea cross join b join c on …is(a cross join b) join c on …. So a join after a comma has only the items since the comma on its left: itsONcan't reada(above),USINGandNATURAL JOINmatch the columns ofb, nota, and aRIGHTorFULLjoin keeps every row ofa:select * from a, b right join d on d.x = b.xisacross joined withb right join d, so a row ofdthat matches nobcomes with each row ofa.SELECT *lists each FROM-list item's columns in turn. Up to v0.0.12 a comma wasCROSS JOIN: that query gave NULL forain such a row, andd's rows whenahad none, andfrom a, e natural join cjoinedawithc(or failed withcommon column name "x" appears more than once in left table). TheUSINGerrors are now Postgres's too:column "x" specified in USING clause does not exist in left table(and… right table),common column name "x" appears more than once in left tableandcolumn name "x" appears more than once in USING clause(up to v0.0.12:USING column "x" is ambiguous,USING column "x" does not exist in the left side of the join, andUSING (x, x)was accepted) - Column names are Postgres's. A select-list item is named by its
alias, or else by its expression (Postgres's FigureColname): a column by
the column,
CAST(x AS t)andx::tbyx(by the type ifxhas no name:int4,varchar,date), a call by the function (count,upper; alsoextract,btrim,timezoneforEXTRACT,TRIMandAT TIME ZONE), a scalar subquery by its column,EXISTSbyexists,CASEby itsELSE(orcase), a row byrow, a field(r).f1by the field, and an operator or a literal is?column?.SELECT count(*), upper(v), CAST(iv AS VARCHAR), v || 'x', 1 FROM thas the columnscount,upper,iv,?column?and?column?. These name the result's columns (the Arrow fields, also fromExecuteSchema),RETURNING's, and those of derived tables, CTEs, views andCREATE TABLE … AS, soSELECT count FROM (SELECT count(*) FROM t) sandSELECT * FROM (SELECT CAST(iv AS VARCHAR) FROM t) s ORDER BY ivwork as in Postgres - Names that repeat (
SELECT 1, 2,SELECT count(*), count(v),SELECT a, a,AS s(x, x)): a result, a derived table or a CTE keeps them all, as in Postgres, and only reading such a column by its name is an error:column reference "count" is ambiguous(also ass.count),ORDER BY "count" is ambiguous, and inUSINGorNATURAL JOINcommon column name "count" appears more than once in left table(orright table). A position (ORDER BY 2),*or column aliases (AS s(a, b)) read them. A table or view can't have them:CREATE TABLE … ASandCREATE VIEWarecolumn "count" specified more than once, as in Postgres; give the columns aliases. Column aliases on a table (FROM t AS x(a, a)) still can't repeat a name, which Postgres allows until one is read - Names are compared without case, as elsewhere in the driver: in
SELECT a FROM (SELECT 1 AS "A", 2 AS a) s,ais ambiguous, where Postgres reads the second column - Upgrading from v0.0.12 and earlier: an item without an alias that
wasn't a column was named by its text,
count(*),CAST(iv AS VARCHAR),v || 'x',1, so result field names change: code that reads columns by name, unaliased expressions included, needs the new names, or aliases. Tables thatCREATE TABLE … ASmade and views keep the names they were made with; made again, they get the new names, and a query whose items now share a name (SELECT count(*), count(v),SELECT 1, 2) needs aliases. A view whose query reads a derived table's or CTE's column by its old name (SELECT "count(*)" FROM (SELECT count(*) FROM t) s) is nowview "v" is no longer valid: column "count(*)" does not exist; replace it withCREATE OR REPLACE VIEW. Derived tables and CTEs whose columns shared a name werecolumn name "a" appears more than once in "s"; add aliases, a view'scolumn name "a" appears more than once in view "v"; add aliases, and a table'sduplicate column name "a". Theambiguouserrors had no SQLSTATE; they are42702now LIMIT,OFFSETandFETCHaccept expressions and bound parameters, including casts (LIMIT ?::INTEGER OFFSET ?). Counts are coerced to BIGINT once per execution before scan planning.LIMIT ALLandLIMIT NULLmean no limit;OFFSET NULLmeans zero. Negative counts report2201W/2201X. Counts cannot reference query columns, aggregate calls or window functions.VALUES (1, 'a'), (2, 'b')is a query term, also supported in FROM, CTEs, IN subqueries and set operations. Its columns arecolumn1,column2, ...; aliases can rename them. Each column resolves one common type across all rows; unequal row widths and incompatible type categories are errors.- A SELECT without FROM reads one empty row before WHERE. Aggregates,
GROUP BY and HAVING therefore work:
SELECT COUNT(*)returns 1 andSELECT COUNT(*) WHERE falsereturns 0. DATE(x)uses the same conversion and errors asCAST(x AS DATE), including the session time zone when converting TIMESTAMP WITH TIME ZONE.FETCH FIRST n ROWS ONLYisLIMIT n(FETCH FIRST ROW ONLYisLIMIT 1);WITH TIESis not supported, and a query has at most oneLIMITorFETCH(Postgres's "multiple LIMIT clauses not allowed")- A query that can't return rows (
WHERE false,LIMIT 0, …; see "How SQL is executed") reads none. It is still planned, so unknown tables, columns and functions and wrong argument counts are errors. Errors the driver raises only when it computes a value on a row (division by zero, a failed cast, the logarithm of zero) are then not raised, as on an empty table:SELECT 1/0 FROM t WHERE falsereturns no rows GROUP BYitems are expressions, output positions or aliases, and also (as in Postgres)ROLLUP (…),CUBE (…),GROUPING SETS (…)and():- A bare name is an input column's before it is an output column's
(unlike in
ORDER BY), as in Postgres. A name no input column has is the output column of that name, also one without an alias, named by its expression (see "Column names" above): afterSELECT upper(v),GROUP BY uppergroups byupper(v); afterSELECT CAST(x AS INTEGER),GROUP BY xis still the columnx. Only the query's own FROM items count, not those of the queries around it; a name two of them have (kofJOIN … USING (k), which merges the shared column) may also be an output column's. Output columns of the same name must be the same expression, otherwise it isGROUP BY "a" is ambiguous, and the one named may not be an aggregate or a window function. Unlike in Postgres, an alias may also be used inside an expression (GROUP BY lower(u)afterupper(v) AS u); a name without an alias may not - Upgrading from v0.0.12 and earlier: a bare name matched only
aliases, so
GROUP BY upperafterSELECT upper(v)wascolumn "upper" does not exist, or in a subquery read an enclosing query's columnupper. Of two aliases of the name the last one was used, where it is now ambiguous. In a join an alias came before an input column of its name, andGROUP BY kafterSELECT t.k … JOIN … USING (k)wascolumn reference "k" is ambiguous ROLLUP (a, b)is the grouping sets(a, b), (a), (), andCUBE (a, b)is every subset of its items (at most 12).GROUPING SETS (…)lists sets, and may containROLLUP,CUBEandGROUPING SETS. A parenthesized list is one item of several expressions (ROLLUP (a, (b, c))), and()is the empty set: one group of all the rows, even when there are none- Several items combine as a cross product:
GROUP BY a, ROLLUP (b, c)is(a, b, c), (a, b), (a). At most 4,096 sets; repeated sets are kept - A set's rows have NULL for the grouping columns it doesn't group.
GROUPING(a, …)tells those from NULL values: anINTEGERwith a bit per argument, the last one lowest, set when the argument is not grouped. It works in the SELECT list, HAVING, ORDER BY, QUALIFY and window definitions (and is 0 in a plainGROUP BY) - As in Postgres, outside aggregate calls such a query may use only
grouping expressions, matched as a whole (
GROUP BY ROLLUP (UPPER(s))can selectUPPER(s)but nots); this also applies to a plainGROUP BYthat callsGROUPING() - Values that compare equal are one group, as in Postgres:
INTERVAL '1 mon'andINTERVAL '30 days',INTERVAL '1 day'andINTERVAL '24 hours', and aDOUBLE PRECISION-0(-xof a zero) and0. The group shows the value of its first row read, which on a cluster can be either. So doSELECT DISTINCT(which runs asGROUP BY), grouping sets andDISTINCTin aggregates, as window partitions,DISTINCT ON, set operations and joins already did - A
CASEorCOALESCEof a number and a string literal gives numbers, as in Postgres (COALESCE(n, '0')of an integernis an integer, and its'0'the integer 0; see "Quoted string literals"), so a0ofnand the'0'are one group, also forDISTINCTand window partitions - Upgrading from v0.0.12 and earlier:
GROUP BY,SELECT DISTINCTover plain expressions andDISTINCTin aggregates told such values apart by their text, so1 monand30 dayswere two groups, andSUM(DISTINCT iv)added both.DISTINCTin aggregates and window partitions also told the0from the'0'. And two rows of several values could be taken for one when they held NUL bytes (BYTEAvalues, or text that was bound or ingested), inGROUP BY,SELECT DISTINCT, set operations and window partitions - Unlike in Postgres,
GROUP BY,HAVINGandQUALIFYexpressions may use the select list's aliases (HAVING n > 1aftercount(*) AS n). A bare name is an alias's only if no FROM item of the query has a column of that name, so an input column comes first, also in a join. An enclosing query's column comes after the alias, and a name two FROM items have (kofJOIN … USING (k)) may be an alias's too. A qualified name (t.x) is never an alias's - Upgrading from v0.0.12 and earlier: in a join an alias came before
an input column of its name in these expressions too, even a qualified
one: after
SELECT CAST(x AS INTEGER) AS x … FROM t JOIN u …,HAVING x > 1.3,HAVING t.x > 1.3andGROUP BY t.xread the alias. And a query planned again (a statement executed again or with parameters, or a subquery in aGROUP BYitem named by position or alias, as inGROUP BY 1, 2) resolved itsGROUP BYagain from the first plan's result: afterSELECT id AS w, max(v) AS id … GROUP BY win a subquery, whereidis an enclosing query's column,whad becomeid, which then named the aliasid:aggregate functions are not allowed in GROUP BY - Subqueries of a grouped query (
GROUP BY, or aggregates orHAVINGwithout it) may read only its grouped columns, as in Postgres. That applies where the query computes a value per group: the SELECT list,HAVING,ORDER BY,QUALIFY,DISTINCT ONand window definitions, outside aggregate calls, also in nested subqueries. A grouped column is aGROUP BYitem by itself, sogroup by vlets a subquery readg.v, butgroup by id + 1doesn't let it readg.id. - The error is Postgres's
subquery uses ungrouped column "g.id" from outer query, for example inselect v from g group by v having exists (select 1 from g x where x.id > g.id). Up to v0.0.8 the subquery read one row of each group (#51). - Aggregates of the outer query: as in Postgres, an aggregate belongs
to the innermost query whose columns its arguments,
ORDER BYandFILTERread.max(g.id)in(select max(g.id) from x where x.id = 1)is the outer query's: it is computed over each of its groups, and is a constant in the subquery (also in itsWHERE). It makes a query withoutGROUP BYan aggregate one, which returns one row.sum(g.v + x.id)reads the subquery's columns too, so it is the subquery's. Up to v0.0.9 such aggregates were computed over the subquery's rows, or rejected as not supported (#55). - Where an aggregate may be is decided at its own level, also for one
in a subquery:
select id from g where (select max(g.id)) > 1gives Postgres'saggregate functions are not allowed in WHERE. These errors (inWHERE,GROUP BY,JOINconditions,UPDATE,VALUES,RETURNING,FILTER, MERGEWHENconditions, …) have Postgres's text; up to v0.0.9 some saidaggregates are not allowed in …. - Not supported: such an aggregate with a subquery in its arguments,
as in
(select max((select g.id)) from x). It isouter-level aggregate with a subquery in its arguments is not supported. - Repeated subqueries: a subquery in
GROUP BYcounts as grouped where it is the same item, by position or alias (group by 1), not where it is written again (Postgres accepts both). - A column outside an aggregate must be grouped (
42803), including inHAVINGandORDER BY. Grouping by a base table's entire primary key permits its other columns. UseANY_VALUE(column)when any value in the group is intended. UNION [ALL],INTERSECT [ALL],EXCEPT [ALL](INTERSECTbinds tighter; parenthesized branches may have their ownORDER BY/LIMIT). Columns are matched by position and widened to a common type (a column of quoted literals takes the other branch's type, see "Quoted string literals" below); NULLs count as equal when removing duplicates.ORDER BYon the combined result uses output column names (the first branch's, named as in Postgres; seeORDER BYbelow) or positions. Set operations work anywhere a query does (subqueries, CTEs, views, CTAS,INSERT … SELECT)WITH RECURSIVE name [(cols)] AS (non-recursive term UNION [ALL] recursive term), evaluated as in Postgres: the recursive term reads the rows the previous iteration added until it adds none, andUNIONalso drops rows equal to any earlier row (NULLs count as equal). The columns are named by the CTE's column list or the non-recursive term, and have the non-recursive term's types, widened to hold the recursive term's values of the same category (otherwise it's Postgres's "has type … in non-recursive term but type … overall" error; cast the non-recursive term). Postgres's rules apply, with its errors: the recursive term refers to the CTE once, not in a subquery, on the NULL-supplying side of an outer join, underEXCEPTorINTERSECT ALL, or at a level with aggregates; noORDER BY/LIMIT/OFFSETon theUNION; no mutual recursion between CTEs. The CTE normally computes in full before it is read. A sole direct projection with finiteLIMIT/OFFSETcan stop a single-row progression early: its seed produces at most one row and each recursive iteration produces at most one row. Sorts, filters, aggregates/windows, joins, shared references, SEARCH/CYCLE and wider frontiers retain full evaluation. The bounded result never enters the shared CTE cache. More than 10,000 iterations that add rows, or more than 1,000,000 rows, remains an error. See scope and evidence. A CTE of aWITH RECURSIVElist that doesn't refer to itself is an ordinary CTESEARCH {BREADTH | DEPTH} FIRST BY col, … SET seqadds aBIGINTcolumn that orders the rows breadth or depth first (Postgres has a record or an array that sorts the same way);CYCLE col, … SET mark [TO v DEFAULT d] USING pathmarks (and doesn't recurse into) a row whose columns repeat a row on its path, and adds the path as text in Postgres's format ({(1),(2)}). As in Postgres, the recursive term must then be a SELECT that reads the CTE at its top level; here it also can't useGROUP BY,HAVING, window functions,LIMITorOFFSETLATERAL (SELECT …)in FROM (, LATERAL,CROSS JOIN LATERAL,[LEFT] JOIN LATERAL … ON …) reads the columns of the items before it; a table function reads them with or withoutLATERAL, as in Postgres. It runs once per distinct value of the columns it reads.RIGHTandFULLjoins can't have such a reference to their left side (Postgres's error), but are fine for aLATERALitem that reads none. Postgres also runs aLATERALitem that reads the FROM-list items before the comma of aRIGHTorFULLjoin's FROM-list item (see "Commas"), as infrom a, b right join lateral (select a.x) s on true; the driver doesn't (a LATERAL reference to "a" from a later FROM-list item with a RIGHT or FULL JOIN is not implemented)NATURAL [INNER | LEFT | RIGHT | FULL] JOINjoins on the columns both sides have (a cross join if none). As in Postgres,SELECT *lists each common column once, first, then the left side's other columns and the right side's; the merged column is the left one for inner and left joins, the right one for right joins andCOALESCEof both for full joins, and an unqualified reference means the merged column (a.idstill readsa's).JOIN … USING (…)follows the same rules, with merged columns first in the order of the USING listGENERATE_SERIES(start, stop [, step])in FROM, with Postgres's overloads: integers (step 1 by default) and numerics, and timestamps with an interval step (DATEarguments giveTIMESTAMP WITH TIME ZONE, as in Postgres; cast with::date; aTIMESTAMP WITH TIME ZONEseries adds its step's months and days in the session time zone). The column is namedgenerate_series, or after the alias (AS g→g,AS g(n)→n). A zero step is an error, a step in the wrong direction or a NULL argument gives no rows, and month steps clamp (Jan 31, Feb 29, Mar 29, …). Parameters work, and so do joins (generate_series(…) d LEFT JOIN t ON t.day = d::datefor a date spine) and arguments read from earlier FROM items. At most 1,000,000 rows per callx op ANY | SOME | ALL (SELECT …)for= <> < <= > >=, with SQL's three-valued logic:ANYis true if a comparison is, else NULL if one is NULL;ALLis false if a comparison is, else NULL if one is NULL. Over no rowsANYis false andALLtrue.= ANYisINand<> ALLisNOT IN- Row constructors, as in Postgres:
(a, b, …)with two or more items, andROW(…)with any number (ROW(a),ROW()). Two rows are compared item by item wherever a condition can be:WHERE,ON,HAVING,CASE, the SELECT list,CHECK, and the conditions ofUPDATE,DELETEandMERGE. Anywhere else a row is a value (see Rows as values below): =and<>: item by item.=is true if every pair is equal, false if a pair is unequal, and otherwise NULL:(1, NULL) = (1, 2)is NULL,(1, NULL) = (2, 2)false.<>is its negation<,<=,>,>=: left to right; the first pair that is unequal or has a NULL decides, NULL for a NULL.(1, 2) < (1, 3)and(2, NULL) > (1, 5)are true,(1, NULL) < (1, 5)is NULL. Keyset pagination writesWHERE (ts, id) > (?, ?); the index finds the rows withts >= ?whentsis an indexed numeric, date or time columnIS [NOT] DISTINCT FROM: item by item, with NULLs equalIS [NOT] NULL:(a, b) IS NULLis true when every item is NULL,IS NOT NULLwhen none is (so a row can be neither)[NOT] IN: over a list of rows, or a subquery with as many columns as the row has items, with three-valued logic: if no row is equal but a comparison is NULL ((1, NULL)with(1, 2)),INis NULL rather than false andNOT INNULL rather than true. So dbt'sdelete+insertstrategy with a listunique_key,delete from t where (k1, k2) in (select distinct k1, k2 from …), keeps the rows oftwith a NULL key column, as on PostgresANY/SOME/ALLover a subquery:= ANYand= SOMEareIN,<> ALLisNOT IN, and the other operators compare with each row(a, b) op (SELECT x, y …)compares with the subquery's row: NULL if it returns none, an error if it returns more than one. As in Postgres, the subquery comes after the row:(SELECT x, y …) = (a, b)issubquery must return only one column, and(SELECT ROW(x, y) …) = (a, b)compares two records. Up to v0.0.12 a subquery of several columns before the row was compared as one after itBETWEENand simpleCASEwork with rows too ((a, b) BETWEEN (1, 2) AND (3, 4),CASE (a, b) WHEN (1, 2) THEN …).EXISTSis unchanged- Errors, raised when the statement is planned, are Postgres's:
unequal number of entries in row expressions,subquery has too many columns/subquery has too few columns(alsox IN (SELECT a, b …)for a single value),cannot compare rows of zero length, andoperator does not exist: record = integerfor a row compared with a single value - A comparison of two rows is rewritten into comparisons of their items,
so
(k1, k2) = (1, 'x')runs in the index likek1 = 1 AND k2 = 'x'. ForIN, see Query execution. - Rows as values: a row that isn't compared with another row is a value of
type record, Postgres's anonymous record:
SELECT (a, b),ORDER BY (a, b),GROUP BY ROW(a, b),DISTINCT, a row nested in a compared row, a derived table's or CTE's column, aCASEorCOALESCEresult. Up to v0.0.12 these were errors (a row constructor can only be compared …,nested row constructors are not supported): - Results: a record is an Arrow struct whose children are named
f1,f2, … (Postgres's names for an anonymous record's fields), each nullable, with the items' types; a nested row is a nested struct. A NULL record is a NULL struct, and a record whose fields are NULL is not. The column ofSELECT (a, b)is namedrow, as in Postgres. Postgres's own ADBC driver can't read anonymous records (Expected nested record type to have 1 fields but got 2), so the struct is the driver's choice - Text:
CAST(r AS VARCHAR),||andCONCATwrite a record as Postgres does:(1,"a b",,t). A NULL field is empty, and a field is quoted when it is empty or has a quote, a backslash, a parenthesis, a comma or white space, with quotes and backslashes doubled. Booleans aret/f, binary values\x…, and a nested row is quoted text:("(1,2)",3). Text can't be read as a record (input of anonymous composite types is not implemented) - Fields:
(r).f1,(r).f2, …;(ROW(1, 2)).f3iscould not identify column "f3" in record data type.(r).*isn't supported - Comparison: two records (rows nested in compared rows, record
columns) compare as Postgres's btree comparison of records does: field
by field, a NULL field equal to a NULL field and greater than any value.
So
((1, NULL), 3) = ((1, NULL), 3)is true, while(1, NULL) = (1, NULL)is NULL. Records of different lengths whose common fields are equal can't be compared (cannot compare record types with different numbers of columns) IS [NOT] NULL: true for a NULL record or one whose fields are all NULL;IS NOT NULLfor a record none of whose fields is NULL. A field that is a record is not NULL, whatever its fields:((NULL, NULL), NULL) IS NULLis false- Sorting and grouping:
ORDER BY (a, b)sorts by the record, as Postgres sorts records: NULL fields last in ascending order and first withDESC. A NULL record sorts the same way unlessNULLS FIRST/NULLS LASTplaces it.DISTINCT,GROUP BY ROW(a, b),UNION,PARTITION BYandCOUNT(DISTINCT …)treat NULL fields as equal.GROUP BY (a, b)isGROUP BY a, b, as in Postgres. The driver sorts and groups records itself, not the index - Functions:
COALESCE,NULLIF,GREATEST,LEAST,IIF,CASE,CONCAT,COUNT,ANY_VALUE,MODE, the JSON constructors and aggregates (TO_JSON(ROW(1, 'a'))is{"f1":1,"f2":"a"}) and the offset window functions take records. Other functions don't exist for one (function upper(record) does not exist), nor do arithmetic,LIKEand casts to types other than text andRECORD(operator does not exist: record + bigint,cannot cast type record to integer). A record as a condition isargument of WHERE must be type boolean, not type record - Storage: as in Postgres, a record can't be stored: a column of a
table (
CREATE TABLE,CREATE TABLE … AS,ALTER TABLE … ADD COLUMN) or a view can't have type record (column "row" has pseudo-type record), and writing one to a column iscolumn "a" is of type integer but expression is of type record. Arrow structs can't be bound or ingested. A table's name as a whole-row value (SELECT t FROM t,ROW(t.*)) isn't supported - Differences from Postgres: a nested record's fields are known, so
((ROW(1, ROW(2, 3))).f2).f1is 2 (Postgres can't identify the column), and records whose fields have different types (ROW(1)andROW(1.0)) compare by value (Postgres can't compare them). Records of different lengths in one column (UNION ALL,CASE) are an error when they are returned, since an Arrow struct has one set of fields ORDER BY … [ASC|DESC] [NULLS FIRST|LAST]; NULLs sort last withASCand first withDESC, unless explicitly overridden- A bare name is an output column's before it is an input column's, as
in Postgres, also when the output column has no alias, named by its
expression (see "Column names" above). So after
SELECT CAST(iv AS VARCHAR) FROM t,ORDER BY ivsorts the text, not the interval (and the index can't sort it);ORDER BY t.iv, a position or any other expression reads the input. Output columns of the same name must be the same expression (SELECT a, a … ORDER BY a), otherwise it isORDER BY "a" is ambiguous, as inSELECT x::text, x … ORDER BY x.DISTINCT ON (…)names columns the same way (DISTINCT ON "a" is ambiguous), and so doesORDER BYon a set operation, with the first branch's columns - Upgrading from v0.0.12 and earlier: a bare name matched an alias,
then an input column, so after
SELECT CAST(iv AS VARCHAR)ORDER BY ivandDISTINCT ON (iv)used the interval,SELECT x::text, x … ORDER BY xandSELECT a, b AS a … ORDER BY asorted byxandb, andORDER BY t.ivsorted by an aliasiv.SELECT DISTINCT CAST(iv AS VARCHAR) … ORDER BY ivwasfor SELECT DISTINCT, ORDER BY expressions must appear in select list, andORDER BY countafterSELECT COUNT(*)wascolumn "count" does not exist(on aUNION,ORDER BY on a UNION result must use output column names or positions)
Expressions, aggregates, and functions¶
- Function calls (the functions are listed below) are checked when the
statement is planned, before any row is read, wherever a call can be:
the SELECT list,
WHERE,GROUP BY,HAVING,QUALIFY,ORDER BY,ON, window definitions, subqueries, CTEs and derived tables, the body ofCREATE VIEW,INSERT,UPDATE,DELETE,MERGEandRETURNING, andCHECKandDEFAULTexpressions when they are defined. So the errors don't depend on whether a table has rows, and a query that reads none (WHERE false, dbt'swhere false limit 0,ExecuteSchema) gets them too: - Unquoted names fold to lowercase; quoted names are exact.
"upper"(x)calls the builtin;"UPPER"(x)does not. Schema-qualified calls (pg_catalog.upper(x)) are not supported - An unknown function is Postgres's
function nosuchfunc(integer) does not exist, with the argument types the planner infers (unknownfor NULL and for a parameter without a type) - A wrong argument count is
UPPER expects 1 argument,ROUND expects 1 or 2 arguments,REGEXP_INSTR expects 2 to 7 arguments,GREATEST expects at least 1 argumentorRANDOM expects no arguments, also when the arguments are NULL. OnlyCOUNTtakes*(SUM does not accept *), and only aggregates takeDISTINCT(UPPER does not accept * or DISTINCT) - Where an argument's type decides whether the call exists, it is
checked too:
BOOL_OR(int_col)isfunction bool_or(integer) does not exist, and the offset ofLAGmust be an integer - As in Postgres, the tables of a statement are resolved before its expressions, and an unknown column or function in a call's arguments is reported before the call's own error
- Errors that depend on values (division by zero, a failed cast,
LOG(0)) are still raised when a row is evaluated, as in Postgres - A view stored by an earlier version whose body calls an unknown
function fails whenever it is read, also when its tables are empty:
view "v" is no longer valid: function nosuchfunc(integer) does not exist.CREATE OR REPLACE VIEWorDROP VIEWfixes it - A CTE that no part of the statement reads isn't planned, so its calls (like its columns) aren't checked
information_schema.routineslists the functions, each alias too:routine_name(lower case),routine_schema(pg_catalog, though a call can't name it),routine_type(FUNCTION),function_kind(SCALAR,AGGREGATE,WINDOWorTABLE),min_arguments,max_arguments(NULL for no maximum) andalias_of(the function an alias names, NULL otherwise)- Aggregates, with
GROUP BYor over the whole input. They skip NULL inputs, and all butCOUNTgive NULL when there are none: COUNT(*),COUNT/SUM/AVG/MIN/MAX(x),COUNT(DISTINCT x), andCOUNT(DISTINCT a, b, …)(as in MySQL), which counts the distinct combinations of rows where none of them is NULL- With
DISTINCT, values that compare equal count once, as in Postgres (seeGROUP BY):COUNT(DISTINCT iv)ofINTERVAL '1 mon',INTERVAL '30 days'andINTERVAL '1 day'is 2, and theirSUM(DISTINCT iv)adds the first of the two equal ones read:1 mon 1 day(on a cluster, maybe31 days) - Of values that compare equal,
MINandMAXreturn the last one, as in Postgres:MAXofINTERVAL '1 mon'and thenINTERVAL '30 days'is30 days. Up to v0.0.12 they returned the first one, except as window functions SUM,AVGand the variances and standard deviations add their values exactly, whatever their type, also as window functions: every integer, decimal and double is a fraction whose denominator is a power of 2 times a power of 5, so the driver keeps the sum as such a fraction and rounds the result once, to the nearest double (ties to even), or to the result's decimal places. So the result is the same in any row order: on a cluster, rows come in a different order each time, and summing doubles in that order used to give a different last digit from one run to the next. Postgres also adds doubles one by one, in its plan's order.SUMof integers isBIGINTand fails (integer overflow in SUM) only if the total doesn't fit, andSUMof decimals is exact at their scale.AVG(DOUBLE PRECISION) is the exact mean, rounded once, also for integers beyond 2^53 and for decimals- Doubles: a NaN, or both infinities, make
SUMandAVGNaN, and one infinity makes them that infinity, as adding them in any order would; the variances are then NaN, as in Postgres. A finite sum beyond the largest double is an infinity, as before (Postgres raises "value out of range: overflow") - Adding a value costs an integer addition, about 25 ns for a double (40 ns with the squares a variance needs), against 3–7 µs to read a row
- Intervals:
SUMandAVGareINTERVALs, as in Postgres.SUMadds the months, days and time each on its own, without carrying one into the next:INTERVAL '1 mon'+INTERVAL '30 days'is1 mon 30 days, and 20 and 5 hours are25:00:00.AVGdivides that sum by the count asinterval / ndoes (see Intervals and date/time arithmetic): a fraction of a month becomes days, 30 to a month, rounded to millionths of a day, one of a day becomes time, and the time is rounded to microseconds, half to even (AVGof1 sec,0and0is00:00:00.333333, as isINTERVAL '1 sec' / 3). Each field is summed exactly, so only a total out of range is "interval out of range", in any row order; Postgres adds the values one by one and fails as soon as a running total is out of range, so the same rows in another order can fail there.SUM's time part has the interval's range (about ±2,562,047 hours, see Intervals and date/time arithmetic), whileAVG's sum may go up to Postgres's (microseconds in 64 bits) - Upgrading from v0.0.12 and earlier:
SUMandAVGof an interval returned aDOUBLE PRECISION0 (soTO_CHAR(SUM(iv), …)wasexpected a date, time or timestamp, got float64), also as window functions - Upgrading: results of v0.0.7 and earlier can differ in their last
digits:
SUM,AVGand the variances of doubles,AVGof decimals, andAVGof integers whose sum is beyond 2^53. The new value is the correctly rounded one.SUMof integers no longer fails when a running total overflows and the final one fits, and a decimalSUMwhose values include integers (SUM(CASE WHEN … THEN 1 ELSE 2.5 END)) no longer leaves the integers out
STRING_AGG(x, sep [ORDER BY …]), andLISTAGG(x [, sep]) [WITHIN GROUP (ORDER BY …)](Snowflake, Oracle;sepdefaults to'');STRING_AGG(x, sep) WITHIN GROUP (ORDER BY …)also works (SQL Server). Values of any type are joined as text. As in PostgreSQL, each value but the first is preceded by its own row's separator, and withDISTINCT(whoseORDER BYmay only use the arguments) the values come out sortedBOOL_OR(b),BOOL_AND(b)/EVERY(b)on booleans,ANY_VALUE(x)(a non-NULL value of the group)VAR_SAMP/VARIANCE,VAR_POP,STDDEV_SAMP/STDDEV,STDDEV_POPreturn exactNUMERICfor integer and numeric inputs, using PostgreSQL's division scale and square-root rounding; floating inputs returnDOUBLE PRECISION. Values and their squares are summed exactly. Arrow output uses one common decimal scale per result column. The sample versions are NULL for one value, the population ones 0- Ordered-set aggregates:
PERCENTILE_CONT(f) WITHIN GROUP (ORDER BY x)interpolates (numbers as doubles, and intervals),PERCENTILE_DISC(f) WITHIN GROUP (ORDER BY x)returns the first value at or past fractionf(any sortable type),MODE() WITHIN GROUP (ORDER BY x)the most frequent value (the first in sort order among ties), with PostgreSQL's formulas and errors.MEDIAN(x)isPERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x). The fraction is evaluated once per group (a constant, a parameter or a grouped column) and must be between 0 and 1- Between two intervals
loandhi,PERCENTILE_CONTislo + (hi - lo) * p,pbeing how far the position is fromlotohi, withinterval * n(see Intervals and date/time arithmetic), as in Postgres:MEDIANof1 yearand1 mon 1 day 1 secis6 mons 16 days -11:59:59.5. The difference's months and days must fit in 32 bits ("interval out of range"), but its time needn't fit an interval's nanoseconds:MEDIANof-2000000 hoursand2000000 hoursis00:00:00 - Upgrading from v0.0.12 and earlier: the interpolation cascaded
fractions in nanoseconds (that
MEDIANwas6 mons 15 days 12:00:00.5), and a difference past ±2,562,047 hours was "interval out of range"
- Between two intervals
agg(…) FILTER (WHERE cond)on any aggregate, also as a window function: only the rows wherecondis true are aggregated, and their arguments alone are evaluated- Aggregate
ORDER BYmatters only forSTRING_AGG/LISTAGG; its NULLs sort last by default in either direction, as inORDER BY - Window functions,
fn(…) OVER ([PARTITION BY …] [ORDER BY …] [frame]), in the SELECT list,ORDER BYandQUALIFY. They see the rows afterWHERE,GROUP BYandHAVING, and work anywhere a query does (joins, subqueries, CTEs, views, CTAS,INSERT … SELECT): - Ranking:
ROW_NUMBER,RANK,DENSE_RANK,PERCENT_RANK,CUME_DIST,NTILE(n) - Offset:
LAG/LEAD(x [, offset [, default]]),FIRST_VALUE(x),LAST_VALUE(x),NTH_VALUE(x, n), each withIGNORE NULLSorRESPECT NULLS(the default) after the call (SQL standard, Snowflake) or after its last argument (BigQuery, DuckDB).IGNORE NULLScounts only the rows whose value is not NULL, soLAST_VALUE(x) IGNORE NULLS OVER (ORDER BY ts)fills forward;LAG(x, 0)is still the current row's value - Aggregates:
COUNT(*),COUNT/SUM/AVG/MIN/MAX(x),STRING_AGG,LISTAGG,BOOL_OR,BOOL_AND,EVERY,ANY_VALUE, and the variances and standard deviations, withFILTER (WHERE …), also over grouped results (SUM(COUNT(*)) OVER (ORDER BY day)). As in PostgreSQL,DISTINCT, an aggregateORDER BY(order the window instead) and the ordered-set aggregates andMEDIANare not supported withOVER - Frames:
{ROWS | RANGE | GROUPS} {start | BETWEEN start AND end}withUNBOUNDED PRECEDING,n PRECEDING,CURRENT ROW,n FOLLOWING,UNBOUNDED FOLLOWING, andEXCLUDE CURRENT ROW | GROUP | TIES | NO OTHERS(GROUPremoves the current row and its peers,TIESonly its peers).RANGEoffsets are numbers for a numericORDER BYkey and intervals for dates, times, timestamps and intervals; as in PostgreSQL, aTIMEkey moves by the interval's time part and its frames don't wrap around midnight. Without a frame, the SQL default applies: withORDER BY, from the start of the partition to the current row's last peer (rows with equal keys); withoutORDER BY, the whole partition - Named windows:
WINDOW w AS (…),OVER w,OVER (w ORDER BY … [frame]) QUALIFYfilters on window results and may use output aliases (QUALIFY ROW_NUMBER() OVER (PARTITION BY k ORDER BY ts DESC) = 1)- Result types:
BIGINTforROW_NUMBER,RANK,DENSE_RANK,NTILEandCOUNT;DOUBLE PRECISIONforPERCENT_RANK,CUME_DISTandAVG(INTERVALfor intervals); the argument's type forMIN,MAXand the offset functions; the other aggregates as withGROUP BY - As in
ORDER BY, NULLs sort last withASCand first withDESC. Rows that tie on the window'sORDER BYkeep their input order - Dates, times and timestamps become text (
CAST(x AS VARCHAR),||,CONCAT,STRING_AGG,LIKE,MD5(CAST(x AS TEXT)), …) as Postgres writes them, whatever the declared precision: seconds always, then the fraction without trailing zeros (2024-01-10 10:00:00,2024-01-10 10:00:00.5,10:00:00.05), a timestamp with time zone as its local time in the session time zone with that zone's offset (2024-01-10 10:00:00+00in UTC,2024-01-10 02:00:00-08in America/Los_Angeles; see Session time zone), a year before 1 AD as a BC year (0044-03-15 BC), and a year past 9999 with all its digits (10000-01-01). Text in those forms reads back as the same value, and there is no year 0. JSON (TO_JSON,JSON_BUILD_OBJECT, …) has the ISO 8601 form,2024-01-10T10:00:00.5+00:00, also in the session time zone. Intervals are written as in Postgres too, with a+on a field after a negative one (-1 days +02:00:00). Query results are Arrow values, which this doesn't change - Upgrading from v0.0.7 and earlier: they kept the declared
precision's trailing zeros (
2024-01-10 10:00:00.000000,10:00:00.000,2024-01-10 10:00:00.000+00), wrote years before 1 AD as0000-…or-0001-…, and left out an interval's+. The text of such values changes, and with it every hash of the text: dbt_utils'generate_surrogate_keyover a timestamp or time column, and thedbt_scd_idof dbt snapshots (anmd5of the key andupdated_atas text). Keys that earlier versions stored don't match the ones computed now (which match Postgres's): an incremental model whoseunique_keyis such a key inserts its rows again instead of updating them, and a snapshot whoseunique_keyis one sees every row as new. Rebuild those (dbt run --full-refresh, or drop the snapshot's table and rundbt snapshot) before running them with this version. Snapshots only read their storeddbt_scd_ids back, so they keep working; the rows they add from now on have ids in the new form. Text stored by earlier versions (CAST(ts AS VARCHAR)written to a column) keeps the old form, and still reads back as the same values, except for years before 1 AD - Casts:
CAST(x AS type)andx::typefail on a value that doesn't convert (text that doesn't parse, a value out of the type's range, overflow).TRY_CAST(x AS type)andSAFE_CAST(x AS type)return NULL for it instead, andCAST(x AS type DEFAULT v ON CONVERSION ERROR)returnsv(converted to the type). The result has the target type. An error while computingx, and a cast between types that never convert (DATEtoBOOLEAN), are still errors. A value that doesn't convert fails withInvalidDataand a class22SQLSTATE, and a cast between types that never convert withInvalidArgumentand42846(see Error handling). A cast toVARCHAR(n)orCHAR(n)cuts the text toncharacters, andCHAR(n)pads it (see String lengths). A cast to aTIME(p)orTIMESTAMP(p)of a lower precision rounds topdigits (see Fractional seconds under Types), and casts betweenTIMESTAMP,TIMESTAMP WITH TIME ZONE,DATE,TIMEand text use the session time zone (see Session time zone) - Text as an integer (
SMALLINT,INTEGER,BIGINT) is read as Postgres'sint4inreads it: optional spaces and a sign, then digits, with single underscores between them ('1_000'), or0x,0oor0band hexadecimal, octal or binary digits ('0x1F'is 31). Anything else ('1.5','1e3','1.0','') isinvalid input syntax for type integer: "1.5"(22P02), and a value past the type's range isvalue "3000000000" is out of range for type integer(22003), withsmallintorbigintfor those types. Text is read so wherever it becomes an integer: written to an integer column (INSERT,UPDATE,MERGE,DEFAULTs, bound parameters, bulk ingest andredis-adbc copy), compared with an integer (WHERE n = '1.5'fails), and as a function's integer argument (LPAD(s, '3.5')fails, as anINTEGER). Text compared with a value of another type it doesn't convert to fails with that type's conversion error and SQLSTATE (DATE '2024-01-01' = 'x'isinvalid input syntax for type date: "x",22007) - Differences: an
INlist stops at the first value that matches, so1 IN ('1', '1.5')is true, and an indexed column'sINlist skips the values that don't convert; Postgres reads every value first, and fails on'1.5'. A text column compared with an integer is read row by row (invalid input syntax for type integer: "a"for a row of'a'); Postgres refuses the comparison (operator does not exist: text = integer) - Upgrading from v0.0.12 and earlier: text was read as a number and
rounded, so
CAST('1.5' AS INTEGER)was 2,'1e3'was 1000 and'1.0'was 1, also when written to a column, andWHERE n = '1.5'matched the rows wherenis 2;'0x1F'and'1_000'were errors. The errors wereinvalid decimal "x",value 3000000000 out of range for INTEGERandcannot convert decimal value "99999999999999999999" to BIGINT, and text compared with a value it didn't convert to wascannot compare int32 with string, without a SQLSTATE.redis-adbc copy -type n=INTEGERrounded a field of1.5; it now stops there, or copies NULL with-on-error null - Text as a number, a boolean, a date or a time is read as Postgres
reads it, with its errors, wherever text becomes one (as for integers):
NUMERICasnumeric_inreads it: optional spaces and a sign, then digits with an optional point and exponent ('.5','1.','1.5e-3'), or0x,0oor0band hexadecimal, octal or binary digits ('0x1F'is 31), with single underscores between digits ('1_000.000_1','0x_1F'), then optional spaces. Anything else isinvalid input syntax for type numeric: "abc"(22P02). An exponent past ±1,073,741,823, or more than 131,072 digits before the point or 16,383 after it, isvalue overflows numeric format, and a value the type can't holdnumeric field overflow; a field with precision 5, scale 2 must round to an absolute value less than 10^3(22003), also when it isn't text (CAST(123456 AS NUMERIC(5, 2)))DOUBLE PRECISIONandREALasfloat8inandfloat4inread them, with C'sstrtod: optional spaces and a sign, then digits with an optional point and exponent,0xand hexadecimal digits with an optional point and binary exponent ('0x1F'is 31,'0x1.8p1'3), orInfinity,inforNaN(alsonan(…)), in any case, then optional spaces; no underscores. Anything else isinvalid input syntax for type double precision: "x"(realfor aREAL), and a value past the type's range, or one that isn't 0 but rounds to it,"1e400" is out of range for type double precision(22003). A double past aREAL's range isvalue out of range: overflow, orunderflowif it rounds to 0BOOLEANasboolinreads it: without spaces, a prefix oftrueorfalse,yesorno,on,oforoff,1or0, in any case ('t','fa','YE';'o'isn't one). Anything else isinvalid input syntax for type boolean: "maybe"(22P02)- Dates, times and timestamps: text that isn't one is
invalid input syntax for type date: "x"(22007;time,timestamp,timestamp with time zoneandtime with time zonefor those types), and a field out of rangedate/time field value out of range: "2024-02-30"(22008), also a time past24:00:00('24:00:01','23:59:60.5';'23:59:60'is24:00:00). An offset is written+5,+0530,+05:30or+05:30:15, up to 15:59:59 (past it,time zone displacement out of range: "…",22009), and a zone name with a/that doesn't exist istime zone "mars/base" not recognized(22023). Past the driver's ranges (see Intervals and date/time arithmetic) a date isdate out of range: "…"and a timestamptimestamp out of range: "…"(22008). ADATEchecks a time, offset or zone after the date and ignores them ('2024-01-05 23:00-05'and'2024-01-05 24:00'are 2024-01-05), and so does aTIMEwith an offset or zone after the time, and a date before it ('10:00+05'is 10:00:00), and aTIMESTAMPwith an offset or zone ('2024-01-05 10:00-05'is 10:00; see Session time zone)
- Differences: dates and times are read in ISO 8601 order only
(
2024-01-05 10:00:00, with four to seven year digits); Postgres also reads'Jan 5 2024','20240105','2024-1-5','10:00 PM','1000'and the special values ('epoch','today','infinity','allballs'), which areinvalid input syntaxhere. ADATEand aTIMESTAMPhave the driver's ranges, so'5874898-01-01'is a date here (Postgres:date out of range) and'294276-12-31'isn't a timestamp. ANUMERICcan't holdNaNor the infinities (see NaN and the infinities), and has at most 76 digits (38, with 9 after the point, without a precision), soCAST('1e40' AS NUMERIC)isnumeric field overflow. A number with an exponent is aDOUBLE PRECISIONhere, soCAST(1e39 AS REAL)isvalue out of range: overflow, where Postgres reads aNUMERICand says"1000…" is out of range for type real - Upgrading from v0.0.12 and earlier:
NUMERICtext with an exponent was read as a double, losing digits ('12345678901234567890123e-3'was 12345678901234567000.000), and'1_000'and'0x1F'were errors. ADOUBLE PRECISIONread'1_000'as 1000, refused'0x1F','-NaN'and'1e400'(with22P02), and read'1e-400'as 0; aREALpast its range wasInfinity(also a double cast to one), or 0. ABOOLEANwas a whole word or one letter ('tr','of'were errors).'24:00:01'was aTIMEpast the end of the day, an offset past 15:59 was read,+05:30:15was misread, aDATEwith a time was the date of that timestamp in UTC ('2024-01-05 23:00-05'and'2024-01-05 24:00'were 2024-01-06),DATE '2024-01-05 10:00'was an error, so was aTIMEwith a date or an offset, and a date after 294247 AD wastimestamp out of range for unit us. The errors had the driver's messages (see Error handling) - Math functions:
ROUND(x [, n])andTRUNC(x [, n])(nmay be negative:ROUND(1250, -2)is 1300),FLOOR,CEIL/CEILING,MOD/%,POWER/POW,SQRT,CBRT,LN,LOG(x)(base 10) /LOG(b, x),LOG10,EXP,SIGN,ABS,PI(),RANDOM() ROUND(x)rounds floating inputs half to even, preserving negative zero; floating-to-integer casts use the same rule. Integer andNUMERICrounding remains half away from zero. The two-argument floating extension retains decimal rounding (ROUND(2.675e0, 2)is 2.68)- Integers and doubles keep their type.
ROUND(NUMERIC(p,s), n)has scalen(at mosts);ROUND(x),TRUNC(x),FLOORandCEILscale 0.MODand%have their arguments' common type (NUMERICstays exact);SQRT,CBRT,LN,LOG,EXP,POWER,PIandRANDOMreturnDOUBLE PRECISION - Errors as in Postgres for the square root of a negative number, the
logarithm of zero or of a negative number,
MOD/%by zero (of any numeric type), zero to a negative power, and overflow.EXPof a double that underflows is an error too ("value out of range: underflow");EXPof aNUMERICthen returns 0, as Postgres'sexp(numeric)does - Trigonometric functions, as in Postgres, all
DOUBLE PRECISION: - In radians:
SIN,COS,TAN,COT,ASIN,ACOS,ATAN,ATAN2(y, x);RADIANS(degrees)andDEGREES(radians)convert - In degrees:
SIND,COSD,TAND,COTD,ASIND,ACOSD,ATAND,ATAN2D(y, x). With Postgres's algorithm, they are exact where the result is a simple number:SIND(30)is 0.5,COSD(60)0.5,TAND(45)1,ASIND(0.5)30,ATAN2D(1, 1)45, andTAND(90)andCOTD(0)are Infinity - Arguments are numbers (or text that converts); a NULL argument gives
NULL and a NaN gives NaN. Errors as in Postgres:
ASIN: input is out of rangefor an argument beyond [-1, 1] ofASIN,ACOS,ASINDandACOSD, and for an infinite one of the sines, cosines, tangents and cotangents;DEGREEScan overflow andRADIANSunderflow ("value out of range: …") - The radian functions are Go's
mathpackage. Postgres uses the platform's C library, which can differ in the last bit:TAN(1)is 1.557407724654902 here and 1.5574077246549023 with glibc - So dbt_utils'
haversine_distanceworks, in its default form (withradians) and in its BigQuery form (withacos(-1)) - String functions; positions are 1-based and count characters, not bytes:
OCTET_LENGTHandBIT_LENGTHinstead count bytes/bits of VARCHAR or VARBINARY, return BIGINT, preserve NULL, and reject fixed CHAR/other argument types. SUBSTRING(s, start [, len]),SUBSTRING(s FROM start [FOR len])(with text arguments, the pattern forms below),SUBSTR,LEFT(s, n)/RIGHT(s, n)(a negativendrops characters from the other end),POSITION(sub IN s),STRPOS(s, sub)TRIM([BOTH | LEADING | TRAILING] [chars] FROM s),TRIM(s [, chars]),LTRIM,RTRIM,BTRIM(spaces by default),LPAD/RPAD(s, len [, fill]),REPLACE,REVERSE,REPEAT,INITCAP,MD5,STARTS_WITH,SPLIT_PART(s, delim, n)(a negativencounts from the end)REGEXP_REPLACE(s, pattern, replacement [, start [, n]] [, flags])replaces the first match, or all of them with flagg; with Postgres 16'sstartandn, then-th match fromstart(all of them ifnis 0).\1…\9and\&in the replacement insert groups and the match. Flags and pattern syntax as below- Regular expressions, with Postgres's arguments, results and errors:
s ~ pattern,s ~* pattern(case-insensitive),s !~ pattern,s !~* pattern. As in Postgres, they bind like||: tighter than comparisons,IS,LIKE,BETWEENandIN, looser than arithmetic (s ~ 'a' || 'b'is(s ~ 'a') || 'b')REGEXP_LIKE(s, pattern [, flags]),REGEXP_COUNT(s, pattern [, start [, flags]]),REGEXP_INSTR(s, pattern [, start [, n [, endoption [, flags [, subexpr]]]]])andREGEXP_SUBSTR(s, pattern [, start [, n [, flags [, subexpr]]]]), as in Postgres 15 (Snowflake's have the same order): then-th match from positionstart, or its groupsubexpr. A search fromstartsees the text before it, so^does not match thereREGEXP_MATCH(s, pattern [, flags])returns the first match's groups (or the whole match if there are none) as the text of Postgres'stext[]result,'{bar,beque}', since the driver has no array type.REGEXP_SUBSTR(s, pattern, 1, 1, '', n)returns groupnaloneSUBSTRING(s FROM pattern)returns the first group of the first match, or the whole match. As in Postgres, the argument types choose the form: text is a pattern and an integer a position, soSUBSTRING(s, '2')is a pattern match andSUBSTRING(s FROM 2 FOR 3)a position[NOT] SIMILAR TO pattern [ESCAPE e]andSUBSTRING(s SIMILAR pattern ESCAPE e)(alsoSUBSTRING(s FROM pattern FOR e)) translate the SQL pattern as Postgres does. It must match the whole string,%and_are wildcards,|,*,+,?,{m,n},( )and[ ]keep their meaning, and the escape character (backslash by default, none withESCAPE '') makes the next character literal (an escaped letter is an RE2 escape, such as\dfor a digit).SUBSTRING … SIMILARreturns the part between the twoe"separators, where the part before them matches as little of the string as it can- Flags:
i(case-insensitive),c,n/m(newline-sensitive),s,p,w,q(a literal pattern), andgforREGEXP_REPLACEonly;x(expanded),b(basic) ande(extended) are supported. Without flags,.matches newlines and^and$anchor the whole string - Patterns support backreferences, lookaround, and PostgreSQL word
constraints (
\y,\Y,\m,\M;\bis backspace). Greedy patterns select the longest match (SUBSTRING('xyz' FROM 'x|xy')returnsxy). Advanced patterns have a 250 ms execution deadline. The engines are Go RE2 and regexp2; full PostgreSQL ARE compatibility, including capture priority and embedded mode options, remains incomplete. A pattern that doesn't compile is an error such asinvalid regular expression: missing closing ): `(` - Patterns over columns are evaluated by the driver, on the rows the rest
of the
WHEREclause finds in the index:~ '^abc'andSIMILAR TO 'abc%'are not index prefix queries.REGEXP_MATCHESandREGEXP_SPLIT_TO_TABLE/REGEXP_SPLIT_TO_ARRAY(sets and arrays) are not supported jsonb || jsonbmerges objects with right-hand keys winning, concatenates arrays, and wraps non-array operands when needed. The merge is top-level. A quoted text literal beside a JSON expression is parsed as JSON; invalid JSON raises22P02.- Conditional functions:
NULLIF(a, b),GREATEST(…)/LEAST(…)(NULL arguments are ignored), andIIF(cond, a, b)(likeCASE, only the chosen branch is evaluated).COALESCEevaluates arguments only until the first non-NULL value. Immutable constant errors in reachable CASE branches can be reported during planning; provably dead constant branches are pruned. Row-dependent expressions and volatile functions are not evaluated by this planning check. This is scoped constant analysis, not the full PostgreSQL optimizer.COALESCE,GREATESTandLEASTwiden their arguments to a common type (COALESCE(int_col, 2.5)isNUMERIC); a string literal (see below), or a number that type holds exactly, takes the other arguments' type (COALESCE(int_col, 0)staysINTEGER,GREATEST(d, '2024-01-01')is aDATE) - Quoted string literals (
'1',$$1$$) have no type of their own, as in Postgres (itsunknown): where an expression's value is one of several values, a literal among them takes the type of the others, and is text if all are. SoCASE WHEN id = 1 THEN '1' ELSE 1 ENDis aBIGINT, whose'1'is the integer 1: arithmetic, sorting, grouping,DISTINCT, window partitions and index queries see integers. These are the results ofCASEandIIF, the arguments ofCOALESCE,GREATESTandLEAST,LAG's andLEAD's default,NULLIF's arguments (NULLIF('1', int_col)is anINTEGER), and a column of the branches ofUNION,INTERSECTandEXCEPT(SELECT '1' UNION SELECT 1is aBIGINT), also inCREATE TABLE … ASand views. A cast gives a literal a type (CAST('1' AS VARCHAR),'1'::text). An operand of+,-,*,/and%(MOD) takes its type from the operator and the other operand, as Postgres resolves the operator: a value of the other operand's type if the operator takes two ('1' + 1is 2,id + '1'adds 1,d - '2024-01-01'is a number of days,ts - '2024-01-01'an interval), and otherwise the type of the one form the operator has for it (ts + '1 day'andtm + '01:00'add an interval,iv * '2'multiplies by a double), also inWHERE(id = '1' + 1is an index query),GROUP BY,UPDATE … SETand views. ANUMERICliteral keeps its own digits (n * '2'hasn's scale). - Errors, as in Postgres, when the statement is planned, also if no
row reads the value: a literal that isn't a value of the type (
CASE WHEN … THEN 'x' ELSE 1 ENDand'x' + 1areinvalid input syntax for type integer: "x",22P02); typed values without a common type, such as aVARCHARcolumn and an integer (CASE WHEN … THEN v ELSE 1 ENDisCASE types integer and varchar cannot be matched,42804, and so forCOALESCE,GREATEST,LEASTandIIF; aCASEof literals alone is text, soCOALESCE(CASE WHEN … THEN '1' END, 1)is such an error too); and an operator that has no form for the other operand's type (b + '1'isoperator does not exist: boolean + unknown,42883) or more than one (d + '1'could add days or an interval: `operator is not unique: date- unknown
,42725), as are two literals ('1' + '1',NULL + '1') and-'1'. ForMOD(…)they arefunction mod(date, unknown) does not existandfunction mod(unknown, unknown) is not unique`.
- unknown
- Differences: the literal is read as
CASTreads text, so'1.5'as an integer is 2 where Postgres fails ('1.5' + 1is 3). ACASEwhose results are allNULLhas no type, asNULLhas, soCOALESCE(CASE WHEN … THEN NULL END, '2', 1)is 2 (Postgres'sCASEis text:COALESCE types text and integer cannot be matched).%andMODof aDOUBLE PRECISIONare the driver's (Postgres has none), and so isf % '1';tm * '2'fails astm * 2does (Postgres reads the time as an interval);+'1'stays text (Postgres: a double). Comparisons read text as the other side's type, andINSERT,UPDATEand casts convert it to theirs, as before. - Upgrading from v0.0.12 and earlier: results change type. Such a
CASE,IIF,NULLIForLAGwas aVARCHAR, whose other values weren't converted (CASE WHEN id = 1 THEN '1' ELSE 1 ENDgave the text'1'and the integer 1), so its column, and the columnCREATE TABLE … ASmade of it, is now aBIGINT(or the other values' type),+ 1of it works, andCOUNT(DISTINCT …),DISTINCTand window partitions see one value where they saw two.COALESCE,GREATESTandLEASThad the type already, but gave a literal's text where the other arguments were NULLs without a type (COALESCE(CASE WHEN … THEN n END, '0')gave'0', so+ 1of it failed), andGREATEST('1', '2', 3)andSELECT '1' UNION SELECT 1weretypes VARCHAR and BIGINT cannot be matched. Typed values without a common type gave aVARCHARcolumn of values of both types, and are now an error: cast them (CAST(n AS VARCHAR)). Arithmetic of a literal was an error (operator + is not supported for string and int64, orfor DATE and VARCHAR, without a SQLSTATE), and works now, or fails with Postgres's error (42883,42725) - Functions return NULL for a NULL argument, except
COALESCE,NULLIF,GREATEST,LEASTandIIF. Functions of constants are computed once, soWHERE n > ROUND(?)is still an index query; functions over columns are evaluated by the driver on the rows the index returns.RANDOM()is computed for every row and never pushed down - Date/time functions. A
TIMESTAMP WITH TIME ZONE's fields are those of its local time in the session time zone (see Session time zone), and the current time is fixed once per statement: CURRENT_DATE,CURRENT_TIMESTAMP/NOW(),CURRENT_TIME,LOCALTIMESTAMP,LOCALTIME(the date and times are local), read in microseconds, as Postgres's clock is. With a precision,CURRENT_TIMESTAMP(p),LOCALTIMESTAMP(p),CURRENT_TIME(p),LOCALTIME(p)andNOW(p)are rounded topdigits, as in Postgres (CURRENT_TIMESTAMP(0)is whole seconds, andCURRENT_TIMESTAMP(2)isCAST(CURRENT_TIMESTAMP AS TIMESTAMPTZ(2))), and have typeTIMESTAMP(p) WITH TIME ZONE,TIMESTAMP(p),TIME(p) WITH TIME ZONE(CURRENT_TIME, up to 6 digits) orTIME(p)(LOCALTIME);pis an integer constant from 0 to 9.CURRENT_TIMEis the local time with the session time zone's offset (10:30:00-04), as in Postgres. Up to v0.0.12 it was aTIME, an Arrowtime64; it is now a string column (see Types)EXTRACT(field FROM x)returns numeric;DATE_PART('field', x)returns double precision. EXTRACT computes fractional fields from integer components, preserving microseconds without a float intermediary. Numeric arithmetic, CASE and window sums retain the result's scale; Arrow uses the existing numeric result-shape resolution. Both supportyear,isoyear,quarter,month,week(ISO),day,dow(0 = Sunday),isodow,doy,hour,minute,second,milliseconds,microseconds,epoch,decade,century,millennium, and for a timestamp with time zone or a time with time zonetimezone,timezone_hourandtimezone_minute(its offset, east of UTC). A time with time zone has the time's fields,epoch(the seconds of its time in UTC, so'10:00+02'is 28800) and the offset's; the others areunit "day" not supported for type time with time zone, as in Postgres. ShortcutsYEAR(),QUARTER(),MONTH(),WEEK(),DAY(),DAYOFYEAR(),HOUR(),MINUTE(),SECOND()DATE_TRUNC('unit', x)(dates stay dates),DATE_DIFF('unit', a, b)(unit boundaries crossed),LAST_DAY(d)DATE_BIN(stride, source, origin)(Postgres 14): the start of thestride-long bin, counted fromorigin, thatsourceis in, also whenoriginis after it. The stride is elapsed time (a day is 24 hours) and has no months or years (timestamps cannot be binned into intervals containing months or years). It is aTIMESTAMPif the source or the origin is one and neither has a time zone, otherwise aTIMESTAMP WITH TIME ZONE, as Postgres picks its formDATEADD(part, n, x)andDATEDIFF(part, a, b)(Snowflake, Redshift, SQL Server; what dbt'sdateaddanddatediffemit), withparta bare keyword or a string:year,quarter,month,week,day,hour,minute,second,millisecond,microsecond, their plurals, and abbreviations such asyy/yyyy,qq,mm/mon,wk/ww,dd/d,hh,mi/n/m,ss/s,ms,us/mcs(mis minute andwis week, as in Snowflake). In the part's position a bare part name is the part even if a column has that name (write"day"for the column); everywhere else it is the columnDATEDIFFcounts boundaries crossed, exactly likeDATE_DIFF.DATEADDaddsnparts (a non-integernis rounded, as byCAST(n AS BIGINT)); months clamp to the end of the month. As in Snowflake, a date stays a date for a part of a day or longer and becomes a timestamp for a smaller one; timestamps and times keep their type (times wrap around midnight, and date parts aren't valid for them), and text is read as a timestamp- Aliases:
TIMESTAMPADD/TIMESTAMPDIFF(as in Snowflake; MySQL'sTIMESTAMPDIFFcounts whole units elapsed instead),DATE_ADD(part, n, x)(Trino, Databricks), andDATE_ADD(x, INTERVAL n part)/DATE_SUB(x, INTERVAL n part)(MySQL, BigQuery), which areDATEADD(part, n, x)/DATEADD(part, -n, x) MAKE_DATE,MAKE_TIME,MAKE_TIMESTAMP,MAKE_TIMESTAMPTZ,TO_TIMESTAMP(epoch_seconds),EPOCH(x),EPOCH_MS(x). As in Postgres, a negative year is BC (MAKE_DATE(-44, 3, 15)is0044-03-15 BC), and year 0 is an error (date field value out of range: 0-03-15). Up to v0.0.12 they counted years astronomically, so -44 was 45 BC and 0 was 1 BCTO_CHAR(x, format)with Postgres's template patterns, read as Postgres reads them (case-sensitive, the first pattern that matches):HH/HH12,HH24,MI,SS,MS,US,FF1toFF6,SSSS/SSSSS,AM/PM/A.M./P.M.,Y,YYY,YYYY,YYY,YY,Y,IYYY,IYY,IY,I,BC/AD/B.C./A.D.,MONTH/Month/month,MON/Mon/mon,MM,DAY/Day/day,DY/Dy/dy,DDD,IDDD,DD,D,ID,W,WW,IW,CC,J,Q,RM/rm,TZ/tz,TZH,TZMandOF. The numbers and the meridiem and era indicators can also be written in lower case (yyyy-mm-dd,pm).- Modifiers:
FMbefore a pattern (no padding),TM(names without padding; they are English), andTH/thafter a number (DDthis29th).FX, andSPafter a pattern, are accepted and ignored, as in Postgres - Names are padded to 9 characters and
RMto 4 ('XI '), numbers with zeros, unlessFMis given;MS,USandFF1toFF6are always padded.CCis the century that starts in a year ending in 01, and-01for the 1st century BC. Years before 1 AD are their BC years, forBC(0044 BC) - A timestamp with time zone is formatted as its local time in the
session time zone, and a date as local midnight:
TZis that zone's abbreviation there (UTC,PST/PDT,IST;tzin lower case), andTZH,TZMandOFits offset (-08,00and-08;+00,00and+00in UTC). For a timestampTZis empty and the offset+00 - Anything else is copied: text in double quotes (where
\escapes the next character),\"(a"), and every character that doesn't start a pattern, soMmisMmwhileDdis two weekday numbers, as in Postgres. An empty format gives NULL - An interval is formatted from its own fields, as in Postgres:
YYYYis its years,MMthe months left over (TO_CHAR(INTERVAL '14 mons', 'YYYY MM')is0001 02),DDits days andHH24its hours, which can pass 23.HH/HH12are on a 12-hour clock, andAM/PMfollow the hour of the day. A negative interval's fields are negative, and numbers are padded one wider for the sign (YYYYis-0001,MM-02).CCis the years / 100,DDDthe length in days with a month as 30,QandRMcome from the months (empty without them), andJ,IYYY,IWandIDDDare Postgres's date arithmetic on the fields. The patterns that need a calendar date (MONTH,MON,DAY,DY,D,ID,BC/AD,TZ,TZH,TZMandOF) are an error, as in Postgres:invalid format specification for an interval value - A time is formatted as the interval since midnight, because Postgres
casts it to one:
TO_CHAR(TIME '13:05', 'YYYY-MM-DD HH24:MI')is0000-00-00 13:05. The session time zone doesn't change it. Postgres has noTO_CHARof a time with time zone, so it isfunction to_char(time with time zone, varchar) does not exist - Upgrading from v0.0.12 and earlier:
TO_CHARof an interval was an error (expected a date, time or timestamp, got interval), and a time was formatted as on 1970-01-01:YYYYwas1970,J2440588andDayThursday, andTZ,Dand the other patterns that need a calendar date gave a value
- Modifiers:
TO_DATE(text, format)andTO_TIMESTAMP(text, format)read the same templates as Postgres 16 does: every pattern above butTZ,tzandOF, which are an error (formatting field "TZ" is only supported in to_char, as in Postgres 16).TO_TIMESTAMPreads the text as a local time in the session time zone, unlessTZHandTZMgive its offset.TO_DATEignores the time and the offset- Reading: a number is as wide as its pattern when another number
follows it (
YYYYMMDDreads20240315); otherwise, or withFMorTH, it takes all its digits and a sign (-44forYYYY). Names are read in any case. OutsideFXmode, spaces before and after a field are skipped, a separator or space in the format matches one in the text or none (TO_DATE('2024/02', 'YYYY-MM-DD')is 2024-02-01), and any other character, also in quotes, skips one character. Text left after the format is ignored - Combining fields: a field that isn't given, or is 0, is year 0
(1 BC), January, the 1st or midnight, so
TO_DATE('03-15', 'MM-DD')is0001-03-15 BC.YYY,YYandYare years near 2020 (69is 2069,701970), or withCCyears of that century; a negative year orBCis BC.Jgives the date (withDDandMMreplacing its day and month),WWandWthe first day of their week,DDDthe month and the day not given, andIYYYwithIW,IDorIDDDan ISO week date.D,DAY,DYandQare read and otherwise ignored, butDAYandDYare the day of anIWweek.MS,USandFF1toFF6are fractions of a second (.5is 500 ms), andFFnrounds to n digits - Errors are Postgres's messages, without its details and hints: a
field given twice with different values (
conflicting values for "MM" field in formatting string), Gregorian and ISO week patterns together (invalid combination of date conventions), text a pattern can't read (invalid value "xx" for "MM"), and fields out of range (date/time field value out of range: "2024-02-30"). The results have the driver's date and timestamp ranges (below), which go further back than Postgres's 4714 BC - Upgrading from v0.0.12 and earlier: they read only
YYYY(alsoIYYY),YY,MM,MONTH/MON,DD,HH24,HH12/HH,MI,SS,MS,USandAM/PM, unlike Postgres: a missing year was 1970,MSandUSread a count (.5was 5 ms),MONandMONTHtook both forms, and text after the format, or a missing separator, was an error
- Reading: a number is as wide as its pattern when another number
follows it (
- Time zones, as in Postgres:
x AT TIME ZONE zone(alsoTIMEZONE(zone, x)) reads aTIMESTAMPas a local time inzoneand gives that instant, aTIMESTAMP WITH TIME ZONE, and gives the local time inzoneof aTIMESTAMP WITH TIME ZONE, aTIMESTAMP. ADATEand text are read as aTIMESTAMP WITH TIME ZONE(local times in the session time zone), and the precision is kept. ATIME WITH TIME ZONEgives the time of the same instant inzone, with the offsetzonehas at the statement's time, wrapped into a day (TIMETZ '10:00+02' AT TIME ZONE 'America/New_York'is04:00:00-04in July), and aTIMEis first read as a time with time zone in the session time zone, as Postgres casts it (TIME '10:00' AT TIME ZONE 'UTC'is14:00:00+00in a New York session in July).x AT LOCAL(alsoTIMEZONE(x)) converts to or from the session time zone. Up to v0.0.12 aTIMEwas an error (function timezone(varchar, time) does not exist)AT TIME ZONEbinds tighter than*and+and looser than::and unary minus, left to right, so dbt_date'scast(cast(x as timestamp) at time zone 'UTC' at time zone 'America/Los_Angeles' as timestamp)converts from UTC to Los Angeles timeCONVERT_TIMEZONE(source, target, x)(Snowflake, Redshift; what dbt_date'sconvert_timezoneandnow()emit) readsxas a local time insourceand gives the local time intarget, aTIMESTAMP. ATIMESTAMP WITH TIME ZONEis read as its UTC time, as Snowflake reads it.CONVERT_TIMEZONE(target, x)readsxin UTC and also gives aTIMESTAMP, as in Redshift (Snowflake gives aTIMESTAMP_TZ, which has no equivalent here)- Zones are looked up in Postgres's order. First the time zone
abbreviations: the 195 of Postgres's default set
(
timezone_abbreviations = 'Default'), in any case. Most are fixed offsets:UTC,GMT,Z,EST/EDT,PST/PDT,CET/CEST,MSD,EAT,JST,AEST/AEDT,NZST/NZDT, …, soCETis UTC+1 in July too.ISTis Israel Standard Time, UTC+2, as in Postgres; India's zone isAsia/Kolkata. The rest are tied to a zone, as in Postgres:MSKis the offset it had inEurope/Moscowat that instant (UTC+4 in 2012, UTC+3 now), and one that the tz database no longer uses is the zone's offset (SGTisAsia/Singapore's, which it writes+08). Then the IANA zone names (America/New_York,Etc/GMT+5), case-insensitive, from the tz database built into the driver. Then POSIX time zones:UTC+5,+05:30,5or<+0530>-05:30, whose sign is west of Greenwich, as in POSIX and Postgres, soUTC+5and+05:30are behind UTC. A POSIX zone can have daylight saving time:CET-1CEST,M3.5.0,M10.5.0/3is UTC+1, and UTC+2 from the last Sunday of March at 02:00 to the last Sunday of October at 03:00. Its rules are as in Postgres: daysMm.w.d,Jn(without February 29) orn, times outside a day (M3.2.0/-1,M10.4.6/26), any case, and without rules (XYZ1ABC) the US rules, Postgres's default. AnINTERVALis an offset east of Greenwich, as in ISO 8601 (INTERVAL '+05:30'is ahead of UTC), without months or days (interval time zone "1 day" must not include months or days). Anything else istime zone "x" not recognized. Timestamp text with a zone name ('2024-03-10 02:30 America/Los_Angeles','2024-07-01 12:00 MSK') reads the zone the same way, except a POSIX zone with rules, which Postgres splits at its commas. Up to v0.0.12 the driver had 38 of the abbreviations, andWEST(UTC+1), which isn't one of Postgres's - Daylight saving time as in Postgres, in an IANA zone or a POSIX one: a
local time that the spring-forward transition skips is read with the
offset before it (
TIMESTAMP '2024-03-10 02:30' AT TIME ZONE 'America/Los_Angeles'is 10:30 UTC), and one that the fall-back transition repeats with the offset after it (TIMESTAMP '2024-11-03 01:30'is 01:30 PST, 09:30 UTC)
- Session time zone (
SET TIME ZONE,SET timezone = …, theadbc.redis.time_zoneoption; UTC by default). Each connection has its own. As in Postgres, aTIMESTAMP WITH TIME ZONEis an instant, and the session time zone is where it is shown and read as a local time: - Text: casts to text,
||,CONCAT,STRING_AGG,LIKEand the other functions that read text,TO_CHAR(TZ,OF,TZH,TZM) and JSON write its local time with the zone's offset at that instant:2024-01-09 21:00:00-08and2024-07-01 11:45:30.25-07inAmerica/Los_Angeles,2024-01-10 10:30:00+05:30inAsia/Kolkata. - Input: text without an offset or zone (
'2024-01-10 02:00',TIMESTAMPTZ '2024-01-10 02:00', a bound string parameter, a value written to a column, also by bulk ingest) is a local time; text with an offset or a zone name keeps it. A time with time zone without an offset ('10:00',TIMETZ '10:00'), and aTIMEconverted to one, take the zone's offset on the current date, as in Postgres:10:00:00-04in New York in July,10:00:00-05in January. - Text read as a
TIMESTAMPis the date and time written, in every session time zone: an offset or a zone name is checked and ignored, as Postgres'stimestamp_inignores it (and aDATE's; see Casts).TIMESTAMP '2024-01-05 10:00:00-05'andCAST('2024-01-05 10:00:00 Asia/Tokyo' AS TIMESTAMP)are2024-01-05 10:00:00. The same goes for text compared with aTIMESTAMPcolumn (ts = '2024-01-05 10:00:00-05', also in the index query), written to one (INSERT,UPDATE,DEFAULT, bound parameters, bulk ingest) or checked against one.- Upgrading from v0.0.14 and earlier: such text was converted to
UTC, so
TIMESTAMP '2024-01-05 10:00:00-05'was2024-01-05 15:00:00,ts = '2024-01-05 10:00:00-05'matched the rows at 15:00, and such text written to aTIMESTAMPcolumn was stored as its UTC time. Rows written that way keep it. Views,DEFAULTs andCHECKs keep the text they were defined with, so ones defined by earlier versions read it the new way.
- Upgrading from v0.0.14 and earlier: such text was converted to
UTC, so
- Conversions: a
TIMESTAMPbecomes aTIMESTAMP WITH TIME ZONEas a local time, and back as its local time; aDATEis local midnight, and a timestamp with time zone'sDATEandTIMEare its local date and time. These also apply where Postgres converts implicitly: comparisons (tz_col = TIMESTAMP '…',tz_col >= current_date),IN,BETWEEN,GREATEST/LEAST,COALESCE,CASE,UNION, joins, and writes to a column of the other type.x AT LOCALconverts to or from the session time zone. - Bulk ingest converts as
INSERTdoes: an Arrow string without an offset, an Arrow timestamp without a zone and an Arrow date written to aTIMESTAMP WITH TIME ZONEcolumn are local times, and an Arrow timestamp with a zone written to aTIMESTAMP,DATE,TIMEor text column is its local time. Arrow timestamps with a zone, and text with an offset, written to aTIMESTAMP WITH TIME ZONEcolumn keep their instant; text with an offset written to aTIMESTAMPorDATEcolumn is the date and time written, its offset ignored (see above). Up to v0.0.11, bulk ingest converted in UTC. - Fields and arithmetic:
EXTRACT/DATE_PARTand the field functions,DATE_TRUNC(a day or longer starts at local midnight; a shorter unit keeps the offset, so01:30 PSTtruncates to01:00 PSTon a fall-back day),AGE,DATEDIFF/DATE_DIFF,LAST_DAYandTO_CHARread the local time. Adding an interval adds its months and days to the local time and its time as elapsed time, so across the spring-forward transition+ INTERVAL '1 day'is 23 hours and+ INTERVAL '24 hours'24 (alsoDATEADD,GENERATE_SERIESand windowRANGEoffsets).DATE_BIN's stride is elapsed time; only its origin is read as a local time. - The current time:
CURRENT_DATE,LOCALTIMESTAMP,CURRENT_TIMEandLOCALTIMEare local (CURRENT_TIMEwith the zone's offset);NOW()is the instant.MAKE_TIMESTAMPTZandTO_TIMESTAMP(text, format)read a local time, unless the format hasTZHorTZM. - Daylight saving time, as in Postgres: a local time that the
spring-forward transition skips is read with the offset before it
(
'2024-03-10 02:30'in Los Angeles is2024-03-10 03:30:00-07), and one that the fall-back transition repeats with the offset after it ('2024-11-03 01:30'is01:30:00-08). - What it doesn't change: stored values (instants), Arrow results (a
TIMESTAMP WITH TIME ZONEcolumn is an Arrow timestamp with time zoneUTC, with the same values in every session time zone), andCONVERT_TIMEZONE, which reads a timestamp with time zone as its UTC time (as Snowflake does). - Index queries work on UTC instants: a local-time constant is converted in the session time zone before it is pushed down (see "How SQL is executed"), so the index returns the rows the comparison does.
- Views, defaults and
CHECKs read a local-time literal once, in the session time zone of the statement that defines them (CREATE VIEW,CREATE TABLE,ALTER TABLE), as Postgres does. The literal is stored as its instant, with that zone's offset:'2024-01-01 00:00'in New York is stored as'2024-01-01 00:00:00-05', whichinformation_schemashows (Postgres'spg_get_viewdefshows'2024-01-01 00:00:00-05'::timestamp with time zone). So a view created in New York returns the same rows in every session, and aCHECK (ts >= '2024-01-01')defined there rejects the same rows in every session. A local-time literal is text without an offset that is cast to a timestamp with time zone or a time with time zone (TIMESTAMPTZ '…','…'::timestamptz,TIMETZ '…','…'::timetz) or read as one: compared with one (also inIN,BETWEENand aCASEoperand), a result of aCASEorIIFor an argument ofCOALESCE,GREATEST,LEASTorNULLIFwith one, the operand ofAT TIME ZONEandAT LOCAL, and theDEFAULTof such a column. A time with time zone is stored with the offset the zone has that day ('10:00'in New York in July is'10:00:00-04'), as Postgres stores'10:00:00-04'::time with time zone. The conversions the definition makes when it runs read the zone of the session that runs it, as in Postgres: casts of columns and of other values (ts::date,DATE '2024-01-01'::timestamptz, aTIMESTAMPcompared with a timestamp with time zone),CURRENT_DATE,LOCALTIMESTAMPand text output.- Upgrading from v0.0.12 and earlier: they read such a literal in
the zone of the session that queried the view or wrote the row.
Views, defaults and
CHECKs they stored keep doing so; define them again (CREATE OR REPLACE VIEW,ALTER TABLE … ALTER COLUMN … SET DEFAULT,DROP CONSTRAINTandADD CONSTRAINT) to store the instant.
- Upgrading from v0.0.12 and earlier: they read such a literal in
the zone of the session that queried the view or wrote the row.
Views, defaults and
- Values: the forms in the
TimeZonerow of the parameter table under "Transactions and settings". An IANA name is looked up case-insensitively and shown as the tz database spells it (america/new_yorkisAmerica/New_York), and a POSIX time zone in upper case (CET-1CEST,M3.5.0,M10.5.0/3, with daylight saving time). Here, as in Postgres, abbreviations aren't read, soSET TIME ZONE 'CET'is the zone, with summer time (whileAT TIME ZONE 'CET'is the abbreviation, UTC+1). Numbers andINTERVALs are east of Greenwich, as in Postgres'sSET TIME ZONE(-8andINTERVAL '-08:00' HOUR TO MINUTEare 8 hours behind UTC), while a POSIX time zone is west of it (UTC+5is 5 hours behind). An unknown zone, and an abbreviation that isn't a zone name (PST,IST), areinvalid value for parameter "TimeZone": "x", as in Postgres. Up to v0.0.12 the driver read such an abbreviation as a fixed offset. - Differences from Postgres:
MAKE_TIMESTAMPTZwith a zone argument isn't supported. - Intervals and date/time arithmetic:
INTERVAL '1 year 2 months 3 days 04:05:06','1.5 hours','2 days ago','3 04:05:06','1-2'(years-months), ISO 8601'P1Y2M3DT4H'(also in the alternative format,'P0001-02-03T04:05:06'),INTERVAL '2' HOUR,INTERVAL '1-2' YEAR TO MONTH,INTERVAL '1.5' SECOND(0),INTERVAL(0) '1.5',INTERVAL 7 DAY/INTERVAL ? DAY, andCAST('7 days' AS INTERVAL)- A literal is read as Postgres 16 reads it. Each field's integer part
is exact, and its fraction cascades into the smaller units: a month
is 30 days, a week 7 days and a day 24 hours (
'1.5 months'is1 mon 15 days,'0.1 mon'is3 days), and a fraction of a year, decade, century or millennium is whole months, rounded half to even ('0.125 years'is2 mons). The time is rounded to microseconds, as Postgres's always is, though an interval holds nanoseconds: the fraction of a unit rounds half a microsecond toward zero ('0.0000015 seconds'is00:00:00.000001,'1.0000005 sec'is00:00:01), and a clock's fraction of a second rounds half to even ('00:00:00.0000015'is00:00:00.000002). So doesninINTERVAL n UNIT. Bound and ingested Arrowmonth_day_nano_intervalvalues keep their nanoseconds - A clock with a fraction and two fields is minutes and seconds
(
'1:30.5'is00:01:30.5). Its minutes go up to 59 and its seconds to 60, and the months of years-months to 11: past them, it isinterval field value out of range: "1:75:00", as in Postgres - The fields are read from right to left, as in Postgres, so that a
unit comes before its number: a number without a unit has the unit
of the number to its right, but after hours or a clock it is days
(
'1 2 hours'is1 day 02:00:00,'1-2 years'is1 year 2 mons), and the rightmost is seconds ('1 day 5'is1 day 00:00:05). Each unit is given once:'1 day 2 days','01:00 02:00'and'1.5 seconds 3 microseconds'(a fraction of a second is its milliseconds and microseconds too) areinvalid input syntax for type interval: "…", as is'1 2'.agomay be anywhere ('1 day ago 2 hours'is-1 days -02:00:00). The units are Postgres's, which includec,cent,dec,mil,usecondandmsecond, and punctuation between fields is a separator ('@ 1 day, 2 hours'). An ISO 8601 number is read as C'sstrtodreads it, also in hexadecimal ('P0x10Y'is16 years) - With fields (
INTERVAL '…' HOUR,DAY TO MINUTE,MINUTE TO SECONDand Postgres's other qualifiers), the rightmost number without a unit is the last field, and the fields after it are dropped, the time truncated toward zero, as in Postgres:INTERVAL '1.5' HOURis01:00:00,INTERVAL '1 day 02:30' HOURis1 day 02:00:00, andINTERVAL '1:30' MINUTE TO SECONDis00:01:30.SECOND(p)andINTERVAL(p) '…'round the time topdecimals, half away from zero.INTERVAL n HOURandINTERVAL ? HOURdrop them too (INTERVAL 1.5 HOURis01:00:00) - Differences from Postgres:
INTERVAL n UNITwith an expression, andINTERVAL 'n' UNITwith a unit that isn't a qualifier's field (HOURS,WEEK,QUARTER), which keeps its fraction (INTERVAL '1.5' HOURSis01:30:00), are driver extensions; Postgres readsINTERVAL '1.5' hoursas an interval of 1.5 seconds namedhours. The qualifier of a column or a cast (CAST(x AS INTERVAL HOUR)) is ignored - Upgrading from v0.0.12 and earlier: the fields were read from
left to right and added up:
'1 2 hours'was02:00:01,'1 2'00:00:03,'1-2 years'an error, and a unit given twice was summed ('1 day 2 days'was3 days). The driver also read quarters ('1 quarter'was3 mons), lower-case ISO 8601 ('p1d'), ISO 8601 text with spaces around it (CAST(' P1D' AS INTERVAL)),'1.seconds','-.5 hours','ago'(00:00:00) and'1 ago', andagoonly at the end, but not'1 c','1d2h'or'@ 1 day'.INTERVAL '1.5' HOURandINTERVAL 1.5 HOURwere01:30:00,INTERVAL '1 day 02:30' HOURwas "INTERVAL HOUR needs a number",INTERVAL '1e3' SECONDwas00:16:40, and any units were accepted aroundTO(YEAR TO DAY). Text that isn't an interval wasinvalid interval "…"(with the same SQLSTATE, 22007), and a field past its part of the interval ('2147483648 days') was "interval out of range" (22008), notinterval field value out of range: "…"(22015) - Upgrading from v0.0.12 and earlier: the fields were summed as
nanoseconds in doubles, so a literal's time past about 2,500 hours
wasn't exact (
'1000000:00:00.000001'was1000000:00:00.000001024), and digits past microseconds were kept ('0.0000015 seconds'was00:00:00.0000015). A fraction of a year cascaded into days ('0.05 years'was18 days; it is1 mon),'1:30.5'was 1 hour 30.5 minutes,'1:75:00'and'1-12'were accepted, and the ISO 8601 alternative format was an error. No literal is exactly -2^63 nanoseconds any more:'-9223372036.854775808 seconds'rounds to-2562047:47:16.854776(Postgres's value), past the nanoseconds' range, so it is "interval out of range"
- A literal is read as Postgres 16 reads it. Each field's integer part
is exact, and its fraction cascades into the smaller units: a month
is 30 days, a week 7 days and a day 24 hours (
timestamp ± interval(a timestamp with time zone adds months and days in the session time zone),date ± interval(gives a timestamp),date ± integer(days),date - date(days),timestamp - timestamp(an interval of days and time),time ± interval,time - time,timetz ± interval(the interval's time part, wrapped around midnight; the offset is kept),date + timetz(a timestamp with time zone: the time at its offset on that date),interval ± interval,interval * n,interval / n,-interval- Months follow the calendar and clamp to the end of the month
(
TIMESTAMP '2024-01-31' + INTERVAL '1 month'is Feb 29). Comparisons treat a month as 30 days and a day as 24 hours, soINTERVAL '1 mon' = INTERVAL '30 days'andINTERVAL '1 day' = INTERVAL '24 hours', and so doORDER BY,GROUP BY,DISTINCT, joins and set operations, as in Postgres (seeGROUP BY) interval * nandinterval / nwork as in Postgres, withnas aDOUBLE PRECISION: each field is multiplied (or divided) and truncated, the fraction of a month becomes days, 30 to a month, rounded to millionths of a day, the fraction of a day becomes time, and the time is rounded to microseconds, half to even. SoINTERVAL '1 sec' / 3is00:00:00.333333,INTERVAL '1 mon' / 7is4 days 06:51:25.6896(30 / 7 is 4.285714 days), andINTERVAL '1 mon 3 days' / 4is8 days 06:00:00. The time never carries into days:INTERVAL '29 days 23:59:59.999999' * 0.5is14 days 24:00:00. Months or days past 32 bits (also once a cascaded fraction adds a day), a NaN and an infinitenare "interval out of range" (dividing by an infinity is 0), and/ 0is "division by zero"- An interval's time is nanoseconds here and microseconds in
Postgres. A time that isn't whole microseconds (a bound or ingested
Arrow
month_day_nano_interval, or the difference of twoTIMESTAMP(9)s orTIME(9)s) counts with its fraction, and the result is whole microseconds, so even* 1rounds it: 2.5 µs × 1 is00:00:00.000002. The result must fit the nanoseconds' range, about ±2,562,047 hours, 1000 times shorter than Postgres's - The doubles are computed as Postgres does on x86-64. The arm64
build in the
postgres:16Docker image fuses multiply-adds (its compiler's default), so thereinterval * nandPERCENTILE_CONTcan differ in the last microseconds when the fields are large:INTERVAL '2147483647 mons' * 0.3is53687091 years 2 mons 3 days 00:00:00.0864on x86-64 and53687091 years 2 mons 2 days 23:59:59.9136on arm64 - Upgrading from v0.0.12 and earlier: the fractions cascaded in
nanoseconds and weren't rounded:
INTERVAL '1 sec' / 3was00:00:00.333333333andINTERVAL '1 mon' / 74 days 06:51:25.714285714.PERCENTILE_CONTandMEDIANof intervals changed with them (see Aggregates)
- An interval's time is nanoseconds here and microseconds in
Postgres. A time that isn't whole microseconds (a bound or ingested
Arrow
- A result outside its type's range is an error, as in Postgres, rather
than wrapping. A
DATEis an Arrowdate32(5877642-06-23 BC, year -5877641, to 5881580-07-11): past it,date ± integer,MAKE_DATE,DATEADD,TO_DATE, the other date functions and casts toDATEgive "date out of range". A timestamp past the range of its unit (microseconds: about ±292,000 years from 1970) is "timestamp out of range". An interval's time part is nanoseconds in 64 bits (about ±2,562,047 hours); past that, and for a months or days count beyond 32 bits, it is "interval out of range" AGE(a, b)/AGE(x)(years, months, days and time), andEXTRACTandTO_CHARon intervalsSUM,AVG,MINandMAXof intervals, also as window functions, are intervals (see Aggregates)- Intervals are returned as Arrow
month_day_nano_interval; Arrow interval and duration values can be bound and ingested - JSON stored as text in
VARCHARcolumns (there is no JSON column type). Documents are parsed by the driver in each call, on the rows the index returns; nothing JSON-specific is pushed down. Semantics follow Postgres: - Operators:
doc -> 'key'anddoc -> n(an array element; negativencounts from the end) return JSON,doc ->> …returns text (a string's value, NULL for JSON null);doc #> '{a,0,b}'and#>>follow a path given as a text array. As in Postgres they share one precedence level with||, left to right, below+/-and above comparisons:doc ->> 'a' || '!'is(doc ->> 'a') || '!', while'x' || doc ->> 'a'applies->>to the concatenation JSON_EXTRACT_PATH(doc, key, …),JSON_EXTRACT_PATH_TEXT,JSON_TYPEOF,JSON_ARRAY_LENGTH;x::json/CAST(x AS JSON)checks the text,x::jsonbnormalizes it (both giveVARCHAR)- These read the json type: a value keeps its text from the document, so
numbers are never rounded (
'{"n": 1e400}' ->> 'n'is1e400) and of duplicate keys the last one wins. Malformed JSON is an error,invalid input syntax for type json: …with Postgres's detail, and so are\uescapes Postgres can't decode (code point 0, unpaired surrogates).doc IS JSONguards against bad rows - The
JSONB_spellings (JSONB_EXTRACT_PATH,JSONB_TYPEOF,JSONB_BUILD_OBJECT,JSONB_AGG, …),TO_JSONB,::jsonbandRETURNING JSONBgive jsonb's text: keys sorted (shorter first) without duplicates, numbers as exactNUMERIC(1e2is100,1.50stays1.50), and{"a": 1}spacing. Up to v0.0.12TO_JSONBgave JSON's text x IS [NOT] JSON [VALUE | SCALAR | ARRAY | OBJECT] [{WITH | WITHOUT} UNIQUE [KEYS]]is false for malformed text and NULL for NULL- SQL/JSON query functions, as in Postgres 17 (documents are read as
jsonb):
JSON_VALUE(doc, path [RETURNING type] [behavior ON EMPTY] [behavior ON ERROR])withERROR,NULLorDEFAULT expr;JSON_QUERY(doc, path [RETURNING type] [{WITHOUT | WITH [CONDITIONAL | UNCONDITIONAL]} [ARRAY] WRAPPER] [{KEEP | OMIT} QUOTES] …), which also allowsEMPTY [ARRAY]andEMPTY OBJECT;JSON_EXISTS(doc, path [{TRUE | FALSE | UNKNOWN | ERROR} ON ERROR]). The defaults areNULL ON EMPTYandNULL ON ERROR(FALSE ON ERRORforJSON_EXISTS).ON ERRORcovers a malformed document, a strict-mode path error, a result of the wrong shape (several items, or an object forJSON_VALUE) and a failed conversion to theRETURNINGtype; an invalid path is always an error - Paths:
[lax | strict] $followed by.key,."key",.*,[n],[last],[last - n],[a to b], lists like[0, 2 to last], and[*]. Lax mode (the default) applies.keyto each element of an array, treats a non-array as an array of one for[n], and skips missing keys and out-of-range subscripts; strict mode makes them errors. Filters (?(…)), item methods (.size(), …), arithmetic,.**andPASSINGvariables are not supported - Constructors:
JSON_BUILD_OBJECT(k, v, …),JSON_BUILD_ARRAY(…),JSON_OBJECT(k VALUE v | k : v, … [{NULL | ABSENT} ON NULL] [{WITH | WITHOUT} UNIQUE [KEYS]] [RETURNING JSON | JSONB | VARCHAR])(and Postgres'sJSON_OBJECT('{k,v,…}')/JSON_OBJECT(keys, values)over text arrays),JSON_ARRAY(v, … [{ABSENT | NULL} ON NULL] [RETURNING …]),TO_JSON(x). Values are encoded as Postgres'sto_jsondoes: numbers and booleans as such, NULL asnull, strings escaped, dates and timestamps as ISO 8601 strings ("2024-01-15T10:30:00+00:00"), binary values as"\\x…", and with Postgres's spacing ({"a" : 1},[1, 2]) - Aggregates, computed by the driver:
JSON_AGG(x [ORDER BY …])andJSON_OBJECT_AGG(k, v [ORDER BY …]), also withDISTINCT(whose values come out sorted). NULL values arenull, a NULL key is an error, and no rows give NULL.ORDER BYputs NULLs last withASCand first withDESC. They are not available as window functions - Results are
VARCHAR. An argument is embedded as JSON when it is itself JSON: a JSON function,->,#>or::json. Any other text becomes a JSON string, including JSON read back from a table, view or subquery, so writecol::jsonto embed it - Differences from Postgres: the SQL/JSON functions apply
ON ERRORto a malformed document too (Postgres raises the error when it converts the text to jsonb) - Not supported: the other jsonb operators (
@>,?,-), set-returning functions (JSON_EACH,JSON_ARRAY_ELEMENTS,JSON_TABLE),JSONB_SETand the other modification functions,JSON_ARRAY(SELECT …), and a JSON column type (a RedisJSON-backed one is possible future work)
Additional statements and metadata¶
SELECTwithoutFROMfor literal expressionsinformation_schema(read-only, built from the driver's metadata when queried):schemata,tables(BASE TABLE/VIEW/LOCAL TEMPORARY, each table's rowkey_prefixandindex_name, NULL for views, andcomment),columns(ordinal_position,column_default,data_type,is_nullable,character_maximum_length,character_octet_length,numeric_precision,numeric_scale,datetime_precision,is_indexedandcomment),views(view_definition),table_constraintsandkey_column_usage(the primary keys andCHECKconstraints, see Constraints above), androutines, the driver's functions (see "Function calls" above).commentis theCOMMENT ONtext, NULL without one.data_typeis the driver's name of the type, with its length or precision (VARCHAR(3),TIMESTAMP(2),TIMESTAMP(6) WITH TIME ZONE), where Postgres writescharacter varyingortimestamp without time zoneand keeps the number incharacter_maximum_lengthordatetime_precision, which have it here too. Any SQL works on them, including joins[WITH …] UPDATE t [[AS] a] SET col = …, … [FROM item, …] [WHERE …]and[WITH …] DELETE FROM t [[AS] a] [USING item, …] [WHERE …](the Postgres forms). The FROM / USING items are written like a SELECT's FROM clause (tables, views, CTEs and(SELECT …), with commas or joins) and are joined with the target; SET and WHERE can read all of them. A target row that matches several FROM / USING rows is updated or deleted once. For UPDATE the matches must give the same new values, otherwise it's an error (Postgres silently uses one of them)UPDATE … SET (a, b) = …assigns a row to a column list, as in Postgres, also inUPDATE … FROMand in MERGE'sUPDATE SET. The row is(x, y, …),ROW(x, …)(also for a single column) or a subquery(SELECT x, y …), which must return as many columns ("number of columns does not match number of values") and at most one row: no row sets the columns to NULL, more than one is an error. The subquery runs once per row (once in all if it isn't correlated), not once per column. As withcol = …, every value is computed from the row before the change, soSET (a, b) = (b, a)swaps[WITH …] MERGE INTO t [[AS] a] USING item [[AS] s] ON condfollowed by any number ofWHEN MATCHED [AND cond] THEN UPDATE SET … | DELETE | DO NOTHINGWHEN NOT MATCHED [BY TARGET] [AND cond] THEN INSERT [(cols)] VALUES (…) | INSERT DEFAULT VALUES | DO NOTHINGWHEN NOT MATCHED BY SOURCE [AND cond] THEN UPDATE SET … | DELETE | DO NOTHING
The source is a table, view, CTE or (SELECT …). Each source row with
its matching target row, each unmatched source row and (with BY SOURCE
clauses) each unmatched target row takes the first clause of its kind
whose condition holds, as in Postgres. WHEN NOT MATCHED clauses see only
the source's columns (so VALUES (id, name) reads the source), BY
SOURCE clauses only the target's. Changing a target row for more than one
source row is an error ("MERGE command cannot affect row a second time");
matching more than one is fine if only one match changes it. NULL keys
never match. The result is the number of rows inserted, updated and
deleted. INSERT gives the columns it leaves out (or sets to DEFAULT)
their defaults, and INSERT DEFAULT VALUES inserts a row of defaults
- RETURNING … at the end of INSERT, UPDATE, DELETE and MERGE,
written like a select list: *, t.*, and expressions with optional
aliases, including subqueries (aggregates and window functions are not
allowed, as in Postgres). It reads the target's columns, and in
UPDATE … FROM, DELETE … USING and MERGE the other items' too. As in
Postgres, * lists the target's columns first, then the FROM / USING
items'; in MERGE, the source's columns, then the target's. The
statement returns one row per row it changed (INSERT: in VALUES or
query order; otherwise in no particular order):
- INSERT: the new row as stored (cast to the column types);
RETURNING __rowid gives the row id it was given
- UPDATE: the row after the change
- DELETE: the deleted row
- MERGE: each row inserted, updated or deleted, with the source row it
came from (NULLs for WHEN NOT MATCHED BY SOURCE); merge_action()
(Postgres 17) is 'INSERT', 'UPDATE' or 'DELETE'
The list is computed with the other checks, before anything is written, so
an error in it leaves the table untouched, and its subqueries see the
tables as they were before the statement. ExecuteQuery returns the rows
(with the RETURNING columns, also when no row changed); ExecuteUpdate
runs the statement and returns the number of rows changed, as Postgres
drivers do; ExecuteSchema returns the columns without running it. Without
RETURNING, ExecuteQuery on these statements still returns an empty
result with no columns, and the number of rows changed. __rowid can't be
read in the RETURNING list of UPDATE … FROM, DELETE … USING or
MERGE (as in any join), and merge_action() can't be called inside a
subquery
Transactions, limitations, and types¶
- Transactions and settings. The driver is autocommit only: every statement
commits on its own. SQL written for Postgres still sends transaction
control and
SET(dbt sends a literalcommit;before hooks that run outside the transaction, andsql_headeroften holdsSETs), so they are accepted as Postgres accepts them in autocommit mode: - Transaction control does nothing:
BEGIN [WORK | TRANSACTION] [mode, …],START TRANSACTION [mode, …],COMMIT [WORK | TRANSACTION] [AND [NO] CHAIN],END …,ROLLBACK [WORK | TRANSACTION] [AND [NO] CHAIN]andABORT …, and alsoSET TRANSACTION mode, …andSET SESSION CHARACTERISTICS AS TRANSACTION mode, …. The modes (ISOLATION LEVEL …,READ ONLY,READ WRITE,[NOT] DEFERRABLE) are accepted and ignored, soREAD ONLYdoesn't stop writes.ROLLBACKundoes nothing: inBEGIN; INSERT …; ROLLBACKthe row stays, because theINSERTcommitted. Postgres warns when there is no transaction to commit or roll back; ADBC has no way to return warnings, so nothing is reported.COMMIT AND CHAINoutsideBEGINis Postgres's errorCOMMIT AND CHAIN can only be used in transaction blocks. - Not supported:
SAVEPOINT,RELEASE [SAVEPOINT],ROLLBACK TO [SAVEPOINT],COMMIT PREPARED/ROLLBACK PREPAREDandSET TRANSACTION SNAPSHOT(SAVEPOINT is not supported (autocommit only), …). The error comes when the script is parsed, so none of its statements run. SET [SESSION | LOCAL] name {= | TO} {value [, …] | DEFAULT},RESET name,RESET ALL,SHOW nameandSHOW ALL, alsoSET TIME ZONE …,RESET TIME ZONE,SHOW TIME ZONE,SET SCHEMA 'name'(search_path) andSET NAMES 'name'(client_encoding), for the parameters below. Names are case-insensitive. Other names, Postgres's customa.bones included, giveunrecognized configuration parameter "x".SHOWreturns oneVARCHARcolumn named as in Postgres (DateStyle,TimeZone, …);SHOW ALLreturnsname,settinganddescription. Settings belong to the connection and last until it closes;ROLLBACKdoesn't undo them either.SET LOCALlasts until the transaction ends, as in Postgres: untilCOMMIT,END,ROLLBACKorABORTafter aBEGIN(the connection remembers that aBEGINis open, for this only), or else until the end of the script it is in (Postgres runs a script of several statements as one transaction). ASET LOCALstatement on its own, outsideBEGIN, is checked and has no effect, as in Postgres.-
Parameters:
Parameter Values Default search_pathSchemas, comma-separated. Unqualified names use the first one, skipping "$user",pg_catalogandpg_temp(temporary objects are always found first), asadbc.redis.default_schemasets it:CREATE, queries, writes and bulk ingest. The schema needn't exist yet (schemas exist once they have a table).SHOWshows the list as set, and the ADBC current schema (adbc.connection.db_schema) is the same settingadbc.redis.default_schemaTimeZone(SET TIME ZONE)The session time zone (see Session time zone): an IANA zone name (case-insensitive, shown as the tz database spells it), a POSIX time zone ( UTC+5, west of Greenwich, or with daylight saving time,CET-1CEST,M3.5.0,M10.5.0/3; shown in upper case), a number of hours east of Greenwich (-8, shown<-08>+08), orINTERVAL '+05:30' [HOUR TO MINUTE](east, shown<+05:30>-05:30).LOCALandDEFAULTare the default. Others giveinvalid value for parameter "TimeZone": "…", also a time zone abbreviation (PST), as in Postgresadbc.redis.time_zone, orUTCclient_encodingOnly UTF8(UTF-8,UNICODE)UTF8application_nameAny; not used ''standard_conforming_stringsOnly on:'…'strings don't treat backslashes as escapesonstatement_timeout,lock_timeout,idle_in_transaction_session_timeoutMilliseconds, or a number with a unit ( us,ms,s,min,h,d), shown as Postgres shows them (5s,1500ms). Not enforced: there is no statement timeout (and there are no locks or transactions)0extra_float_digits-15 to 3; no effect, as results are Arrow values, not text 1DateStyleOnly the ISOstyle, with an orderMDY,DMYorYMD, which has no effect (only ISO dates are read)ISO, MDYIntervalStyleOnly postgres, the format of intervals cast to textpostgresThe same settings through ADBC options are not supported, other than the current schema and the time zone (
adbc.redis.time_zone). - One-dimensionalARRAY[...]expressions,ARRAY_AGG(including ORDER BY),ANY/SOME/ALLcomparisons against arrays,ARRAY_LENGTHandCARDINALITYare supported for catalog queries and in-memory results. Set-returning functions (GENERATE_SERIES,UNNEST,GENERATE_SUBSCRIPTS,STRING_TO_TABLE,REGEXP_SPLIT_TO_TABLE,REGEXP_MATCHES) work in FROM and SELECT lists, with lateral arguments, lockstep NULL padding,WITH ORDINALITYandROWS FROM. CASE/COALESCE and aggregate arguments reject SRFs. One-dimensional scalar arrays support storedT[],T[n]andT ARRAYcolumns, Arrow list ingestion/parameters, text casts, subscripts/slices, containment and concatenation, and PostgreSQL array manipulation functions. Multidimensional arrays and non-default lower bounds are not supported. - Not supported: mutually recursive CTEs,ANY/ALLover bare value lists, JSON set-returning functions,RETURNING OLD.* / NEW.*(Postgres 18),GROUP BY DISTINCT(which drops repeated grouping sets),GROUPING()of an enclosing query's columns inside a subquery,PERCENTILE_CONT/PERCENTILE_DISCof an array of fractions, and the ordered-set aggregates andMEDIANas window functions - Types:BOOLEAN, SMALLINT, INTEGER, BIGINT, REAL, DOUBLE PRECISION, NUMERIC(p,s), VARCHAR(n), CHAR(n), VARCHAR/TEXT, UUID, VARBINARY/BLOB, DATE, TIME(p) [WITH TIME ZONE], TIMESTAMP(p) [WITH TIME ZONE], INTERVAL(interval and time with time zone columns are stored but not indexed; see String lengths forVARCHAR(n)/CHAR(n)). ATIMESTAMP WITH TIME ZONEis a UTC instant (an Arrow timestamp with time zoneUTC), shown and read as a local time in the session time zone. JSON documents are stored inVARCHARcolumns (NOINDEXif they are never compared as a whole);JSONandJSONBare only cast targets. A row value has typeRECORD, which no column can have, and is returned as an Arrow struct (see Rows as values) - Time with time zone:TIME(p) WITH TIME ZONE(alsoTIMETZ(p)) is Postgres's: a time of day and a UTC offset, kept as written, with no date.pis 0 to 6 (6 without it; apabove 6 is 6, as in Postgres), and a value is rounded to it as aTIME(p)is. - Text in: a time and an offset ('10:00+02','10:00:00.5-05:30','10:00 +0530','10:00Z', up to±15:59:59) or an abbreviation (EST,CEST,MSK). A zone name needs a date before the time, whose offset it takes ('2024-07-01 10:00 America/New_York'is10:00:00-04), unless the zone has only ever had one offset (Etc/GMT+5);'10:00 America/New_York'isinvalid input syntax for type time with time zone, as in Postgres. Without an offset, the session time zone's on the current date (see Session time zone). Text out:10:00:00+02,10:00:00.5-05:30- Comparisons, as Postgres's: by the time in UTC, then larger offsets first. So'10:00+02' = '09:00+01'is false and'10:00+02' < '09:00+01';'23:00-05'(04:00 UTC the next day) sorts after'24:00+00'.ORDER BY,GROUP BY,DISTINCT, joins,IN,MIN/MAXand windowRANGEframes (the time ± the interval, with the row's offset) compare that way. ATIMEcompared with one is read as one in the session time zone - Conversions: aTIMEtakes the session time zone's offset on the current date, aTIMESTAMP WITH TIME ZONEbecomes its local time and offset in the session time zone, and a time with time zone becomes aTIMEby dropping its offset (also when written to aTIMEcolumn). ATIMESTAMP, aDATEand other types don't convert, as in Postgres.CASE,COALESCE,GREATEST,LEASTandUNIONof aTIMEand a time with time zone give a time with time zone - Arrow has no such type, so a time with time zone is a string column (utf8) of its text, whose field metadataARROW:FLIGHT:SQL:TYPE_NAME(Flight SQL's) names its type,TIME(6) WITH TIME ZONE, in query results andGetTableSchema. Postgres's own ADBC driver has no mapping either: it returns Postgres's binary form as an opaque binary column. Bound strings and ingested strings are read as text in; bulk ingest that creates a table makes a string field with that metadata a time with time zone column, so a result ingests back as the same type - Rows store the text (10:00:00+02). The index doesn't hold such a column, so comparisons with it are checked by the driver on the rows the rest of the query finds, and it is sorted and grouped by the driver -information_schema.columnsshowsdata_typetime with time zone,datetime_precision6 anddriver_data_typeTIME(6) WITH TIME ZONE;GetObjectsreports that driver spelling asxdbc_type_nameand the precision asxdbc_decimal_digits- Upgrading from v0.0.12 and earlier: they readTIME WITH TIME ZONEasTIME, dropping a value's offset ('10:00+02'was10:00:00), and had noTIMETZ. Columns they created areTIMEcolumns and stay so;CREATE TABLE … AS SELECT c::timetzor a new column makes a time with time zone column - NaN and the infinities:REALandDOUBLE PRECISIONholdNaN,Infinityand-Infinity(read from text in any case, also asinf), in every column, indexed or not (see "NaNs in the index"). As in Postgres, NaN is greater than every other number and equal to itself, in comparisons,ORDER BY,GROUP BY,DISTINCT, joins and aggregates, and as text (casts,||, JSON) they areNaN,Infinityand-Infinity, which wereNaN,+Infand-Infup to v0.0.8. Arrow results hold the doubles themselves.NUMERIChas neither (in Postgres it has both): converting one to it fails withcannot convert NaN to NUMERIC(10,2): NUMERIC values can't be NaN or infinite, and nothing is written. A value beyondREAL's range is an error, as in Postgres (value out of range: overflow; it was an infinity up to v0.0.12) - Fractional seconds:TIME(p)andTIMESTAMP(p)holdpdigits, from 0 to 9 (6 withoutp; apabove 9 is 9, where Postgres reduces apabove 6 to 6; a negativepis an error, as in Postgres). Their Arrow unit is the one with the next number of digits: seconds for 0, milliseconds for 1 to 3, microseconds for 4 to 6 and nanoseconds for 7 to 9, so aTIMESTAMP(2)is an Arrowtimestamp[ms]whose values are whole hundredths. A value with more digits is rounded half away from zero, as Postgres rounds, in one step (.1245is.12in aTIMESTAMP(2)), when it is cast to the type or written to a column of it:INSERT,UPDATE,MERGE,CREATE TABLE … AS, defaults and bulk ingest ('… 10:00:00.123456'is stored as.12in aTIMESTAMP(2)). The rounding carries into the next second, minute, day or year ('2024-12-31 23:59:59.5'is2025-01-01 00:00:00in aTIMESTAMP(0)), and aTIME(0)of23:59:59.9is24:00:00, which Postgres allows (its Arrow value is 86,400 seconds, one past the end of Arrow's range for a time of day; pyarrow reads it as 00:00:00). As in Postgres, whose timestamps count from 2000-01-01, a timestamp before 2000 exactly halfway rounds down ('1999-12-31 23:59:59.5'is23:59:59). The text, the Arrow value and what index queries compare with are the rounded value - The type keepsp: in the table's metadata,information_schema(driver_data_typeTIMESTAMP(2),datetime_precision2) andGetObjects(xdbc_type_nameTIMESTAMP(2),xdbc_decimal_digits2). A column, a cast, a scalar subquery, andCASE,COALESCE,NULLIF,GREATEST,LEASTand set operations keep it, with the most digits of their inputs, so that no value is rounded (Postgres's type has no precision, which is 6, when they differ). SoCREATE TABLE … ASand views keep the precision of a column or cast they select. Arithmetic (ts + INTERVAL '1 second'), functions, aggregates and a text literal inCASEorCOALESCEhave none, as in Postgres, and have the unit's digits (aTIMESTAMP(3)for aTIMESTAMP(2)) -TIMESTAMP(p) '…',TIMESTAMP(p) WITH TIME ZONE '…',TIMESTAMPTZ(p) '…'andTIME(p) '…'are the text cast to the type - Text with more than 6 digits read into a type of 6 or fewer is read as Postgres reads it, in microseconds with an exact half rounded to even, before it is rounded to the type ('… 10:00:00.1234565'is.123456in aTIMESTAMP,'… 10:00:00.4999995'is10:00:01in aTIMESTAMP(0)). Read into a type of 7 to 9 digits, and as a literal (TIMESTAMP '… .1234565', aTIMESTAMP(9)), it is nanoseconds, which round half away from zero like any other value; outside the nanoseconds' range (1677 to 2262) it is microseconds, read as Postgres reads them - Text compared with a time or timestamp is read with the digits written (in microseconds unless the other side is in nanoseconds), as Postgres compares at full precision, so aTIMESTAMP(0)of10:00:01equals'… 10:00:01'but not'… 10:00:00.5'- Earlier versions truncated (10:00:00.5was10:00:00in aTIMESTAMP(0)); the values they stored don't change. v0.0.8 and earlier also kept 3, 6 or 9 digits for apof 1 or 2, 4 or 5, 7 or 8, and the column's type in their metadata is that: aTIMESTAMP(2)column they created stays aTIMESTAMP(3), values and type
Tables can be qualified as schema.table or redis.schema.table, and
pg_temp.table is the connection's temporary table. Strings are written
'…' ('' for a quote) or dollar-quoted, $$…$$ or $tag$…$tag$, as in
Postgres. Schemas are key
namespaces (default public; SET search_path changes it for the
connection). String comparison uses byte order, matching a Postgres database
created with the C or POSIX collation. COLLATE "C", COLLATE "POSIX"
and COLLATE "default" are accepted on expressions and column types; other
collation names are rejected. There are no transactions (autocommit only; BEGIN, COMMIT
and ROLLBACK are accepted and do nothing, see "Transactions and settings"
above). UPDATE, DELETE and MERGE find their rows and compute and check
every change first (new values and casts, string lengths, NOT NULL, CHECK, MERGE's
one-change-per-row rule, RETURNING), so such an error leaves the table untouched. Then
they write, in pipelined batches of up to 1,000 rows (MERGE: updates, then
deletes, then inserts). If a write fails part-way, for example because the
connection drops, the batches already written stay written, and other
clients can see a partly applied statement.
PostgreSQL catalogs and session discovery¶
information_schema.columns uses PostgreSQL data_type and udt_name
spellings, with precision/length in their standard fields. New TEXT declarations
remain distinct from VARCHAR. driver_data_type preserves the exact driver
spelling, including typmods; is_indexed and comment remain extension columns.
An explicit column COLLATE "C" or "POSIX" is recorded in collation metadata.
Older TEXT declarations were saved as VARCHAR and cannot be distinguished.
Read-only pg_catalog relations expose namespaces, relations, attributes,
defaults, built-in types, CHECK/primary-key constraints, indexes, descriptions,
settings, the current database/role, functions, access methods and collations.
pg_tables, pg_views, pg_matviews, pg_indexes and pg_stat_user_tables
are also available. Unsupported domains, enums, inheritance,
dependencies and rewrite rules have empty typed catalogs. pg_stat_user_tables
reports live indexed row counts; cumulative PostgreSQL statistics are unavailable.
Catalog names are searched implicitly before the current schema.
OID aliases (oid, regclass, regtype, regproc, regnamespace) support
name/OID casts and the to_reg* lookups; missing-name lookups return NULL while
casts fail. format_type understands built-in OIDs and typmods. The
pg_get_expr, pg_get_constraintdef, pg_get_indexdef, pg_get_viewdef,
obj_description, col_description and visibility functions read metadata.
Privilege compatibility functions return true; Redis ACLs still authorize each
operation. pg_get_serial_sequence returns the owned sequence name, or NULL without one.
Relation-size functions measure Redis HASH memory (requiring +memory|usage);
pg_total_relation_size includes the search-index memory reported by FT.INFO.
These are Redis bytes rather than PostgreSQL disk pages. Physical search indexes use the access-method
name redis_search; primary-key metadata retains the documented unenforced
uniqueness semantics.
Stock SQLAlchemy PostgreSQL reflection is checked through the ADBC shared
library with go/validation/check_catalog_reflection.py: schemas, tables,
views, columns, defaults, comments, composite primary keys, indexes and CHECKs.
pg_catalog.f(...) resolves built-in calls. version() returns
PostgreSQL 16.0 (Redis ADBC driver), current_database() returns redis,
current_schema() returns the connection's selected schema, and current_user,
session_user, user and current_role return its configured Redis ACL user
(or default). pg_typeof reports the PostgreSQL type name.
current_setting and set_config use the same settings as SET/SHOW. Read-only
values are server_version=16.0, server_version_num=160000,
transaction_isolation=read committed, max_identifier_length=63,
server_encoding=UTF8, integer_datetimes=on, and is_superuser=off.
These are client compatibility values; the driver remains autocommit-only.
Options¶
Experimental CUDA execution is selected per statement with
adbc.redis.execution_backend=cuda and adbc.redis.cuda_device (default 0).
adbc.redis.gpu_metrics reports actual kernel launches/input rows. CPU remains
the default. See the supported SQL subset and C Device API
before opting in; unsupported CUDA operations return errors without fallback.
| Option | Level | Meaning |
|---|---|---|
adbc.redis.statement_snapshots |
database, connection | Always true for managed tables. Explicit true is accepted; false is rejected. See snapshots. |
adbc.redis.read_snapshot_action |
connection, write-only | acquire binds one read-only managed catalog view across metadata and sequential SELECTs; release unbinds while existing readers retain their view. Native connection only. See shared read snapshots. |
adbc.redis.read_snapshot_status |
connection, read-only | JSON state, generation and deadline while valid, or state=unbound. Unavailable bound views return an error. No attachment capability is exported. |
adbc.redis.read_snapshot_retention_ms |
connection | String or int64 milliseconds, 120000–3600000; default 900000. Set while unbound; fixed lifetime includes discovery and active reads. |
adbc.redis.foreign_indexes_refresh |
connection, write-only | Set true to refresh managed copies or unmanaged schema registrations, per selected mode and index. Does not modify source HASHes/indexes. |
adbc.redis.stream_required |
statement | false (default) permits existing materializing fallback. true requires an unordered incremental managed-table/key-source SELECT or UNION ALL, rejecting blocking plans and fallback before execution. See policy and limits. |
adbc.redis.extension_policy |
statement | reject (default) or explicit storage normalization for top-level allowlisted arrow.uuid/arrow.json fields. Canonical SQL schemas persist; unknown/nested semantics reject. See extension policy. |
adbc.redis.result_layouts |
statement | JSON map of exact result names to explicit physical Arrow layouts; SQL/table schemas remain canonical. See layout contract. |
uri |
database | redis://[user:pass@]host:port/db, or rediss://… for TLS, with go-redis's query parameters (see URI parameters) |
adbc.redis.tls_ca_file |
database | PEM file supplying exclusive TLS trust roots for new rediss:// connections, including cluster nodes. Empty (default) uses system roots. Requires certificate verification; each partition worker supplies its own local path. |
username, password |
database | Credentials (override the URI) |
adbc.redis.address, adbc.redis.db |
database | Used when no URI is given |
adbc.redis.cluster |
database | auto (default) / true / false: OSS Cluster API client or single endpoint |
adbc.redis.default_schema |
database | Schema for unqualified names (default public). SET search_path and the ADBC current schema change it for one connection; RESET search_path goes back to it |
adbc.redis.aggregate_pushdown |
database, statement | exact / all / none |
adbc.redis.query_workers |
database | Per-query ceiling: auto (default) permits measured exploration up to min(32, 2 × GOMAXPROCS); explicit values are 1 through 32. Eligible ungrouped COUNT/SUM/AVG/MIN/MAX over direct columns on driver-owned tables scan disjoint row-ID ranges. The shared worker budget may grant fewer workers. DISTINCT, FILTER, grouped queries, complex expressions and small tables retain the serial path. Exact SUM/AVG results are preserved. Each worker owns a cursor and bounded pages. |
adbc.redis.query_worker_budget |
database | Maximum active eligible aggregate scan/reduce workers across connections opened from this database handle. auto (default) starts at min(32, GOMAXPROCS) workers and adapts from completed scan measurements; a positive integer sets an explicit budget. A lone query can use the learned budget up to its ceiling. Explicit budgets remain fixed shared limits; automatic per-query grants still learn within them. Setting both options explicitly selects fixed allocation. FIFO admission assigns available tokens fairly to queued queries; no query starts without at least one token. Grants last until the query's workers finish, including error/cancellation cleanup. Lowering capacity constrains future grants without preempting running workers. |
adbc.redis.query_workers_active, adbc.redis.query_workers_queued |
database or connection, read-only | Current shared active worker grants and number of queries waiting for admission. query_worker_budget also reads the currently resolved budget on either handle. |
adbc.redis.query_workers_last_grant |
connection, read-only | Worker grant for this connection's most recently admitted eligible aggregate, or 0 before its first admission. query_workers reads the per-query ceiling, which can be larger than the grant. |
adbc.redis.query_worker_tuning |
database or connection, read-only | JSON snapshot: effective shared budget, target and retained workers, latest candidate, comparison phase, cached models, exploration ceiling, completed observations, last measured signals, and reason for the most recent allocation decision. See worker autotuning. |
adbc.redis.rename_rekey |
database, connection | false (default): RENAME TO only changes metadata. true: it also moves the rows and index to the new name's keys, in time proportional to the number of rows, and the table refuses changes meanwhile (see ALTER TABLE). The connection option overrides the database's |
adbc.redis.read_timeout |
database, connection | How long the client waits for each reply: 30s, 10m, … or a number of seconds; 0 for no timeout. Default 5m, or the URI's read_timeout (see Timeouts). The connection option overrides the database's |
adbc.redis.write_timeout |
database, connection | How long the client waits to send each command, in the same format. Default: the read timeout, or the URI's write_timeout |
adbc.redis.ingest.index_columns |
statement | Comma-separated columns to index on bulk ingest (* = all indexable) |
adbc.redis.ingest.unmanaged_source |
statement | JSON application prefix/index and optional encodings for unmanaged creation; dedicated replace requires explicit destructive consent. Empty clears. |
adbc.redis.stream_results |
database, statement | true (default): stream the results of queries that can be. false: build every result whole before ExecuteQuery returns. The statement option overrides the database's |
adbc.redis.stream_batch_rows |
database, statement | Rows per record batch of a streamed result (default 65,536). A result that fits in the first batch is returned whole. Also an integer option on the statement |
adbc.redis.ipc.input |
statement | Absolute local regular-file path for native Arrow IPC ingestion; empty disables. Requires an ingest target and ExecuteUpdate (C ExecuteQuery with NULL output). See IPC contract. |
adbc.redis.ipc.output |
statement | Absolute new local file path for native SELECT-to-IPC export; empty disables. Existing files are not replaced. See IPC contract. |
adbc.redis.ipc.format |
statement | stream (default) or file, for native IPC input/output. |
adbc.redis.stream_batch_bytes |
database, statement | Optional accounted-byte ceiling per streamed batch (default 0: disabled); complements the row ceiling. One oversized row progresses; cursor-page overshoot and allocator/consumer memory are outside this budget. See streaming accounting above. Also an integer option on the statement |
adbc.redis.partition_count |
statement | Maximum partitions returned by ExecutePartitions: integer or string from 1 to 1024 (default 8). Splits allocated internal row-ID ranges; does not set concurrency or guarantee equal row counts. See ADBC partitioned reads. |
adbc.redis.partition_result_mode |
statement | live (default) or retained: bounded global execution and immutable final Arrow replay. See retained results. |
adbc.redis.partition_result_namespace |
database | Explicit tenant namespace for retained publication/replay; 1–64 ASCII letters/digits/underscores/hyphens. |
adbc.redis.partition_reader_only |
database | true bypasses source bootstrap and refuses SQL/ingestion; requires a result namespace. Default false. |
adbc.redis.partition_result_ttl |
statement | Live snapshot admission lifetime or fixed retained-result lifetime after publication, integer/string seconds 1–86400 (default 300). Reads do not renew the group/result lifetime. |
adbc.redis.partition_snapshot_id |
statement (read only) | Last successful live partition set's release ID. DROP PARTITION SNAPSHOT '<id>' blocks new readers; admitted readers retain their pins. |
adbc.redis.partition_result_limits |
database | Partial JSON output/namespace policy: job_bytes, total_bytes, chunk_bytes, chunks, jobs; publishers must agree. See limits. |
adbc.redis.partition_execution_limits |
statement | Partial JSON cumulative coordinator resource limits; over-budget queries fail before publishing descriptors. See limits. |
adbc.redis.partition_control_persistence |
database | auto checks that persistent quota keys are protected from eviction; guaranteed requires independent operator verification when CONFIG is restricted. |
adbc.redis.partition_strategy |
statement | rowid (default): equal allocated-ID widths; balanced: opt-in live-row quantiles with bounded planning probes and fallback. Does not start workers or freeze membership. |
adbc.redis.partition_scan_cap |
statement | Guaranteed aggregate-cap lower bound: integer/string 0..2147483648 (default 0: fresh discovery, explicit error when hidden). See partitioned reads. |
adbc.statement.exec.incremental |
statement | Canonical string true/false (default false). Emit available descriptors once, then empty terminal sets; see lifecycle. |
adbc.redis.time_zone |
database, connection | The session time zone, with the values SET TIME ZONE takes (default UTC; see Session time zone), and what RESET timezone and SET TIME ZONE DEFAULT go back to. The connection option overrides the database's and replaces a SET's value; reading it gives the current value, as SHOW timezone does |
adbc.redis.temporary_objects |
connection, read-only | true while the connection owns at least one temporary table or view, otherwise false. The value is read from connection-local state without a Redis round trip, so a Flight server can decide whether it is safe to move a scan to another pooled connection. |
adbc.redis.foreign_indexes |
database, connection | false (default); true imports application HASH indexes into redis_foreign, using the selected ownership mode. |
adbc.redis.foreign_indexes_mode |
database, connection | managed (default) creates read-only copies; unmanaged registers live source HASHes with SQL INSERT/UPDATE/DELETE. Set before enabling discovery. Existing registrations cannot silently change modes. |
Aggregate worker tokens control concurrency; a token does not reserve a physical CPU. Connections should reuse one ADBC database handle to share capacity. Separate database handles and application processes have independent budgets, even when they use the same Redis URI. Serial fallbacks and server-side aggregate pushdown do not acquire worker tokens. A running query keeps its grant; later queries can borrow spare tokens or wait. When tokens become available, queued queries receive at least one each in FIFO order, with remaining tokens divided round-robin up to each ceiling. Set a smaller explicit budget for application or Redis headroom. See the concurrent aggregate benchmark for measurements and reproduction instructions.
Scale considerations¶
Scale tips: keep column names short (they are repeated in every HASH), raise
hash-max-listpack-entries / hash-max-listpack-value so small rows use the
compact encoding, and index only the columns you filter or aggregate on.
Performance¶
Benchmark setup¶
Measured on 2026-10-08 against the default Redis Software environment:
one RAM database and one shard, Redis 8.6.2 and Search 8.6.11, using
redislabs/redis:8.2.0-78.18. The driver is at
5904851,
with the worker coordinator from PR #225. The seed-42 fixture contains
100,000 sales rows × 10 columns; these queries all use sales.
Hardware: Apple M4 (10 cores, 16 GiB), Docker Desktop (10 CPUs, 7.75 GiB).
Client: Go 1.26.6, Python 3.14.0, PyArrow 25.0.1, ADBC driver manager 1.12.0.
QPF stays at standard, client GOMAXPROCS stays at 10, and the shared
worker budget stays at auto (10). Only the per-query worker ceiling changes:
1, 2, 4, 8, or automatic (10). One query is in flight at a time. This measures
worker scaling at fixed CPU capacity; a worker count is not a count of busy
CPU cores.
Each table cell is the median of five block medians, with two warmups and three timed SQL executions plus complete Arrow fetch per block. Each block uses a fresh client process. The five rounds balance setting order and shuffle workload order (seed 224): 175 blocks, 525 timed queries. Complete results and actual worker grants are checked. No other containers appeared in the round-boundary inventory; host applications still share the laptop. Small differences, especially in the controls, can be run variation.
Raw measurements, exact SQL, CPU and peak memory, and reproduction details are included. The separate QPF experiment measures concurrent throughput. The earlier 20-query QPF latency table and OSS standalone/cluster snapshot remain archived. These runs compare worker settings within one build; they do not isolate the effect of PR #225 or compare Software and OSS under identical conditions.
Single-query latency by driver worker count¶
These measurements predate measured worker autotuning. Auto (10) below
uses the earlier CPU-sized policy. The current policy and its measured
tradeoffs are documented in the autotuning evaluation.
Lower latency is better. Auto/one-worker speedup is shown only for eligible ungrouped aggregates. Controls use zero aggregate worker grants at every setting, so their columns are repeated measurements of the same execution path. The indexed queries use server reductions; grouped driver aggregates and full scans do not use this worker pool. Default exact aggregate pushdown remains enabled.
| Query | Rows out | 1 worker (ms) | 2 workers (ms) | 4 workers (ms) | 8 workers (ms) | Auto (10) (ms) | Auto vs 1 |
|---|---|---|---|---|---|---|---|
Ungrouped AVG(discount) |
1 | 656.7 | 431.9 | 357.9 | 309.3 | 298.5 | 2.20× |
Ungrouped SUM, AVG, MIN, MAX |
1 | 729.9 | 479.5 | 387.1 | 360.4 | 330.3 | 2.21× |
Filtered ungrouped SUM, AVG, MIN, MAX |
1 | 239.6 | 159.2 | 133.2 | 132.4 | 116.2 | 2.06× |
Indexed range COUNT (control) |
1 | 6.4 | 6.3 | 6.4 | 6.7 | 6.0 | — |
Indexed GROUP BY, COUNT, SUM (control) |
6 | 9.1 | 9.4 | 8.8 | 10.0 | 9.4 | — |
Driver grouped AVG(discount) (control) |
6 | 695.0 | 704.3 | 682.9 | 732.2 | 706.0 | — |
| Full scan, all columns (control) | 100,000 | 1049.8 | 1042.5 | 1052.3 | 1020.5 | 1044.0 | — |
Bulk ingest (adbc_ingest), standard QPF: 1.37 s for 100,000 rows
(73,107 rows/s), measured once during fixture loading.
Interpretation¶
- Eligible ungrouped aggregates benefit from more workers. Automatic
workers reduced
AVG(discount)from 656.7 to 298.5 ms (2.20×), mixedSUM, AVG, MIN, MAXfrom 729.9 to 330.3 ms (2.21×), and the filtered variant from 239.6 to 116.2 ms (2.06×). QPF remained at standard. - Faster latency uses more client resources. For the mixed aggregate, mean busy client cores rose from 0.72 to 1.89, median client CPU time from 532.9 to 639.2 ms/query, and median process peak RSS from 128.6 to 297.0 MiB. Ten granted workers did not occupy ten CPU cores. RSS includes Python, Arrow, Go, warmups and timed queries; it excludes Redis.
- The controls do not use this worker pool. Indexed count remained about 6–7 ms, driver grouped AVG about 0.7 s, and the full scan about 1.0 s. Their columns vary without a worker-path change; these differences should not be credited to the worker setting.
- The automatic policy in this archived run followed CPU capacity. It had the lowest reported median for these three eligible queries, but some block ranges overlap. This fixture does not establish the best setting for every query, host, or concurrency level. See the report for all five rounds and resource measurements.
The following specialized measurements are historical OSS results and were not rerun in this worker-scaling experiment:
- dbt's
delete+insertwith a composite key (where (k1, k2) in (select distinct k1, k2 from …)) deleted 10,000 keys from a table of 200,000 rows in about 1 s, standalone and on a 3-shard cluster, and inserted them in 0.4 and 0.15 s (measured separately withTestSQLRowValueScale: Redis 8.6.2 on a machine shared with other work, median of 4 runs, which ranged from 0.8 to 2.2 s). Every row is read and checked against a hash set of the keys. The per-key forms,DELETE … USINGandEXISTS, also read every row and took 1.2–1.6 s. When a key column of the keys has at most 1,000 distinct values, the index fetches only the rows with those values, and the delete took 0.2–0.3 s. - Long literal row lists and keyset pagination (
TestSQLRowValueScaleat 200,000 rows, standalone: the better of two runs, and one run of v0.0.12). Counting the rows in a(k1, k2) IN (…)list of 2,000 scattered keys, more than an index union takes, took 0.6 s: every row is read and checked against a hash set of the list. Up to v0.0.12 each row was compared with each row of the list, andINandNOT INtook 89 and 56 s. With 1,000 keys the index fetches 4,280 rows, in 62 ms (0.56 s). Paging through a table of 200,000 rows 1,000 at a time withWHERE (ts, id) > (?, ?) ORDER BY ts, id LIMIT 1000took 11 s for the 200 pages, and a page of 100 rows near the end 12 ms: the index starts each page at the last page'sts. Up to v0.0.12 each page also read every row before it (86 s and 751 ms). ORDER BYon values from 2^53 on costs about as much as on smaller ones, as long as the rows don't tie (see "Ties in the sort vector"). On 100,000 rows withBIGINTids 2^60 + 7k (Snowflake-like; 36 share each double, so aLIMITnearly always ends within a tie),SELECT * … ORDER BY id DESC LIMIT 20took 8.3 ms, against 7.9 ms in v0.0.12 (which could return the rows of a tie in the wrong order): one moreFT.AGGREGATEreads the tie. WithORDER BY id LIMIT 20 OFFSET 50000it took 22 ms (20 ms), with anFT.AGGREGATEfor the tie at each end and anFT.SEARCHcount. Measured with Redis 8.6.2, standalone, on a machine shared with other work, median of 21 runs.GROUP BY,MINandMAXon values from 2^53 on, in theallmode, cost the index'sFT.AGGREGATEand then the driver's, which reads every row as in theexactmode (see "Aggregate pushdown"). On 100,000 rows with those Snowflake-like ids,SELECT id, COUNT(*) … GROUP BY idtook 400 ms, against 382 ms in theexactmode, andSELECT g, COUNT(*), MIN(id), MAX(id) … GROUP BY g(6 groups) 294 ms (279 ms). v0.0.12 took 25 and 7.7 ms, and returned 2,735 groups for the 100,000 ids and roundedMINs andMAXes. Groups,MINs andMAXes of smaller values still take oneFT.AGGREGATE, as before. Measured with Redis 8.6.2, standalone, on a machine shared with other work, median of 21 runs.- Queries that can't return rows read nothing, only the tables'
metadata. On a table of 152,686 rows (measured separately, best of 3,
standalone), dbt's
select * from (<model>) as __dbt_sbq where false limit 0over a model with twoGROUP BYs and a join takes 1.2 ms, and the model'sdbt run --emptyform (each ref read as(select * from t where false limit 0)) 1.0 ms. Up to v0.0.6 they ran in full, in 1.0 s (as long as the model itself) and 2.7 s;select * from t where false limit 0took 1.4 s, and now 0.5 ms.
Query performance factor (QPF)¶
QPF substantially improved concurrent index-query throughput in the corrected SQL/Arrow experiment. At 16 clients, 4× QPF delivered 4.13× indexed group-by throughput and 4.01× indexed range-count throughput, relative to standard. Single-client latency and workloads that fetch and process many rows showed much smaller benefits.
The earlier October 6 comparison had an invalid baseline: on this Software
image, setting active=false retained expanded routing/connection settings
after QPF had been enabled. Its saved metadata confirms this. The corrected
harness uses active=true, scaling_factor=0 for standard and verifies
Search workers, routing, and connection limits after every transition.
The old report is marked superseded.
The corrected experiment ran 90 trials on the same 100,000-row fixture: three rounds with rotated QPF order, five seconds of requests per trial, plus draining outstanding work. Each client warms up twice. Index workloads use 1 and 16 clients; larger driver/transfer workloads use 1 and 4. All connections share one ADBC database handle and its automatic worker budget. The five workloads' complete results are checked across settings; all trials had zero query errors.
The table shows median trial throughput over three repeats. Ratios above 1.00× mean more completed SQL queries per second, including Arrow fetch.
| SQL workload | Clients | Standard queries/s | 2× / standard | 4× / standard |
|---|---|---|---|---|
Indexed GROUP BY country, COUNT, SUM |
16 | 145.96 | 2.68× | 4.13× |
Indexed range COUNT |
16 | 259.07 | 2.37× | 4.01× |
Driver-side grouped AVG(discount) |
4 | 3.47 | 1.22× | 1.20× |
Ungrouped SUM, AVG, MIN, MAX |
4 | 3.82 | 1.07× | 1.11× |
| Full scan, all 100,000 rows | 4 | 1.71 | 1.31× | 1.27× |
At 16 clients, indexed group-by's median trial p95 fell from 176.4 ms to 38.4 ms at 4× QPF; range-count p95 fell from 68.8 ms to 22.9 ms. These are local measurements on a shared client/server laptop, not production forecasts. Five-second scan trials have few requests, so their p95 values and small throughput differences are noisy.
QPF increases server Search compute. It cannot eliminate decoding, transfer, or driver-side work, and it needs CPU headroom. The test verified 0 background Search workers at standard, 3 at 2×, and 6 at 4×. See Redis's QPF sizing guidance. Standard remains the server default. For best concurrent query performance with this driver, we recommend 4× QPF where supported, with Redis Search (Query Engine) enabled and sufficient CPU capacity. Validate the setting with your representative workload; 4× QPF is not a guarantee of a 4× speedup.
Full report and reproduction commands, raw trials and configuration checks, and SQL/Arrow harness are included.
Potential optimizations¶
Known ways to make the slow cases faster (not done yet):
COUNT(*)with aLIKEprefix, andCOUNT(*)on a lazy view, still read the matched rows to re-check them, although the index has already matched them exactly; the index could answer them alone.- Keyset pagination with a small
LIMITstill sorts every row from the bound on and reads a whole cursor page (up to 10,000 rows) per page: the row comparison is checked on the rows, which keeps theLIMITout of the index. The first cursor page could ask for only as many rows as theLIMIT. - A
LIMITthat ends within a tie of largeNUMERICvalues, or of a key after one that doesn't round (ORDER BY category, id), asks the index again with more rows, which sorts every match again; the driver could read the tie by value, as it does for an integer first key. Many rows with the same large value make one long tie, which such aLIMITreads whole. - In the
allmode,MINandMAXofBIGINTs from 2^53 on read every row, although only the rows that tie with the index's answer (@c:[v v]) can hold the exact one; the driver could read those, as anORDER BY … LIMITreads a tie.
Reproduce the benchmark¶
From go, using the default Redis Software environment:
make redis-up
make build
# Use the built library through the installed Python wrapper (.dylib on macOS).
export REDIS_ADBC_LIBRARY="$PWD/build/libadbc_driver_redis.so"
REDIS_URI=redis://localhost:6379/0 uv run --project validation --with-editable '../python[dbapi]' --with redis python examples/bench.py --rows 100000 --repeat 1
REDIS_URI=redis://localhost:6379/0 uv run --project validation python ../benchmarks/workers/run.py --cpus 10 --output /tmp/adbc-workers-new
python3 ../benchmarks/workers/summarize.py /tmp/adbc-workers-new
The first Python command replaces the sales and customers tables and
runs a fixture sanity check. The worker harness then runs the five-round
comparison at standard QPF and saves raw results; the final command produces
the summary and tables. Use a new output directory for each run. --cpus
fixes client GOMAXPROCS, not Docker's CPU allocation. See the
method and reproduction details.
Run uv run --project validation --with-editable '../python[dbapi]' python examples/load_sample.py
afterwards to get the quick-start data back.
Building and testing¶
The default setup needs Docker Compose, curl, and jq. It uses the bundled
trial license and fixed local test credentials, with the admin API and database
bound to localhost. The database is in RAM, with no replica or persistence.
SOFTWARE_API_PORT (default 19443) and SOFTWARE_DB_PORT (default 6379)
override host ports; set REDIS_URI for tests when changing the database port.
The default Redis Software setup enables and verifies 4× QPF.
Validation suite¶
See Building from source to build the driver. To run the ADBC validation suite against the default local Redis Software database:
cd go
make redis-up
make validate
The suite accepts any Redis 8.x server, so it can also target a remote or
Redis Cloud database. Use rediss:// if the database requires TLS, and use
an empty database: the suite leaves its test tables (test_*, getobjects*,
statistics*) behind.
REDIS_URI='rediss://default:<password>@<host>:<port>/0' uv run pytest -v tests/
Go tests¶
SQL features beyond the validation suite (CASE, HAVING, CTAS, …) have Go
integration tests with exact expected results. They need a running Redis and
are skipped by direct go test commands when REDIS_URI is unset.
make test sets the local Software URI by default; start it with
make redis-up first (from go):
REDIS_URI=redis://localhost:6379/0 go test -run TestSQL ./...
To run every test, pass -p 1 (as make test does), so the driver's
package and its commands' packages don't test against the server at once. Any
connection that opens rolls back an abandoned move, including the ones the
driver's rename tests set up and then expect to find.
REDIS_URI=redis://localhost:6379/0 go test -p 1 ./...
TestSQLRowValueScale times dbt's delete+insert with a composite key
(with -v) on 20,000 rows; REDIS_ROW_VALUES_ROWS=200000 runs it on
200,000 (a multiple of 200), as measured under Performance.
Some tests need more:
- The ACL tests (
TestACL…) create ACL usersit_acl_*and delete them afterwards, so the user needs theACLcommand. To run the whole suite as a user with only the driver's commands (see ACL users), setREDIS_URIto that user andREDIS_ADMIN_URIto one that can do everything: the tests' own checks use it (KEYS,ACL, …). Redis Software manages users through its management API and blocksACL SETUSER, so these protocol-level ACL tests skip there and run in the OSS compatibility job. - Command-count assertions need
CONFIG RESETSTATand immediate INFO counters. They skip when Software blocks that command; the other SQL correctness tests run on Software, and the OSS job covers these assertions. TestTimeoutServerPausedpauses the server (CLIENT PAUSE, on one shard of a cluster) for about half a minute, so it only runs withREDIS_PAUSE_TESTS=1. Run it against a test server only.
The pull-request Test workflow runs eight independent jobs: AMD64 and ARM64 for each of Redis Software single-shard, Redis Software three-shard, Redis Open Source standalone, and Redis Open Source three-shard cluster. Every environment includes Search and runs both the Go tests and Python ADBC validation. Jobs run in parallel, subject to GitHub runner availability; tests within a job share a database and run sequentially.
To start a local three-shard Redis Software database with Search:
SOFTWARE_SHARDS=3 make redis-up
The Software shards share one disposable Software node and are accessed
through its proxy endpoint on port 6379. This tests database sharding, not
multi-node failover. Remove an existing Software container with
make redis-down before changing the shard count; setup verifies the actual
shard count and fails if an existing database has a different topology.
Redis Cluster¶
Open Source is an optional compatibility environment. For standalone OSS,
run make redis-down then make redis-oss-up from go; it uses port 6379.
Remove it with docker compose --profile oss rm -sf redis before switching
back to Software. To test the OSS Cluster API on a local three-shard cluster:
docker compose --profile cluster up --detach --wait redis-cluster
cd validation && REDIS_URI=redis://localhost:7001/0 uv run pytest -v tests/
Redis Flex¶
To test against Redis Flex, flex-setup.sh (from go) starts a
single-node Redis Software cluster with flash storage in Docker. It then
creates a Redis 8.8 Flex database with Search on it, and prints the database's URI.
It pulls the latest Software image and defaults to 1,024 MB total with 256 MB
in RAM (25%); FLEX_REDIS_VERSION overrides the version for a new database.
The cluster uses about 1.5 GB of memory and the image's trial license. Its
"flash" is a directory inside the container, and its admin API (port 9443)
listens on localhost only, with fixed test credentials.
./flex-setup.sh
Flex remains unsupported (see Server requirements).
TestFlexRefused checks the older builds' refusal before writing metadata.
On Software 8.2.6-25 / Redis 8.8 this test fails because the connection now
succeeds; table creation still fails on unsupported SORTABLE fields. The test is skipped unless
REDIS_FLEX_URI is set:
REDIS_FLEX_URI=redis://localhost:12000/0 go test -count=1 -run TestFlex -v ./...
Remove the cluster with docker compose --profile flex down redis-flex.
Name the service: without it, down also removes the redis container.
Verified local result¶
On 2026-11-04, the latest Software 8.2.6-25 image with Redis 8.8.0 and
Search 8.8.8 passed the basic aggregate, numeric, geo, and field retrieval
probes. TestFlexRefused failed because connection now succeeds;
TestSQLAliasesInExpressions failed on its first table creation because
SORTABLE is unsupported. Aggregate cursors are also unsupported.
See the updated report for full results.
On 2026-10-06, TestFlexRefused passed against Redis Software 8.2.0-78,
Redis 8.6.2, and Search 8.6.11. The driver returned ADBC
NotImplemented with the expected Flex message and left the key count
unchanged. Direct probes confirmed that FT.AGGREGATE, NUMERIC, and
SORTABLE fields were unavailable. Temporary probe keys and indexes were
removed.
Redis Cloud Pro Flex¶
Run the same test from go using an authenticated database URI. The Redis
Cloud account and user API keys authenticate the management API; use the
database password for the Redis connection. Keep the URI in an environment
variable and out of source control. For example, in a shell with history
disabled or using a secret manager:
export REDIS_FLEX_URI='redis://default:<url-encoded-password>@<host>:<port>/0'
go test -count=1 -run TestFlex -v ./...
unset REDIS_FLEX_URI
The live Cloud run tested a newly created 250 GB Flex database in GCP
us-east4, on Redis 8.6 with 10% RAM. The database used
1,000 operations/second, no replication or
persistence, RESP3, no TLS, and a single proxy endpoint. That historical driver
run used RESP2; the current driver requires RESP3. The 250 GB dataset was
the minimum accepted by this subscription.
The authenticated run failed TestFlexRefused with
ERR unknown command 'FT._LIST'. This test expects the later Flex-specific
refusal, so a missing Search module is a test failure rather than a passing
Flex compatibility check. On the new database, PING returned PONG, but
COMMAND INFO found no registration for FT._LIST, FT.CREATE,
FT.SEARCH, or FT.AGGREGATE. No Cloud probe keys or indexes were created.
Redis's documentation
requires enabling a Search-on-Flex beta flag for the Pro subscription before
creating a database with Search on tiered storage. It does not specify the
flag's exact menu location. Setup → Advanced options → Redis Flex enables
tiered storage; it does not confirm Search beta enablement. If the beta
control is absent, ask Redis Support to confirm eligibility and the
enablement procedure. These tests did not independently verify the
subscription's beta entitlement or exercise a Search-enabled Cloud database.
The disposable Cloud database was left active pending explicit deletion approval. The API dry run estimated approximately USD 1.43/hour for its shape. See the report for full settings and the distinction between live results and documented beta limitations.
Cross-platform builds and releases¶
To build the Linux library (on Linux or a Mac, with Docker), run
build-linux.sh from go with amd64 or arm64. It writes
build/linux-<arch>/libadbc_driver_redis.so. A foreign architecture runs
under emulation and is much slower.
./build-linux.sh arm64
The script builds in an AlmaLinux 8 container, against glibc 2.28: the oldest glibc among the platforms Redis Software supports. So one library loads on all of them, and the script fails if it would need a newer glibc.
Release binaries come from .github/workflows/release.yml. When a release is
published, it builds the Linux (amd64, arm64) and macOS (arm64) libraries,
runs the Go tests and the validation suite against Redis on both Linux
builds (standalone and a 3-shard cluster), and then runs the validation suite
on each Linux tarball under Ubuntu 20.04, 22.04 and 24.04, AlmaLinux 8 and 9
(RHEL 8 and 9), and Amazon Linux 2023. Only then does it attach the tarballs
and their .sha256 files to the release. GitHub's macOS runners can't run the
Redis containers, so the macOS build is only checked to load and to pass the
Go unit tests. To rebuild an existing release's assets, run the workflow
by hand with its tag:
gh workflow run release.yml -f tag=v0.0.4
The run builds and tests the tag's own code. If a Go test at that tag is
wrong on one platform (not the driver), skip (a go test -skip pattern)
leaves it out. v0.0.8 and v0.0.9 got their binaries this way: their
linux-amd64 jobs failed TestScalarTrigonometry and TestSQLNaNAggregates
(fixed after them by #121 and #125).
gh workflow run release.yml -f tag=v0.0.8 -f skip=TestScalarTrigonometry
Release builds also produce Python wheels for Linux x86-64/ARM64 and macOS
ARM64. PyPI publication is a separate, manually dispatched
publish-pypi.yml workflow on main, using Trusted Publishing for an existing
stable release tag. It verifies the release wheels and checksums before upload;
release.yml does not publish directly to PyPI. Its optional
description_revision input makes a metadata-only .postN release while
preserving Python code and native-library bytes. See the
release runbook.
Linux release compatibility jobs have a 20-minute limit, with eight minutes for validation. They print installation, dependency and per-test progress, collect failure diagnostics and always clean up the validation container.
Python usage¶
Install from PyPI, then use the bundled driver (Python 3.14 or later):
python -m pip install 'adbc-driver-redis[dbapi]'
from adbc_driver_redis import dbapi
with dbapi.connect("redis://localhost:6379/0") as conn:
with conn.cursor() as cur:
cur.adbc_ingest("my_table", arrow_table, mode="create")
cur.execute("SELECT country, SUM(quantity) FROM sales WHERE quantity > 10 GROUP BY country")
print(cur.fetch_arrow_table())
Connections default to autocommit=True; transactions remain unsupported.
Use adbc-driver-redis[dagster] for the Dagster IO manager. Wheels support
Linux x86-64/ARM64 (glibc 2.28 or later) and macOS ARM64 (15 or later).
Release wheels are also attached to GitHub releases. Source checkout installation, Polars URI
loading, TLS aliases, option enums and driver discovery are covered in
Python integrations.
The C FFI shim in go/pkg was generated with driverbase-go's ffitemplate
(go run main.go -prefix Redis -driver <path>/go).
ADBC driver manifests¶
Run python -m adbc_driver_redis install-manifest --user after installing a wheel,
or ./install.sh --user in an unpacked native release. Both install redis.toml
and rediss.toml with absolute library paths. Tools can then use driver="redis"
or resolve the driver from a redis:// URI. Keep the library in place; rerun the
installer if it moves. See manifest installation.
SQLAlchemy and Superset¶
The wheel's sqlalchemy extra registers redis+adbc:// and rediss+adbc://,
with PostgreSQL SQL compilation and catalog reflection. Use the separate
superset extra in its Python 3.12 compatibility environment. Superset engine
specs support schema/table browsing, datasets, SQL Lab, and PostgreSQL DATE_TRUNC
time grains. This is an autocommit analytics connector; SQLAlchemy rollback
cannot undo writes. See setup and limitations.
Dagster¶
adbc_driver_redis.dagster.RedisADBCIOManager stores Arrow, pandas and polars
asset outputs as Redis tables, loads selected columns into downstream assets,
and replaces static or time-window partitions on retries. Connections use
autocommit=True. Staging protects the old version during ingest; DROP/RENAME
has a missing-table window, and partition DELETE/append can partially apply.
See the Definitions example and write caveats.
Dask¶
Install adbc-driver-redis[dask] and use
adbc_driver_redis.dask.read_query to build a lazy DataFrame from explicit
query parameter partitions and an Arrow result schema. Each task opens its own
ADBC connection; nullable types and empty partitions keep the supplied schema.
Graph creation performs no database reads. Callers must supply disjoint,
exhaustive predicates, including a NULL partition when needed. Each task
materializes its entire partition in worker memory; concurrent reads and retries
have no shared snapshot. This helper uses bound SQL parameters independently
of the ADBC partition descriptor API above. Install the integration and native
driver on every worker and size partitions to available memory.
See the distributed example and partitioning contract.
DuckDB¶
The standalone redis_adbc extension embeds the Redis ADBC driver and Go
runtime in one native extension, targeting DuckDB 2.0. It streams SELECT
results without a separate driver library, driver manager or manifest:
SELECT * FROM redis_adbc_scan(
'redis://localhost:6379/0',
'SELECT id, country FROM public.orders WHERE id > 100'
);
Build from source and load the resulting extension with unsigned extensions
enabled in the matching DuckDB engine. Community-registry installation remains
pending acceptance and deployment. SQL inside redis_adbc_scan runs through the
Redis driver; surrounding DuckDB filters, projections, joins and aggregates run
locally. The scanner accepts exactly one SELECT and provides no ATTACH, writes
or ingestion API. See the native build and behavior guide.
Use the community adbc_scanner extension for attached catalogs and Arrow
bulk loading. Current builds support attached-table autocommit writes; explicit
transactions remain unsupported. Use r.public.t for schema-qualified access.
See tested examples, telemetry opt-out, filter behavior and version caveats.
Columnar's separate adbc extension remains untested.
PostgreSQL compatibility upgrade notes¶
Square brackets now delimit PostgreSQL arrays, subscripts and slices. Replace
SQL Server [identifier] quoting with PostgreSQL "identifier" quoting. Array
columns require clients that preserve element types in table metadata; upgrade
all clients before creating them. Arrays remain unindexed.
Unquoted SQL identifiers now fold to ASCII lowercase; double-quoted names remain exact. Existing mixed-case Redis metadata is retained. Quote its exact names when accessing it, or rename those objects and update stored view SQL.
Small integer literals now infer INTEGER, then BIGINT or NUMERIC as
needed. Integer arithmetic checks overflow at its common operand width. Cast
operands to BIGINT when the wider range or an Arrow int64 schema is required.
Default descending sorts put NULLs first, including windows and ordered
aggregates. Write DESC NULLS LAST to retain the old ordering. Nullable
numeric sorts with NULLs first can retain index ordering when a proof over the
pinned visible candidates finds no NULLs. Other cases use the exact driver sort;
NOT NULL keys and explicit NULLS LAST retain their usual index eligibility.
DATE_ADD(timestamptz, interval [, zone]) and DATE_SUBTRACT implement the
PostgreSQL 16 overloads, using the session time zone unless a zone is supplied.
The Snowflake (part, n, x) form remains available. The MySQL interval syntax
for DATE_ADD now resolves to the PostgreSQL overload and returns timestamptz.
Escape strings (E'...') decode C, octal, hex and Unicode escapes; Unicode
strings and identifiers (U&'...', U&"...") support UESCAPE. Invalid UTF-8
or NUL in decoded strings raises 22021. Boolean tests accept IS [NOT]
TRUE/FALSE/UNKNOWN; ISNULL and NOTNULL are postfix null tests.
The ADBC autocommit option accepts true as a no-op. Setting false reports
that transactions are not implemented. Large LIMIT/OFFSET values fall back
to cursor pagination when their arguments exceed Redis Search's caps. A
lowered total aggregate cap uses bounded Search row-ID ranges for eligible
managed, row-local reads in insertion/ascending internal-ID order. Other plans
retain complete HASH scans and driver-side sorting to avoid truncation.
Arrow normalization and physical result layouts¶
See Arrow interoperability for the adbc.redis.result_layouts
statement option, full-range uint64 ingestion, recursive arrays, stored named
structs, extension rejection, ownership, and partition restrictions.