Search code examples
rcommon-table-expressionduckdb

duckdb syntax error for materialized CTEs

I am trying to create a query with Materialized CTEs but I get a syntax error (in this case with R, but the problem is independent of R, I hope...).

The query that throws an error looks like this (very close to the example given in the documentation)

WITH avg_cte AS MATERIALIZED (
  SELECT carrier, MEAN(dep_delay) AS avg_dep_delay
  FROM flights
  GROUP BY carrier
)
SELECT * FROM avg_cte

A MWE with R would look like this

cc <- DBI::dbConnect(duckdb::duckdb())
DBI::dbWriteTable(cc, "flights", nycflights13::flights)

q <- "WITH avg_cte AS MATERIALIZED (
  SELECT carrier, MEAN(dep_delay) AS avg_dep_delay
  FROM flights
  GROUP BY carrier
)
SELECT * FROM avg_cte
"
DBI::dbGetQuery(cc, q)
#> Error: Parser Error: syntax error at or near "MATERIALIZED"
#> LINE 1: WITH avg_cte AS MATERIALIZED (
Solution

  • The MATERIALIZE keyword is new in 0.9.0, however the current R package is still only 0.8.1-3. The "bleeding edge" installation instructions should allow 0.9.0 to be installed in the meantime.

    install.packages('duckdb', repos=c('https://duckdb.r-universe.dev', 'https://cloud.r-project.org'))