Polars SQL

Querying Polars DataFrames with SQL — supported syntax, ergonomic extensions, and the gaps to know about

Polars ships a SQL interface that translates queries into its own expressions and runs them on the Polars engine — there is no separate SQL database underneath. Queries execute lazily with the full query optimizer, so SQL gets the same performance as the native API. Syntax follows PostgreSQL where possible. See the Polars SQL introduction for how frames are registered as tables, and the SQL reference for the full list of supported clauses and functions.

Polars supports a subset of SQL — SELECT, CREATE TABLE AS, CTEs, joins, set operations — but not INSERT/UPDATE/DELETE, PIVOT/UNPIVOT statements, or COPY … TO. Reshaping and writing files happen through the DataFrame API instead.

Loading data

Files are read with table functions — read_csv, read_parquet, read_json, read_ndjson — directly in the FROM clause (bare path literals like FROM 'data.csv' are not supported). Reads are lazy, so only the rows and columns a query needs are actually loaded.

sql

SELECT * FROM read_csv('data/penguins.csv') LIMIT 5;
SELECT * FROM read_parquet('data/penguins.parquet');

-- Persist a result as a named table for repeated queries
CREATE TABLE penguins AS SELECT * FROM read_csv('data/penguins.csv');

The examples below assume a penguins table with columns species, island, bill_length_mm, bill_depth_mm, flipper_length_mm, body_mass_g, sex, and year.

Inspecting schema and types

There is no DESCRIBE or SUMMARIZE statement, but Polars prints column dtypes in every result header, so a zero-row query is a quick schema check.

sql

SHOW TABLES;                          -- list registered tables
SELECT * FROM penguins LIMIT 0;       -- dtypes without data
EXPLAIN SELECT * FROM penguins WHERE body_mass_g > 4000;  -- optimized query plan

Selecting columns

Beyond plain column lists, Polars SQL supports the same wildcard modifiers DuckDB popularized — EXCLUDE, RENAME, REPLACE — plus regex and pattern-based column selection.

sql

SELECT species, body_mass_g FROM penguins;

-- Everything except a few columns
SELECT * EXCLUDE (year, sex) FROM penguins;

-- Everything, renaming one column
SELECT * RENAME (body_mass_g AS mass) FROM penguins;

-- Everything, but transform one column in place
SELECT * REPLACE (body_mass_g / 1000.0 AS body_mass_g) FROM penguins;

-- Select columns by regex or by ILIKE pattern
SELECT COLUMNS('.*_mm$') FROM penguins;
SELECT * ILIKE 'bill_%' FROM penguins;

Filtering rows

WHERE supports the usual comparison, membership, range, and pattern predicates, including regular expressions via regexp_like.

sql

SELECT * FROM penguins WHERE species = 'Gentoo';
SELECT * FROM penguins WHERE body_mass_g > 4000 AND sex = 'male';
SELECT * FROM penguins WHERE island IN ('Biscoe', 'Dream');
SELECT * FROM penguins WHERE bill_length_mm BETWEEN 40 AND 50;
SELECT * FROM penguins WHERE sex IS NULL;                     -- missing values
SELECT * FROM penguins WHERE species LIKE 'Adel%';            -- pattern match
SELECT * FROM penguins WHERE species ILIKE 'adel%';           -- case-insensitive
SELECT * FROM penguins WHERE regexp_like(species, '^Adel');   -- regex

-- Order and page
SELECT * FROM penguins ORDER BY body_mass_g DESC NULLS LAST LIMIT 10;
SELECT * FROM penguins ORDER BY body_mass_g DESC LIMIT 10 OFFSET 10;

Counting and distinct values

sql

SELECT count(*) FROM penguins;                      -- total rows
SELECT count(body_mass_g) FROM penguins;            -- non-null values
SELECT count(DISTINCT species) FROM penguins;       -- distinct count

-- Frequency table, most common first
SELECT species, count(*) AS n
FROM penguins
GROUP BY species
ORDER BY n DESC;

