Will, in time, the DuckDB SQL syntax be available on PostgreSQL with pg_duckdb?

[acirtep]

Hi all,

First of all nice work and good luck further!

I have just installed pg_duckdb on PostgreSQL 16.4 and while some specific duckdb functions work when the execution is enabled, some syntax is not recognized, such as pivot, qualify, range etc. Will these features of DuckDB be added to the extension in the future and made available on PostgreSQL?

Thank you!

[wuputah]

Thanks for opening this as a discussion! 🎉

As far as I know, we have to maintain Postgres-compatible syntax, as we cannot modify or add new rules to the Postgres parser without forking Postgres. So we have to work within the restrictions of what is allowed by Postgres. As such, directly supporting syntax that DuckDB adds is not possible.

While we would like to avoid this whenever possible, the easiest way to allow DuckDB-specific syntax would be through something like a duckdb.query function. The only caveat would be that you would need to tell Postgres the column names and types. Using the example in the PIVOT docs, it would looks something like this:

SELECT * FROM duckdb.query('
  PIVOT Cities
  ON Year
  USING sum(Population)
') AS (country text, name text, "2000" integer, "2010" integer, "2020" integer);

The tricky thing with PIVOT is it is "generating" columns so knowing the columns ahead of time would require running an unpivoted query first (in this case SELECT DISTINCT year FROM cities). I'm not aware of a way around this, but maybe wiser minds than me can come up with a better solution.

This doesn't necessarily apply to every feature of DuckDB - we would like as many features as possible to integrate natively - but ultimately it does need to be a query that Postgres can parse.