PSQL for DuckDB

I am, and always will be, a fan of the dplyr syntax from the R ecosystem. It just offers such a nice reasoning for your code. You can read code from top to bottom, from left to right, and it just makes sense.

Stuff like this:

df %>%
  filter(x > 10) %>%
  select(y, z) %>%
  group_by(y) %>%
  summarise(mean(z))

Recently though, there have been more and more variants of SQL that also allow for this manner of readability. One of those is PSQL for DuckDB. It is pretty neat!

First, install it:

install psql from community;

Next, load it:

load psql;

And then you can use it like this:

from 'https://raw.githubusercontent.com/ywelsch/duckdb-psql/main/example/invoices.csv' |>
where invoice_date >= date '1970-01-16' |>
select
  *, 
  0.8 as transaction_fees,
  total - transaction_fees as income |>
where income > 1 |>
select
  customer_id, 
  avg(total), 
  sum(income) as sum_income, 
  count() as ct
  group by customer_id |>
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,
  version() as db_version;

I copied this example straight from the docs, but I just love how readable it is. None of that nested SQL stuff, just a nice flow from top to bottom.