spec/sql
SQL (sql-pre-data, sql-post-data, sql-dql)
txtab
P sql-pre-data postgres CREATE TABLE orders (id bigint, customer_id bigint, amount numeric(12,2))
P sql-post-data postgres ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(id)
P sql-post-data postgres CREATE INDEX orders_customer ON orders(customer_id)
P sql-dql clickhouse SELECT customer_id, sum(amount) FROM orders GROUP BY customer_idA file MAY carry SQL next to its data: DDL to recreate the tables, and queries for provenance or example use.
Keywords
| Keyword | Section | What goes there | Executed |
|---|---|---|---|
sql-pre-data |
before data load | CREATE TABLE, schemas, types, extensions |
yes, in order |
sql-post-data |
after data load | indexes, primary and foreign keys, constraints, triggers | yes, in order |
sql-dql |
— | SELECT and friends: provenance, examples |
never |
The section names are those of pg_dump --section.
Syntax
text
P <keyword> <dialect>[-<version>] <statement>- The first payload token is the dialect, mandatory: a lowercase ASCII identifier, optionally followed by
-<version>; the version MAY contain dots (postgres-17,mysql-8.4,clickhouse-24.3). Missing:sql-dialect-missing(FAIL). - After one space, the rest of the line is the statement, raw text to end of line.
- One line holds one statement. There are no continuation lines.
- Every line is self-contained: there is no file-level default dialect. Generic SQL uses
ansi.
Dialects
Parsers SHOULD recognise these tokens and MUST accept any other lowercase identifier as-is.
| Token | Database |
|---|---|
ansi |
ANSI / ISO SQL |
mysql |
MySQL |
mariadb |
MariaDB |
postgres |
PostgreSQL (read aliases: postgresql, pg) |
mssql |
Microsoft SQL Server (read alias: sqlserver) |
sqlite |
SQLite |
oracle |
Oracle Database |
db2 |
IBM Db2 |
clickhouse |
ClickHouse |
snowflake |
Snowflake |
bigquery |
Google BigQuery |
duckdb |
DuckDB |
Writers SHOULD emit the canonical token. Readers MUST treat aliases as equivalent.
The token is split into dialect and version by matching the longest well-known dialect prefix; an unknown token is kept whole.
Matching a target dialect
When a tool selects statements for target D, each line's token L is classified:
| Kind | Rule | Default |
|---|---|---|
exact |
same dialect, same version (or both unversioned) | include |
family |
same dialect, exactly one side unversioned | include, warn |
version-mismatch |
same dialect, both versioned, versions differ | include, warn; skip with --strict |
none |
different dialect | skip |
Versions are compared as strings.
Binding and order
sql-*in a table block belongs to that table. A foreign key is written in the block of the table that owns it, wherever the target table is in the file.sql-*in the file block holds statements shared by the file: schemas, extensions.- Execution order for a target dialect:
- every matching
sql-pre-data, in file order, across all blocks; - data load (if the tool performs it);
- every matching
sql-post-data, in file order, across all blocks.
- every matching
sql-dqlis never executed.
Tooling
Text Tables tools do not run SQL against a database. txtab sql <dialect> FILE prints matching statements to stdout in the order above — warnings to stderr — and MAY print one section at a time (--section pre-data|post-data), like pg_dump.
Computed columns in generated DDL
When a tool generates DDL from column lines (rather than printing sql-* lines), a column with formula= becomes a generated column:
| State | PostgreSQL ≥ 12 / MySQL ≥ 5.7 | ClickHouse | No generated-column support |
|---|---|---|---|
| Virtual | GENERATED ALWAYS AS (<expr>) VIRTUAL |
ALIAS <expr> |
omit the column, or emit the expression as a comment |
| Materialized | GENERATED ALWAYS AS (<expr>) STORED |
MATERIALIZED <expr> |
ordinary stored column |
<expr> is the formula translated into the target dialect; the formula grammar maps onto ANSI SQL everywhere (columns.md). Generated DDL puts CREATE TABLE in pre-data and keys, indexes and references= foreign keys in post-data.