-- Distinct combinations
SELECT DISTINCT species, island FROM penguins;

Summarizing and aggregating

Aggregate functions collapse rows; GROUP BY does it per group. GROUP BY ALL infers the grouping columns from the non-aggregated columns in the SELECT list, and ordinal references (GROUP BY 1) work too.

sql

-- Whole-table summary
SELECT
  count(*)               AS n,
  avg(body_mass_g)       AS mean_mass,
  median(body_mass_g)    AS median_mass,
  stddev(body_mass_g)    AS sd_mass,
  min(body_mass_g)       AS min_mass,
  max(body_mass_g)       AS max_mass,
  quantile_cont(body_mass_g, 0.9) AS p90
FROM penguins;

-- Grouped summary; GROUP BY ALL = GROUP BY species, island
SELECT
  species,
  island,
  count(*)         AS n,
  avg(body_mass_g) AS mean_mass
FROM penguins
GROUP BY ALL
ORDER BY mean_mass DESC;

-- Filter on aggregates with HAVING
SELECT species, avg(body_mass_g) AS mean_mass
FROM penguins
GROUP BY species
HAVING count(*) > 50;

-- Conditional aggregation (counts per category in one row)
SELECT
  count(*) FILTER (WHERE sex = 'male')   AS n_male,
  count(*) FILTER (WHERE sex = 'female') AS n_female
FROM penguins;

Other handy aggregates: variance(), quantile_disc(), corr(x, y), first() / last(), and array_agg(col) to collect values into a list per group. mode() and approx_count_distinct() are not available.

Window functions

Windows compute across rows without collapsing them — ranks, lags, group-relative values. Polars also supports QUALIFY, which filters on a window result directly, without a subquery.

sql

SELECT
  species,
  body_mass_g,
  -- rank within each species, heaviest first
  rank() OVER (PARTITION BY species ORDER BY body_mass_g DESC) AS mass_rank,
  -- each penguin's mass relative to its species mean
  body_mass_g - avg(body_mass_g) OVER (PARTITION BY species)   AS mass_vs_mean,
  -- previous value in year order
  lag(body_mass_g) OVER (ORDER BY year)                        AS prev_mass
FROM penguins;

-- Top 2 per species, no subquery needed
SELECT species, body_mass_g,
       rank() OVER (PARTITION BY species ORDER BY body_mass_g DESC) AS r
FROM penguins
QUALIFY r <= 2;

rank(), dense_rank(), row_number(), lag(), lead(), and aggregates over windows are supported; ntile(), percent_rank(), and cume_dist() are not.

Reshaping: wide ⇄ long

There are no PIVOT/UNPIVOT statements — that's pivot and unpivot in the DataFrame API. In pure SQL, pivot with conditional aggregation and unpivot with UNION ALL.

sql

-- Long → wide: one column per island, mean mass in the cells
SELECT
  species,
  avg(CASE WHEN island = 'Biscoe'    THEN body_mass_g END) AS biscoe,
  avg(CASE WHEN island = 'Dream'     THEN body_mass_g END) AS dream,
  avg(CASE WHEN island = 'Torgersen' THEN body_mass_g END) AS torgersen
FROM penguins
GROUP BY species;

-- Wide → long: stack measurement columns into key/value rows
SELECT species, 'bill_length_mm' AS measurement, bill_length_mm AS mm FROM penguins
UNION ALL
SELECT species, 'bill_depth_mm', bill_depth_mm FROM penguins
UNION ALL
SELECT species, 'flipper_length_mm', flipper_length_mm FROM penguins;

Joins

Along with the standard join types, Polars SQL exposes its native SEMI and ANTI joins — filter one table by (non-)existence of matches in another, without duplicating columns.

sql

-- Given a second table `islands(island, region)`
SELECT p.species, p.island, i.region
FROM penguins p
JOIN islands i ON p.island = i.island;      -- INNER (default)

SELECT p.species, i.region
FROM penguins p
LEFT JOIN islands i ON p.island = i.island; -- keep all penguins

