Repository navigation
SQL and Polars
Tier: Advanced
Commands covered: sqlp, joinp, pivotp, scoresql
Note
Per-command flag reference lives in /docs/help/. This page is the workflow layer — when to reach for each command and how they compose.
This page describes qsv 29.0.0, the latest release, and the current master branch. For what changed between releases, including behavior changes that can break an existing query, see the CHANGELOG.
These commands are powered by the Polars vectorized query engine. They handle larger-than-RAM data, are multithreaded by default, and speak a SQL dialect modeled on PostgreSQL. If you already write Postgres, most of your queries work as-is; this page focuses on where Polars SQL differs and what it adds.
The joinp command is also covered in Joins & Set Ops; this page focuses on sqlp, pivotp, and scoresql.
| If you want to… | Use | Notes |
|---|---|---|
| Run a SQL query across one or many CSVs / Parquet / JSONL / Arrow | sqlp |
Polars SQL (PostgreSQL dialect), larger-than-RAM, multithreaded |
| Score a SQL query BEFORE running it | scoresql |
Plan analysis, anti-pattern detection, cache freshness |
| Pivot a CSV (wide ↔ long) with smart aggregation | pivotp |
Group-by mode when <on-cols> is omitted |
| Join 2+ CSVs (asof, non-equi, larger-than-RAM) | joinp |
See Joins & Set Ops |
Polars SQL over CSV, Parquet, JSONL, and Arrow files. Polars turns the query into a lazy plan and runs it on its streaming engine. Output can be CSV, JSON, JSONL, Parquet, Arrow IPC, or Avro. sqlp prints (rows, cols) to stderr.
The optimization secret: feed it a Polars schema (.pschema.json, generated by schema --polars or --cache-schema) so Polars knows column types without an inference scan. Combined with a stats cache and scoresql, sqlp is the most performant SQL engine for CSV-shaped data we know of.
The Polars SQL user guide and function/keyword reference are written for Python Polars, so expect minor differences. For the full flag list see
/docs/help/sqlp.md.
Most of what you know carries over unchanged, including:
- scope rules (once a table is aliased, only the alias works:
FROM wcp AS wmeansw.City, andwcp.Cityerrors); -
IS DISTINCT FROMprecedence; -
NULLS FIRST/LAST; -
name => valuefunction arguments; - dollar-quoting;
- the
~,~*,~~,~~*,^@operator family.
The differences that matter:
| In PostgreSQL… | In sqlp… |
What to do |
|---|---|---|
| Tables come from a catalog | Each input file is a table named after its file stem (see Tables & inputs) | Name files sensibly, or use the positional _t_1, _t_2, … |
Unquoted identifiers fold to lowercase, so SELECT city finds City
|
Column names are case-sensitive, quoted or not: SELECT city fails when the column is City. Table names resolve case-insensitively |
Write each column name in its exact case |
CAST('10/31/2017' AS DATE) parses per DateStyle
|
String → date/time CAST infers ISO and day-first (31/10/2017) layouts but not month-first (10/31/2017), and one unparseable value fails the whole query |
TRY_CAST(col AS DATE) nulls just the bad rows; STRPTIME(col, '%m/%d/%Y') parses an explicit layout |
CAST(1.99 AS INT) rounds to 2
|
Decimal → integer casts truncate toward zero: 1.99 → 1, -1.7 → -1
|
CAST(ROUND(x) AS INT) (ROUND takes halves away from zero) |
An unaliased expression is named ?column?
|
Unaliased constants are named literal, literal:1, literal:2, … |
Alias them |
| A file has no "blank row" concept | A literal blank line in a CSV becomes an all-null row (see Known issues) | Normalize first with qsv input
|
Plain INTEGER/BIGINT
|
Column types also include unsigned integers (UTINYINT, USMALLINT, UINTEGER, UBIGINT) and TINYINT; a cached schema types non-negative integer columns as unsigned |
Cast an unsigned column to a signed type before multiplying it by a negative value (see Known issues) |
No <=> operator |
<=> is NULL-safe equality (MySQL's operator), the same as IS NOT DISTINCT FROM, and never returns NULL |
Use either form |
Date parts also match Postgres: WEEK is the ISO week (like EXTRACT(WEEK …)), and DAYOFWEEK numbers Sunday as 0 (like EXTRACT(DOW …)). REGEXP_REPLACE replaces only the first match unless you pass the 'g' flag. POSITION/STRPOS return NULL for NULL input. A query with a window function and no outer ORDER BY returns rows in no guaranteed order.
Each input file is registered as a table named after its file stem — wcp.csv → wcp, country_continent.csv → country_continent. You can also address inputs positionally as _t_1, _t_2, … (1-based, in argument order). Quote column names that contain spaces with double quotes (escaped as \" inside a double-quoted shell string).
# named table + positional alias refer to the same file
qsv sqlp wcp.csv "SELECT \"AccentCity\", Population FROM wcp ORDER BY Population DESC LIMIT 20"
qsv sqlp wcp.csv "SELECT \"AccentCity\", Population FROM _t_1 ORDER BY Population DESC LIMIT 20"Input options:
-
stdin: pass
-as the input; the table is namedstdin. - a directory: every file inside is read as input, each as a table named by its stem.
-
an
.infile-listfile: treated as a newline-delimited list of input paths. -
snappy
.szfiles: decompressed on read;--output result.csv.szcompresses the output.
# stdin as a table, joined against a file
cat wcp.csv | qsv sqlp - country_continent.csv \
"SELECT stdin.AccentCity, country_continent.continent
FROM stdin JOIN country_continent ON stdin.Country = country_continent.country"The read_csv, read_ndjson, read_parquet, and read_ipc table functions read and mix formats inline. Each one accepts a single path or a list of paths (read_csv(['jan.csv', 'feb.csv']) reads files with the same columns as one table). read_json reads newline-delimited JSON (not a JSON array) and takes no options; use read_ndjson when you need options.
A leading ~ in a table function path is your home directory (read_csv('~/data/x.csv')), also in a list of paths and in a glob. ~user/…, cloud paths and file:// URIs are left as written.
Pass the SKIP_INPUT sentinel instead of input files to skip qsv's CSV preprocessing and hand parsing straight to Polars' multithreaded, memory-mapped reader. This is the fastest path. qsv's own --delimiter and QSV_COMMENT_CHAR don't apply under SKIP_INPUT; set the delimiter and comment character with read_csv options instead.
# join a CSV against a Parquet file inline
qsv sqlp wcp.csv \
"SELECT * FROM wcp
JOIN read_parquet('country_continent.parquet') AS cc ON wcp.Country = cc.country"
# SKIP_INPUT: let Polars read the CSV directly — fastest path for a single-file scan
qsv sqlp SKIP_INPUT "SELECT * FROM read_csv('wcp.csv') ORDER BY Population DESC LIMIT 100"
# a semicolon-delimited file with '#' comments
qsv sqlp SKIP_INPUT "SELECT * FROM read_csv('data.ssv', separator => ';', comment_prefix => '#')"
# mix NDJSON + Arrow with no CSV input at all
qsv sqlp SKIP_INPUT \
"SELECT * FROM read_ndjson('events.jsonl') AS t1
JOIN read_ipc('dims.arrow') AS t2 ON t1.id = t2.id"
# several files with the same columns, read as one table
qsv sqlp SKIP_INPUT "SELECT * FROM read_csv(['jan.csv', 'feb.csv'])"read_csv decompresses .gz, .zst, and .zlib files transparently:
qsv sqlp SKIP_INPUT "SELECT * FROM read_csv('boston311-100.csv.gz')"
qsv sqlp SKIP_INPUT "SELECT * FROM read_csv('boston311-100.csv.zst')"Each function takes Polars' scan options as named arguments (name => value). An unknown option is an error that lists the valid ones, and n_rows is rejected in favor of LIMIT.
| Function | Options |
|---|---|
read_csv |
has_header, separator, quote_char, comment_prefix, skip_rows, skip_rows_after_header, skip_lines, encoding, new_columns, schema, schema_overrides, infer_schema, infer_schema_length, infer_schema_files, try_parse_dates, decimal_comma, null_values, ignore_errors, truncate_ragged_lines, missing_columns, extra_columns, include_file_paths
|
read_ndjson |
schema, schema_overrides, infer_schema_length, ignore_errors, include_file_paths
|
read_parquet |
schema, missing_columns, extra_columns, hive_partitioning, hive_schema, try_parse_hive_dates, include_file_paths
|
read_ipc |
hive_partitioning, hive_schema, try_parse_hive_dates, include_file_paths
|
# query an NDJSON file directly
qsv sqlp SKIP_INPUT "SELECT * FROM read_ndjson('events.ndjson')"
# infer the schema from the whole file (by default only the first 100 rows are used,
# so a key that first appears later is dropped)
qsv sqlp SKIP_INPUT "SELECT * FROM read_ndjson('events.ndjson', infer_schema_length => NULL)"
# type a column, and read a nested object as its JSON text
qsv sqlp SKIP_INPUT \
"SELECT * FROM read_ndjson('events.ndjson', schema_overrides => {'date': 'DATE', 'payload': 'VARCHAR'})"CSV output can't hold nested data: a nested object column fails with "CSV format does not support nested data" unless you read it as VARCHAR, as above.
SKIP_INPUT also works when the query needs no input at all, which is handy for testing expressions. The generate_series(start, stop[, step]) (includes stop) and range(start, stop[, step]) (excludes stop) table functions generate rows:
qsv sqlp SKIP_INPUT "SELECT 1 AS one, '2' AS two, 3.0 AS three"
qsv sqlp SKIP_INPUT "SELECT * FROM generate_series(1, 5)"Inner / left / right / full / cross joins, USING, and NATURAL JOIN work as in Postgres. Join keys of different numeric types (integer vs. decimal) are compared in a common type. JOIN … ON may contain subqueries, including correlated ones, and its constraint may be parenthesized. Polars adds LEFT SEMI / RIGHT ANTI joins and UNION [ALL] BY NAME.
# inner join with a post-join filter
qsv sqlp wcp.csv country_continent.csv \
"SELECT wcp.AccentCity, wcp.Population, country_continent.continent
FROM wcp
JOIN country_continent ON wcp.Country = country_continent.country
WHERE wcp.Population > 1000000
ORDER BY wcp.Population DESC"
# NATURAL JOIN — match on identically-named columns automatically
qsv sqlp data1.csv data2.csv data3.csv \
"SELECT COLUMNS('^[^:]+$') FROM data1 NATURAL JOIN data2 NATURAL JOIN data3 ORDER BY COMPANY_ID"
# chained SEMI / ANTI joins
qsv sqlp tbl_a.csv tbl_b.csv tbl_c.csv \
"SELECT * FROM tbl_a
RIGHT ANTI JOIN tbl_b USING (b)
LEFT SEMI JOIN tbl_c USING (c)"
# stack yearly exports with UNION ALL BY NAME (column order need not match)
qsv sqlp nyc311-2023.csv nyc311-2024.csv \
"SELECT * FROM nyc311_2023 UNION ALL BY NAME SELECT * FROM nyc311_2024"
# join keys of different numeric types
qsv sqlp t.csv u.csv "SELECT t.x, u.y FROM t JOIN u ON t.x = u.y / 10.0"
# a correlated subquery in JOIN ... ON: each t1 row joins only t2's top match
qsv sqlp t1.csv t2.csv \
"SELECT t1.a, t2.a AS x FROM t1 JOIN t2
ON t2.b = t1.a AND t2.a = (SELECT MAX(y.a) FROM t2 y WHERE y.b = t1.a)"Beyond =, <, IN, and LIKE, the Postgres pattern operators are available, plus MySQL's <=>:
| Operator | Meaning |
|---|---|
^@ |
starts-with |
~~ / ~~*
|
LIKE / case-insensitive ILIKE
|
!~~ / !~~*
|
NOT LIKE / case-insensitive NOT ILIKE
|
~ / ~*
|
regex contains (case-sensitive / insensitive) |
!~ / !~*
|
regex does-not-contain (case-sensitive / insensitive) |
<=> |
NULL-safe equality, same as IS NOT DISTINCT FROM (not in Postgres) |
# starts-with as a predicate and as a projected boolean
qsv sqlp wcp.csv "SELECT * FROM wcp WHERE City ^@ 'San'"
qsv sqlp wcp.csv "SELECT City, City ^@ 'San' AS starts_with_san FROM wcp"
# ILIKE-style ends-with, case-insensitive
qsv sqlp wcp.csv "SELECT City, City ~~* '%burg' AS ends_with_burg FROM wcp"
# regex operators combined with a numeric filter
qsv sqlp wcp.csv "SELECT * FROM wcp WHERE City ~ '^San' AND Population > 10000"
qsv sqlp wcp.csv "SELECT * FROM wcp WHERE City !~* 'ville$' AND Population > 10000"
# regexp_like(string, pattern [, flags]) — flags: 'c' (default), 'i', 'm'
qsv sqlp wcp.csv "SELECT * FROM wcp WHERE regexp_like(City, '^san', 'i')"
# regexp using a pattern from another column
qsv sqlp data.csv "SELECT idx, val FROM data WHERE val regexp pattern_col"
# a LIKE pattern built from an expression
qsv sqlp n.csv "SELECT s FROM n WHERE s LIKE '%' || 'an' || '%'"SELECT * ILIKE (not in Postgres) projects only the columns whose names match the wildcard, which is useful for grabbing a family of columns without listing them. COLUMNS('regex') does the same with a regular expression.
# return every column whose name matches '%a%e%' (e.g. Name, State), case-insensitive
qsv sqlp wcp.csv "SELECT * ILIKE '%a%e%' FROM wcp ORDER BY Country"# CASE (searched and simple forms)
qsv sqlp wcp.csv \
"SELECT City,
CASE WHEN Population > 1000000 THEN 'mega'
WHEN Population > 100000 THEN 'large'
ELSE 'small' END AS tier
FROM wcp"
# COALESCE + NULLIF to default empty strings
qsv sqlp wcp.csv "SELECT COALESCE(NULLIF(Region, ''), 'unknown') AS region FROM wcp"
# string functions
qsv sqlp wcp.csv "SELECT lower(City), substr(Country, 1, 2) FROM wcp WHERE starts_with(City, 'San')"
# REGEXP_REPLACE: first match only, unless you pass 'g' ('i' ignores case)
qsv sqlp people.csv "SELECT REGEXP_REPLACE(phone, '[^0-9]', '', 'g') AS digits FROM people"
# FILTER clause on aggregates — per-aggregate predicates in one GROUP BY pass
qsv sqlp sales.csv \
"SELECT region,
SUM(amount) AS total,
SUM(amount) FILTER (WHERE amount > 100) AS total_over_100,
COUNT(*) FILTER (WHERE status = 'refunded') AS refund_count
FROM sales GROUP BY region ORDER BY region"
# ordered-set percentiles
qsv sqlp t.csv "SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) AS median FROM t"
# approximate (sketch-based) quantile — faster than QUANTILE_CONT on large inputs (not in Postgres)
qsv sqlp wcp.csv \
"SELECT Country, APPROX_QUANTILE(Population, 0.9) AS p90
FROM wcp WHERE Population IS NOT NULL GROUP BY Country"
# number of set bits in an integer
qsv sqlp flags.csv "SELECT flags, BIT_COUNT(flags) AS n_set FROM flags"
# a typed numeric literal, and a LIMIT computed by a subquery
qsv sqlp t.csv "SELECT INTEGER '5' + x AS v FROM t"
qsv sqlp t.csv u.csv "SELECT x FROM t ORDER BY x LIMIT (SELECT COUNT(*) FROM u WHERE g = 1)"Two computed GROUP BY keys on the same column, such as GROUP BY ip % 3, ip % 2, work.
BOOL_AND, BOOL_OR, COUNT_IF, MAX_BY, MIN_BY, ANY_VALUE, APPROX_COUNT_DISTINCT, and REGEXP_EXTRACT are available. Unlike Postgres's substring/regexp_substr, REGEXP_EXTRACT returns an empty string, not NULL, when nothing matches.
qsv sqlp t.csv "SELECT side, BOOL_AND(flag), BOOL_OR(qty > 5), COUNT_IF(qty > 3),
MAX_BY(name, qty), MIN_BY(name, qty), APPROX_COUNT_DISTINCT(name) FROM t GROUP BY side"
qsv sqlp t.csv "SELECT REGEXP_EXTRACT(s, '[0-9]+') AS m, REGEXP_EXTRACT(s, '([a-z])([0-9])', 2) AS g FROM t"Dates & times. Typed DATE '…' literals, date ± whole days, DATE_TRUNC, CURRENT_DATE / CURRENT_TIME / CURRENT_TIMESTAMP, and bare YEAR, QUARTER, MONTH, WEEK, DAY, DAYOFWEEK, DAYOFYEAR, HOUR, MINUTE and SECOND functions are available. WEEK is the ISO week, so WEEK(DATE '2024-12-31') is 1. DAYOFWEEK numbers Sunday as 0.
qsv sqlp wcp.csv \
"SELECT WEEK(DATE '2024-12-31') AS wk, DAYOFWEEK(DATE '2024-12-31') AS dow,
DATE '2024-12-31' + 7 AS next_week FROM wcp LIMIT 1"
# -> 1,2,2025-01-07
# monthly totals
qsv sqlp sales.csv \
"SELECT DATE_TRUNC('month', CAST(ts AS TIMESTAMP)) AS month, SUM(amount) AS total
FROM sales GROUP BY month ORDER BY month"
# today's date
qsv sqlp orders.csv "SELECT id FROM orders WHERE CAST(due AS DATE) < CURRENT_DATE"Dollar-quoting (PostgreSQL syntax) avoids escaping quotes and apostrophes inside literals. In a double-quoted shell string the $ must be escaped as \$:
qsv sqlp tweets.csv "SELECT * FROM tweets WHERE handle = \$\$Diane's@Twitter\$\$"
# named tag form for literals containing both quote styles
qsv sqlp data.csv 'SELECT * FROM data WHERE col1 = $tag$Diane'\''s "horse"$tag$'CTEs (including WITH RECURSIVE), IN / EXISTS / scalar subqueries, and correlated subqueries work as in Postgres. A correlated subquery may use any condition on the outer row, not only equality. A correlated scalar subquery with no match returns NULL; one that matches two or more rows is an error, as in Postgres. As in Postgres, a FROM subquery's alias qualifies columns (a.x) but is not a table that another subquery can read; put the subquery in a WITH clause to reuse it.
Several comma-separated LATERAL derived tables work, as do aggregates without GROUP BY inside them.
qsv sqlp t.csv "SELECT t.name, hi.mx, lo.mn FROM t,
LATERAL (SELECT MAX(qty) AS mx FROM t AS t2 WHERE t2.side = t.side) AS hi,
LATERAL (SELECT MIN(qty) AS mn FROM t AS t3 WHERE t3.side = t.side) AS lo"# Common Table Expression
qsv sqlp wcp.csv \
"WITH big AS (SELECT * FROM wcp WHERE Population >= 1000000)
SELECT * FROM big WHERE STARTS_WITH(City, 'S')"
# IN subquery (uncorrelated — the inner query runs once)
qsv sqlp wcp.csv country_continent.csv \
"SELECT * FROM wcp
WHERE Country IN (SELECT country FROM country_continent WHERE continent = 'EU')"
# correlated EXISTS: countries with at least one city over 10 million
qsv sqlp wcp.csv \
"SELECT DISTINCT w.Country FROM wcp w
WHERE EXISTS (SELECT 1 FROM wcp b WHERE b.Country = w.Country AND b.Population > 10000000)"
# a correlated IN with an inequality on the outer row
qsv sqlp t.csv u.csv "SELECT g, x FROM t WHERE x IN (SELECT y / 10 FROM u WHERE u.y > t.x * 6)"
# a correlated scalar subquery (no match -> NULL)
qsv sqlp t1.csv t2.csv "SELECT a, (SELECT x.a FROM t2 x WHERE x.b = t1.a) AS s FROM t1"
# reuse a derived table: use WITH, not a FROM-subquery alias
qsv sqlp t.csv "WITH a AS (SELECT * FROM t WHERE g = 1) SELECT a.x FROM a WHERE a.x IN (SELECT x FROM a)"
# a recursive CTE: numbers 1 to 5
qsv sqlp SKIP_INPUT "WITH RECURSIVE r(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM r WHERE n < 5) SELECT n FROM r"
# walk a parent/child table, with each row's depth
qsv sqlp tree.csv "WITH RECURSIVE d AS (SELECT id, 0 AS depth FROM tree WHERE id = 1
UNION ALL SELECT tree.id, d.depth + 1 FROM tree JOIN d ON tree.parent = d.id) SELECT * FROM d"
# window function: rank cities within each country
qsv sqlp wcp.csv \
"SELECT City, Country, Population,
ROW_NUMBER() OVER (PARTITION BY Country ORDER BY Population DESC) AS rank_in_country
FROM wcp"
# moving average over each row and the 2 rows before it
qsv sqlp sales.csv \
"SELECT day, AVG(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma3
FROM sales"ROWS <n> PRECEDING frames work with AVG, SUM, MIN, MAX and COUNT. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW gives running totals.
GROUPING SETS (…), ROLLUP(…) and CUBE(…) work as in Postgres, and GROUPING(col) tells a subtotal's NULL key apart from a NULL in the data. Multi-argument GROUPING(a, b, …) and its alias GROUPING_ID pack one bit per argument, with the first argument most significant, so argument order matters.
# ROLLUP subtotals; --wnull-value labels the subtotal keys (otherwise written as empty fields)
qsv sqlp --wnull-value '(all)' allegheny_property_sales.csv \
"SELECT INSTRTYPDESC, SALEDESC, COUNT(*) AS sales, GROUPING(SALEDESC) AS is_subtotal
FROM allegheny_property_sales
WHERE INSTRTYPDESC IN ('DEED','SHERIFF DEED') AND SALEDESC IN ('VALID SALE','OTHER')
GROUP BY ROLLUP(INSTRTYPDESC, SALEDESC)
ORDER BY INSTRTYPDESC, is_subtotal, SALEDESC"If the sql argument ends in .sql, it's read as a script: queries separated by ;, single-line -- comments allowed, and the result of the last query is returned. Earlier queries typically build tables (CREATE TABLE … AS or CREATE TEMP TABLE … AS) consumed by later ones. In long scripts, TRUNCATE TABLE <name>; frees a table's memory before the script ends.
Scripts can also use CREATE VIEW <name> AS … and DROP VIEW <name>. DESCRIBE <table> lists a table's columns (one row per column: column_name, column_type, e.g. qsv sqlp data.csv "DESCRIBE data").
-- pipeline.sql — staged build, last SELECT is the result
CREATE TABLE eu AS
SELECT country FROM country_continent WHERE continent = 'EU';
CREATE TABLE eu_cities AS
SELECT w.* FROM wcp w JOIN eu ON w.Country = eu.country;
TRUNCATE TABLE eu; -- free intermediate memory
SELECT Country, COUNT(*) AS cities, MAX(Population) AS biggest
FROM eu_cities GROUP BY Country ORDER BY cities DESC;qsv sqlp wcp.csv country_continent.csv pipeline.sql --format json --output eu_summary.json--format accepts csv (default), json, jsonl, parquet, arrow, and avro. Columnar formats take --compression (and Parquet adds --compress-level and --statistics).
qsv sqlp wcp.csv "SELECT * FROM wcp WHERE Population > 1000000" \
--format parquet --compression zstd --compress-level 9 --statistics \
--output big_cities.parquet
# snappy round-trip via .sz extension (auto compress/decompress)
qsv sqlp wcp.csv.sz "SELECT * FROM wcp WHERE Population > 10000" --output result.csv.szCSV-output date/number formatting is controlled with --datetime-format, --date-format, --time-format, --float-precision, --decimal-comma, and --wnull-value (the text written for NULLs).
Avro output converts the types Avro lacks before writing:
| Result type | Written to Avro as |
|---|---|
TINYINT, SMALLINT, UTINYINT, USMALLINT
|
int |
UINT, UBIGINT
|
long; a UBIGINT above 2⁶³−1 is an error that names the column |
TIMESTAMPTZ |
a UTC timestamp, the same instant |
TIMESTAMP(9) (nanoseconds) |
a microsecond timestamp (the only conversion that drops digits) |
TIME |
a string, HH:MM:SS plus any fraction (03:04:05.123456789) |
| intervals/durations | an ISO 8601 string (P1DT3H4M5.123456S) |
The same conversions apply to values inside lists. Struct columns from nested Parquet or Arrow input whose fields need converting are not converted, and fail.
Without a schema, Polars infers column types by scanning rows. A cached Polars schema skips that scan and lets you choose types.
-
Location. The schema lives in
<filename>.pschema.jsonnext to the input, named after the full file name:data.csv→data.csv.pschema.json. -
Creating it.
--cache-schemawrites it on the first run.qsv schema --polarsgenerates it from the stats cache. -
Automatic use. If the file exists,
sqlpuses it automatically, even without--cache-schema. -
Editing. Change a column's type to cast it on read. For example, replace
"Float64"with{"Decimal": [10, 3]}for explicit precision and scale. Valid types includeBoolean, the sized ints/uints,Float32/Float64,String,Date,Datetime,Duration,Time,Categorical,Decimal, andEnum. -
Types derived from the stats cache:
- Integer columns with no negative values become the narrowest unsigned type (e.g.
UInt8). - Floats are always
Float64. Edit a column toFloat32to save memory if ~7 significant digits suffice. - A date column is
Date/Datetimeonly if Polars can parse its raw values. Layouts such as04/18/2019 09:55:45 PM, and day-first dates without--prefer-dmy/QSV_PREFER_DMY, are typedString.
- Integer columns with no negative values become the narrowest unsigned type (e.g.
# first run infers + caches the schema; edit wcp.csv.pschema.json to cast, then re-run
qsv sqlp --cache-schema wcp.csv "SELECT * FROM wcp WHERE Population > 1000000"For date parsing on input, add --try-parsedates (falls back to strings on failure). --infer-len controls how many rows are scanned for inference (0 = full scan).
# inspect the query plan without running it
qsv sqlp wcp.csv "EXPLAIN SELECT * FROM wcp WHERE Population > 10000 ORDER BY Population DESC LIMIT 20"EXPLAIN takes queries only. EXPLAIN DROP TABLE t, EXPLAIN CREATE VIEW … and other non-query statements fail with EXPLAIN supports queries only (dathere/qsv#5055). (Changed in 29.0.0.)
When a query's result is too big for memory, use --streaming. sqlp then writes the result batch by batch as it is computed instead of collecting it first. This works for csv, jsonl, parquet and arrow output; json and avro still collect. Queries run on Polars' streaming engine either way. An --output file only appears, or replaces the old one, once the query succeeds. On stdout, rows go out as they are computed, so a failure partway through leaves partial output; check the exit code before using it.
# a big result to a file, without holding it all in memory
qsv sqlp big.csv --streaming --format parquet -o big.parquet "SELECT * FROM big WHERE year >= 2020"--no-optimizations disables Polars' non-default query optimizations, which helps when an optimization causes a query to error. Run scoresql first to catch anti-patterns and estimate cost before executing.
Warning
Literal blank lines become all-null rows. Polars materializes every blank line in a CSV as a row of nulls, while qsv's own CSV reader skips them. So sqlp, pivotp and joinp see more rows than qsv count reports for the same file, SELECT * emits an extra all-empty record per blank line, and pivotp gets a null-key group. Inner joins in joinp drop the null row because null keys don't match; left joins keep it. If your file may contain blank lines, normalize it first: qsv input data.csv > clean.csv (or pipe qsv input data.csv | qsv sqlp - …). See also Indexing, Compression & Diff.
-
A signed integer times an unsigned column gives NULL for the negative rows (pola-rs/polars#30742). For example,
CASE WHEN side = 'buy' THEN 1 ELSE -1 END * CAST(qty AS UINTEGER)returns NULL for the-1rows, so aSUMover it skips them. Cached schemas type non-negative integer columns as unsigned, so this can happen without an explicit cast. Cast to a signed type first (CAST(qty AS BIGINT)). The upstream fix is pending in dathere/qsv#5045. -
COUNT(*)over aUNION ALL(with or withoutLIMIT) panics withnot implemented for dtype UInt128(pola-rs/polars#30523). Count a column instead (COUNT(g)).
See also: /docs/help/sqlp.md, Polars SQL reference, scoresql, pivotp, joinp, Recipe: Build a Data Pipeline.
Analyze a SQL query against the stats / frequency / moarstats caches and the query plan before you run it. Output is a human-readable performance report (default) or JSON. Plan analysis uses Polars by default, or DuckDB with --duckdb.
Missing caches are generated automatically, using qsv stats --everything --stats-jsonl and qsv frequency --frequency-jsonl.
Example: score a join query before running it
qsv scoresql wcp.csv country_continent.csv \
"SELECT wcp.AccentCity, country_continent.continent
FROM wcp JOIN country_continent ON wcp.Country = country_continent.country"The report scores five categories: type optimization, join cardinality, filter selectivity, data distribution, and query patterns (anti-patterns such as SELECT * and cartesian joins). It also includes the query plan (EXPLAIN output), suggestions, and cache freshness.
Example: get JSON output for CI gating
qsv scoresql --json wcp.csv "SELECT * FROM wcp" | jq '.score, .rating, .suggestions'The JSON has the top-level keys score, max_score, rating, breakdown (an array of {category, score, max, detail}), suggestions, plan, and cache_status. The SELECT * above shows up in the "Query patterns" breakdown and in suggestions.
Example: use DuckDB for plan analysis instead of Polars
qsv scoresql --duckdb nyc311.csv "SELECT * FROM nyc311 WHERE status = 'active'"Example: score a query loaded from a .sql file
qsv scoresql data.csv complex_query.sqlOnly the last query in the file is scored.
See also: /docs/help/scoresql.md, sqlp, stats, frequency, Stats Cache & Caching.
Pivot or group-and-aggregate CSV data using Polars. Output is CSV; stdin is not supported. The command has two modes, selected by whether you pass the <on-cols> positional argument:
-
Pivot mode,
qsv pivotp <on-cols> <input>. The--indexcolumns become rows, the distinct<on-cols>values become new columns, and--valuesis aggregated into each cell. At least one of--index/--valuesmust be given; the unspecified one defaults to "all remaining columns". This is what Postgres users would write withcrosstab()or a stack ofFILTERaggregates. -
Group-by mode,
qsv pivotp <input>(no<on-cols>). A plainGROUP BYaggregation.--indexis required (it's the group key). If--valuesis omitted you get a singlecountcolumn.
For the full flag list see
/docs/help/pivotp.md; the subsections below are the workflow view.
# Complaint Type × Year, counting Unique Keys per cell
qsv pivotp 'Complaint Type' NYC_311_SR_2010-2020-sample-1M.csv \
--index YearReported \
--values 'Unique Key' \
--agg len \
--output nyc311_pivot.csv(Assumes a YearReported column — derive it first with datefmt --formatstr '%Y' --new-column YearReported.)
# Sale Year × Property Class, average Sale Price per cell
qsv pivotp 'Property Class' allegheny_property_sales.csv \
--index 'Sale Year' \
--values 'Sale Price' \
--agg meanPivot on multiple columns and/or aggregate multiple value columns — the generated column names join the parts with --col-separator (default _):
qsv pivotp 'Property Class,Sale Year' allegheny_property_sales.csv \
--index Municipality \
--values 'Sale Price,Lot Area' \
--agg mean --col-separator '::'# count rows per Borough (no --values → a single "count" column)
qsv pivotp NYC_311_SR_2010-2020-sample-1M.csv \
--index Borough \
--output borough_counts.csv
# sum/mean per composite group
qsv pivotp allegheny_property_sales.csv \
--index 'Municipality,Property Class' \
--values 'Sale Price' \
--agg mean--agg (default smart) accepts:
| Function | Result |
|---|---|
first / last
|
first / last value encountered |
sum / min / max / mean / median
|
the usual reductions |
quantile@<p> (alias q@<p>) |
linear-interpolation quantile at p ∈ [0, 1] (like percentile_cont) |
len |
count of values |
item |
the single value in the group — errors if there's more than one (pivot mode only) |
smart |
auto-pick based on the value column's type & statistics |
q@0.5 equals median, and q@0.95 / q@0.99 are the usual high-percentile choices for latency / SLA work:
# p95 latency per Borough
qsv pivotp --index Borough --values latency_ms --agg q@0.95 nyc311.csvImportant
Group-by mode does not support --agg item or --agg none.
With --agg smart (the default), pivotp reads the stats cache for the value column and picks an aggregation from its data type and distribution, so you usually don't have to think about it. In group-by mode smart resolves to len (count). In pivot mode it considers type, cardinality, sparsity, sign distribution, and sort order.
With a single --values column it also uses the distribution-shape statistics of moarstats --advanced: outlier profile, Pearson and moment skewness (G1), MAD/stddev, mean/median divergence, quartile dispersion, mode count, kurtosis, bimodality, entropy, and the Gini coefficient. If the input's stats cache doesn't have them yet, pivotp runs moarstats --advanced once (the same cache viz smart --smarter builds) and reuses it on later runs. That first pass reads the whole file; skip it with --no-moarstats. It is also skipped with an explicit --delimiter, stdin, and special formats.
How it decides:
| Column profile | Picks |
|---|---|
| a clean numeric column | sum |
| highly variable (CV above 100%, i.e. stddev larger than the mean), heavy-tailed, outlier-laden, or highly skewed (|G1| ≥ 1, or Pearson skewness when G1 isn't cached) | median |
| mixed sign (over 20% each of negative and positive values) |
mean, to avoid sum cancellation |
| sparse, near-uniform, bimodal (bimodality coefficient ≥ 0.60 and negative excess kurtosis, so skewed single-peaked amounts aren't mistaken for two-peaked ones), or more than 10 tied modes | len |
| a single distinct value | first |
The median/mean and quartile-dispersion ratios are only used on positive data. The chosen function and the reasoning are printed to stderr (silence with -q). Smart only engages with a single value column; with multiple value columns it falls back to first. Because the choice depends on heuristics, pass an explicit --agg (e.g. --agg median) when you need the same result across qsv versions.
# add a grand-total row (sums every numeric, non-index column)
qsv pivotp 'Property Class' allegheny_property_sales.csv \
--index 'Sale Year' --values 'Sale Price' --agg sum --grand-total
# subtotal after each first-index group (requires 2+ index columns), custom label
qsv pivotp 'Property Class' allegheny_property_sales.csv \
--index 'Municipality,Sale Year' --values 'Sale Price' --agg sum \
--subtotal --total-label 'Subtotal'--grand-total needs at least one index column. --subtotal needs at least two (the label lands in the second index column) and is pivot-mode only.
For subtotals in long (un-pivoted) form, use sqlp with GROUP BY ROLLUP(…) — see Grouping sets, ROLLUP & CUBE.
- Output is always in first-appearance order: rows follow their index values' first appearance, and pivoted columns their
<on-cols>values'.--maintain-orderis accepted and has no effect. -
.sz(snappy) input and--outputare handled likesqlp/joinp, as areQSV_COMMENT_CHAR,QSV_POLARS_FLOAT_PRECISIONandQSV_OUTPUT_BOM. -
--sort-columnssorts the generated pivot columns alphabetically (pivot mode only). -
--max-columns <n>(default100000) refuses a pivot that would produce more result columns than that;0means no limit. It always applies in pivot mode. -
--validatechecks the<on-cols>cardinality first and warns if the result would have more than 1,000 columns. It is informational only and pivot-mode only. -
--try-parsedates,--infer-len <n>(0scans the whole file),--decimal-comma(needs a non-comma delimiter), and--ignore-errorscontrol CSV parsing. - Delimiters resolve like
sqlp/joinp:--delimiter, elseQSV_DEFAULT_DELIMITER, else a.tsv/.tab/.ssvextension on the input or--outputfile, else,(stdout defaults to,).
# cardinality check before committing to a wide pivot
qsv pivotp 'Complaint Type' nyc311.csv --index Borough --values 'Unique Key' --validatepivotp automatically picks up a <filename>.pschema.json (from schema --polars) when present, unless you override inference with a non-default --infer-len. That lets Polars skip the type-inference scan.
See also: /docs/help/pivotp.md, sqlp (alternative via GROUP BY with FILTER/CASE; SQL PIVOT is not supported), stats / moarstats (feed smart aggregation), transpose, schema --polars.
See Joins & Set Ops → joinp for the full treatment. The headline capabilities for SQL-style work:
- Asof joins — time-series-aware, matches each left row to the nearest prior right row.
-
Non-equi joins — range-based matches via
--non-equi <expr>, an expression over_left/_right-suffixed columns. -
Pre-join filtering —
--filter-left/--filter-rightevaluate Polars SQL before the join, cheaper than filtering after. -
Column coalescing —
--coalescemerges identically-named columns post-join.
- Command Reference (index)
-
Joins & Set Ops —
joinpin depth -
Aggregation & Statistics —
statscache feedsscoresql -
Validation & Schema —
schema --polarsproduces.pschema.jsonfor sqlp/joinp/pivotp - Stats Cache & Caching
- Polars SQL docs
- Cookbook → Build a Data Pipeline
- Cookbook → Larger-than-RAM CSV
qsv — GitHub · Releases · Discussions · crates.io · qsv pro · Try it online · Benchmarks · DeepWiki · DOI · Licensed MIT · Third-party notices · AI Policy
qsv was made possible by datHere: standards-based, best-of-breed, open source solutions to make your Data Useful, Usable & Used.
Edit this page: Contributing to the Wiki
Home · Why qsv? · Tier legend
- Whirlwind Tour · Notebooks · Lessons & Exercises
- Benchmarks · Interactive Benchmarks
- Visualizations Gallery
- Neuro-symbolic, Localizable, Data Schematics
- Feature Flags · Goals/Non-goals · Testing
- Talks: NYC SOD 2022 · csv,conf,v8 · PyCon US 2025 · csv,conf,v9 · NYC SOD 2026
- "Have we achieved ACI?" series: 1 · 2 · 3 · 4
- Sponsor
- All Commands (index)
- Selection & Inspection
- Transform & Reshape
- Aggregation & Statistics
- Joins & Set Ops
- SQL & Polars
- Validation & Schema
- Metadata Profiling (profile)
- Conversion & I/O
- Geospatial
- Visualization (viz)
- HTTP & Web
- Get & Disk Cache
- Scripting (Luau / Python)
- Indexing, Compression & Diff
- AI & Documentation
- Recipes index
- Inspect an Unknown CSV
- Clean & Normalize
- Geographic Enrichment
- Date Enrichment
- CKAN Integration
- DCAT-US v3 for data.gov
- JSON Schema Validation
- Round-Trip SAS / Stata / SPSS
- Build a Data Pipeline
- Stats → Insights
- Data-Quality Signals with moarstats
- Group By
- Fetch & Cache
- Larger-than-RAM CSV
- Diff & Audit
- Multi-table Joins
- Synthesize Fake Data
- Performance Tuning
- Benchmarks
- Tuned csv Fork Benchmarks
- Security & Dependency Audits
- Environment Variables
- Stats Cache & Caching
- Lookup Tables
- MiniJinja Templating