You may know this already but the postgres extension[1] may help:
If I understand it correctly, when you use it it:
- Pulls the minimal data required (inferred from the query) from postgres into duckdb
- Executes your query using duckdb execution engine
BUT, if your postgres function is not supported by DuckDB I think you can use the `postgres_execute` [2] to execute the function within postgres itself
I'm not sure whether you can e.g do a CTE pipeline that starts with postgres_execute, and then executes Duckdb sql in later stages of the pipeline
Thanks for the suggestion! As I understand, you can only postgres_execute against a running Postgres db. It does work and I’ve used it in my tests, I think I could get around the limitations that I ran into by running a pg instance alongside DuckDB.
For now I think I’ll stick with just pg, as I was looking into DuckDB to replace pg in my local analytic workloads: load data from rest apis, dump into a database and use sql in a custom dbt-like pipeline to build the tables for analysis in bi tools. Unfortunately, many endpoints return xml data and much of the sql I’ve already written deals with json, meaning it would have to be adapted to work with DuckDB.
If I understand it correctly, when you use it it:
- Pulls the minimal data required (inferred from the query) from postgres into duckdb
- Executes your query using duckdb execution engine
BUT, if your postgres function is not supported by DuckDB I think you can use the `postgres_execute` [2] to execute the function within postgres itself
I'm not sure whether you can e.g do a CTE pipeline that starts with postgres_execute, and then executes Duckdb sql in later stages of the pipeline
[1] https://duckdb.org/docs/extensions/postgres.html#running-sql... [2]https://duckdb.org/docs/extensions/postgres.html#the-postgre...