-- USING when the key column shares a name
SELECT * FROM penguins JOIN islands USING (island);

-- Keep penguins with (SEMI) / without (ANTI) a match — no columns added
SELECT * FROM penguins SEMI JOIN islands USING (island);
SELECT * FROM penguins ANTI JOIN islands USING (island);

-- Also supported: RIGHT JOIN, FULL JOIN, CROSS JOIN,
-- and UNION / UNION ALL / INTERSECT / EXCEPT set operations.

CTEs and subqueries

Common Table Expressions (WITH) name intermediate results and keep queries readable — prefer them over deeply nested subqueries. See the CTE docs.

sql

WITH heavy AS (
  SELECT * FROM penguins WHERE body_mass_g > 4500
)
SELECT species, count(*) AS n
FROM heavy
GROUP BY species;

-- Chain multiple CTEs
WITH per_species AS (
  SELECT species, avg(body_mass_g) AS mean_mass
  FROM penguins GROUP BY species
),
ranked AS (
  SELECT *, rank() OVER (ORDER BY mean_mass DESC) AS r FROM per_species
)
SELECT * FROM ranked WHERE r <= 2;

-- Subqueries work in FROM and IN
SELECT * FROM penguins
WHERE island IN (SELECT island FROM penguins WHERE species = 'Gentoo');

Handling missing values

sql

SELECT
  coalesce(sex, 'unknown') AS sex,         -- replace NULL with a default
  count(*) AS n
FROM penguins
GROUP BY ALL;

-- Drop rows with any NULL in key columns
SELECT * FROM penguins
WHERE body_mass_g IS NOT NULL AND sex IS NOT NULL;

-- Mean imputation
SELECT
  coalesce(body_mass_g, avg(body_mass_g) OVER ()) AS body_mass_g
FROM penguins;

-- ifnull() and nullif() also work
SELECT ifnull(sex, 'unknown') AS sex, nullif(species, 'Adelie') AS sp
FROM penguins;

Types and casting

SQL type names map onto Polars dtypes (INTEGER → Int64, DOUBLE → Float64, VARCHAR → String, and so on); results always display their dtypes.

sql

SELECT
  body_mass_g::DOUBLE        AS mass_dbl,       -- :: shorthand
  CAST(year AS VARCHAR)      AS year_str,
  try_cast('abc' AS INTEGER) AS safe,           -- returns NULL instead of erroring
  DATE('2026-06-15')         AS d
FROM penguins;

Dates and strings

Temporal extraction uses date_part / EXTRACT, formatting uses strftime, and parsing uses strptime. (date_trunc and INTERVAL arithmetic are not supported — reach for the DataFrame API's temporal expressions when you need those.)

sql

SELECT
  date_part('year', DATE('2026-06-15'))       AS yr,
  EXTRACT(month FROM DATE('2026-06-15'))      AS mo,
  strftime(DATE('2026-06-15'), '%Y-%m')       AS formatted,
  strptime('15/06/2026', '%d/%m/%Y')          AS parsed;

SELECT
  upper(species)                 AS up,
  length(species)                AS len,
  replace(species, ' ', '_')     AS slug,
  substr(species, 1, 3)          AS abbrev,
  concat(species, '-', island)   AS combo,
  split_part('Biscoe-1', '-', 1) AS prefix,
  starts_with(species, 'Adel')   AS is_adelie
FROM penguins;

Note regexp_replace is not available; regexp_like (in WHERE) and replace (literal) are.

Exporting results

There is no COPY … TO. Every query result is a Polars DataFrame, so writing happens on the frame side — df.write_csv(), df.write_parquet() — or through whatever host tool wraps the SQL context.

A typical workflow: SELECT * FROM read_csv(…) LIMIT 0 to see the schema → SELECT … WHERE to filter → GROUP BY ALL to aggregate → QUALIFY or a CTE to refine → hand the resulting frame to the DataFrame API for reshaping or writing. When SQL runs out, remember it's the same engine underneath — anything missing here exists as an expression.

Polars SQL

On this page