> Documentation index: https://docs.beetl.io/llms.txt # DataFusion SQL > The SQL Beetl runs: Apache DataFusion 54 with the PostgreSQL dialect, and where it differs from PostgreSQL. Source: https://docs.beetl.io/query/datafusion-sql/ Last updated: 2026-10-03 Beetl runs your SQL on Apache DataFusion 54.1 with the PostgreSQL dialect. The same engine serves the SQL editor, the `execute_query` MCP tool and pipeline stages. Most PostgreSQL you already write will run as is. The differences show up in date formatting, a few missing functions and catalog queries, and this page covers them. Beetl adds no functions of its own. You get DataFusion's built-in functions plus the JSON functions described in [JSON extraction](https://docs.beetl.io/query/json-functions). For the complete list, see the DataFusion reference for [scalar](https://datafusion.apache.org/user-guide/sql/scalar_functions.html), [aggregate](https://datafusion.apache.org/user-guide/sql/aggregate_functions.html) and [window](https://datafusion.apache.org/user-guide/sql/window_functions.html) functions. ## Table names In a query, refer to a dataset by its bare name, whatever its tier: ```sql SELECT order_id, total FROM shop_orders LIMIT 100 ``` The name qualified with your tenant's slug, such as `acme.shop_orders`, also works. Editor autocomplete offers both forms, and the MCP `list_data_sets` tool returns the qualified form as `qualified_query_name`. The two are interchangeable in queries. A pipeline stage is different: there you use the source aliases you set in the [builder](https://docs.beetl.io/pipelines/builder#add-sources) and the names of earlier stages, not dataset names. You cannot list tables from SQL. `SHOW TABLES` and `information_schema` are not available, so use the Tables button in the editor, the dataset list on Home or `list_data_sets` instead. ## Identifiers and quoting Unquoted identifiers are folded to lowercase, as in PostgreSQL. Dataset names are always lowercase, so they never need quotes. Columns are another matter: a CSV header such as `Total` or `Order Date` keeps its case and spaces, and you must quote it exactly. ```sql SELECT "Order Date", "Total" FROM invoices ``` Writing `Total` without quotes looks for a column named `total` and fails. Text values take single quotes, and a single quote inside a value is doubled: `'it''s'`. ## Statements Each run takes one statement. A trailing semicolon is fine, but 2 statements separated by a semicolon are rejected. Write queries with `SELECT`, `WITH`, `UNION` and the rest of the read-only syntax; `EXPLAIN` also works when you want to see a plan. A query cannot change data. `INSERT`, `UPDATE`, `DELETE`, `CREATE`, `DROP` and `COPY` are refused before anything runs. To write data, build a [pipeline](https://docs.beetl.io/pipelines). ## CTEs and window functions Common table expressions, subqueries, all join types and window functions work as in PostgreSQL. `QUALIFY` is also supported, which filters on a window function without a wrapping subquery: ```sql SELECT order_id, status, updated_at FROM orders QUALIFY row_number() OVER (PARTITION BY order_id ORDER BY updated_at DESC) = 1 ``` `FILTER (WHERE ...)` on aggregates, `string_agg`, `array_agg`, `median` and `approx_distinct` are available too. ## Types and casts Both `CAST(x AS type)` and `x::type` work. A failed cast stops the whole query with a cast error, so when a column holds values that may not convert, use `TRY_CAST`, which returns `NULL` instead: ```sql SELECT TRY_CAST(amount AS DECIMAL(12, 2)) AS amount, TRY_CAST(order_date AS DATE) AS order_date FROM raw_orders ``` Integer division truncates, as in PostgreSQL: `1 / 2` is `0` and `1.0 / 2` is `0.5`. To check the type of an expression, select `arrow_typeof(expr)`. In the editor, each column header also shows its type. ## Dates and timestamps | Function | Returns | Notes | | ------------------------------------------------ | --------------- | -------------------------------------------------------------------------------------- | | `to_timestamp(text)` | timestamp | Reads ISO 8601 text. An offset such as `+02:00` is converted to UTC. | | `to_timestamp(text, '%d.%m.%Y %H:%M')` | timestamp | Format strings use strftime-style `%` codes. | | `to_date(text [, format])` | date | Same rules as `to_timestamp`. | | `to_timestamp(epoch_seconds)` | timestamp | From a Unix timestamp. `to_unixtime(ts)` goes the other way. | | `CAST(x AS DATE)`, `x::timestamp` | date, timestamp | Text must be ISO, such as `2026-03-01`. Other text fails the query; see `TRY_CAST`. | | `date_trunc('month', x)` | timestamp | A timestamp even when `x` is a date. Cast the result to `DATE` if you need one. | | `extract(MONTH FROM x)`, `date_part('month', x)` | integer | `EPOCH` returns a float. | | `date_bin(INTERVAL '15 minutes', x)` | timestamp | Buckets of any width. | | `to_char(x, '%Y-%m')` | text | Uses `%` codes. A pattern such as `'YYYY-MM'` is returned literally, without an error. | | `now()`, `current_date` | timestamp, date | | Interval arithmetic works as in PostgreSQL. `ts - INTERVAL '7 days'` moves a timestamp, and subtracting one date from another gives the number of days as an integer. There is no `DATEDIFF`, `DATE_SUB`, `strftime` or `julianday`; use subtraction and `extract` instead. ## Text The usual PostgreSQL string functions are present: `upper`, `lower`, `trim`, `substr`, `split_part`, `replace`, `lpad`, `length`, `concat` and the `||` operator. `LIKE`, `ILIKE` and the `~` regex operator work in filters. `regexp_replace(text, pattern, replacement)` replaces only the first match. Add the `'g'` flag to replace them all: ```sql SELECT regexp_replace(phone, '[^0-9]', '', 'g') AS digits FROM contacts ``` `regexp_like` and `regexp_match` are also available. For fuzzy matching, `levenshtein(a, b)` is the only similarity function; there is no `similarity`. See [Match records across systems](https://docs.beetl.io/pipelines/match-across-systems) for a worked example. ## Hashing `sha224`, `sha256`, `sha384` and `sha512` return binary. Wrap them in `encode` to get the hex string you would compare or join on: ```sql SELECT encode(sha256(email), 'hex') AS email_hash FROM customers ``` `digest(x, 'sha256')` does the same with the algorithm as an argument and supports `md5`, `sha224`, `sha384`, `sha512`, `blake2b`, `blake2s` and `blake3`. `md5(x)` already returns hex text. ## NULL ordering NULLs sort as the largest value, as in PostgreSQL: last with `ASC`, first with `DESC`. A "top 10 by revenue" query therefore starts with the rows that have no revenue at all. Add `NULLS LAST` or filter them out: ```sql SELECT customer, revenue FROM revenue_by_customer ORDER BY revenue DESC NULLS LAST LIMIT 10 ``` ## JSON extraction The JSON functions work on any text column that holds JSON. A webhook's `payload` is base64, so decode it first with `CAST(decode(payload, 'base64') AS VARCHAR)`. [JSON extraction](https://docs.beetl.io/query/json-functions) covers paths, arrays and how to turn JSON fields into typed columns. ## Pitfalls * String functions do not accept numbers. `trim(id)` on an integer column fails with a signature error; write `trim(CAST(id AS VARCHAR))`. * Format strings use `%` codes. `to_char(x, 'YYYY')` returns the text `YYYY`, and `to_timestamp(x, 'DD.MM.YYYY')` fails with a parse error. Write `'%d.%m.%Y'`. * A function that does not exist fails with `Invalid function`, often with a suggestion (`Did you mean 'date_bin'?`). Check the DataFusion reference rather than the PostgreSQL manual. * `decode` returns binary. Cast it to `VARCHAR` before passing it to a JSON or string function. ## Rows in a result A result in the editor or over MCP keeps the first 1,000 rows the query produces and reports the full row count. You do not need a small `LIMIT` just to keep the display short. The MCP `execute_query` tool returns up to 50 rows per page and pages the rest with `get_query_result`. To get more than 1,000 rows out, see [Export](https://docs.beetl.io/query/export). ## Related - [JSON extraction](https://docs.beetl.io/query/json-functions) - [The SQL editor](https://docs.beetl.io/query/sql-editor) - [Quarantine bad rows](https://docs.beetl.io/pipelines/quarantine-bad-rows) - [Datasets and tiers](https://docs.beetl.io/concepts/datasets-and-tiers)