Downloads 6.8kthis week

GitHub stars 107

Extension descriptor (YAML)

Support for PSQL, a piped SQL dialect for DuckDB

Compatibility Matrix

This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.

Installing and Loading

INSTALL psql FROM community;
LOAD psql;

Example

from 'https://raw.githubusercontent.com/ywelsch/duckdb-psql/main/example/invoices.csv' |>
where invoice_date >= date '1970-01-16' |>
extend
  0.8 as transaction_fees,
  total - transaction_fees as income |>
where income > 1 |>
select
  customer_id,
  sum(income) as sum_income
  group by all |>
order by sum_income desc |>
limit 10 |>
as invoices
  join 'https://raw.githubusercontent.com/ywelsch/duckdb-psql/main/example/customers.csv'
    as customers
  on invoices.customer_id = customers.customer_id |>
select
  customer_id,
  last_name || ', ' || first_name as name,
  sum_income |>
order by sum_income desc, name;

About psql

PSQL extends DuckDB's SQL with a pipe syntax to provide simple composable queries. It's a lightweight variant of piped languages such as PRQL and Kusto, yet leveraging the full power of DuckDB's SQL.

Every stage continues a FROM-first query on the result of the previous stage: A |> B is FROM (A) B. A stage can therefore contain anything that can follow the first table of a FROM-first query, such as joins, SELECT, WHERE, GROUP BY or ORDER BY, as well as an alias for that table, which needs AS, as in |> as invoices above.

Pipelines can be used anywhere a query can appear: in subqueries, CTEs, views, CREATE TABLE ... AS, INSERT INTO ..., and so on. A final |> to 'file' stage (or |> copy to 'file') writes the result of a pipeline to a file, a short-hand for COPY (pipeline) TO 'file'.

PSQL also supports the operators of BigQuery's pipe syntax that map directly to DuckDB's SELECT:

• |> extend expr [as alias], ... is |> select *, expr [as alias], ...
• |> set column = expr, ... is |> select * replace (expr as column, ...)
• |> drop column, ... is |> select * exclude (column, ...)
• |> rename column [as] new_name, ... is |> select * rename (column as new_name, ...)
• |> distinct is |> select distinct *

from 'https://raw.githubusercontent.com/ywelsch/duckdb-psql/main/example/invoices.csv' |>
drop billing_address, billing_state, billing_postal_code |>
rename billing_city as city, billing_country as country |>
set invoice_date = invoice_date::date |>
extend round(total * 0.8, 2) as income |>
limit 3;

PSQL requires DuckDB 2.0 or later, whose parser is extensible. The pipe syntax is a grammar extension, which loading PSQL activates on every connection. A connection can opt out with reset active_grammar_extensions;, and opt in again with call psql_enable();.

See the README for more examples, and for the BigQuery operators that PSQL leaves out because DuckDB's SQL doesn't need them.

Added Functions

This extension does not add any functions.

Overloaded Functions

This extension does not add any function overloads.

Added Types

This extension does not add any types.

Added Settings

This extension does not add any settings.