Which Postgres Pro role should be allowed to use DuckDB execution and use the secrets. Defaults to superusers only. If this is configured, but the role does not exist when running CREATE EXTENSION pgpro_duckdb, it will be created automatically. This role will have access to DuckDB secrets.

Default: ""

Access: Needs to be in the postgresql.conf file and requires a restart

Disable specific file systems preventing access. This setting only applies to non-superusers. Superusers can always access all file systems. Unless you completely trust the user in duckdb.postgres_role, it is recommended to disable LocalFileSystem. Otherwise they can trivially read and write any file on the machine that the Postgres Pro process can.

Default: "LocalFileSystem"

Access: Superuser-only

Whether known extensions are allowed to be automatically installed when a DuckDB query depends on them.

Default: true

Access: Superuser-only

Whether known extensions are allowed to be automatically loaded when a DuckDB query depends on them.

Default: true

Disable installing community extensions.

Default: false

Access: Superuser-only

Allow the DuckDB to access external storages (e.g., HTTP, S3, etc.). This setting is not tested very well yet and disabling it may break unintended pgpro_duckdb functionality.

Default: true

Access: Superuser-only

The maximum memory DuckDB can use within a single Postgres Pro connection. This is somewhat comparable to Postgres Pro work_mem setting. When set to an empty string, this will use DuckDB normal default, which is 80% of RAM.

Default: "4GB"

Access: Superuser-only

Maximum number of DuckDB threads per Postgres Pro connection. -1 means to use DuckDB its default, which is the number of CPU cores on the machine.

Default: -1

Access: Superuser-only

Maximum number of PostgreSQL workers used for a single Postgres Pro scan. This is similar to Postgres Pro max_parallel_workers_per_gather setting.

Default: 2

Access: General

Allow DuckDB to load extensions with invalid or missing signatures. Mostly useful for development of DuckDB extensions.

Default: false

Access: Superuser-only

Reads a Parquet file, either from a remote storage (via httpfs) or a local storage.

This returns DuckDB rows, you can expand them using * or you can select specific columns using the r['mycol'] syntax. If you want to select specific columns, you should give the function call an easy alias, like r. For example:

SELECT * FROM read_parquet('file.parquet');
SELECT r['id'], r['name'] FROM read_parquet('file.parquet') r WHERE r['age'] > 21;
SELECT COUNT(*) FROM read_parquet('file.parquet');

Further information:

The following arguments are required:

Optional parameters mirror DuckDB read_parquet function. To specify optional parameters, use parameter := 'value'.

Reads a CSV file, either from a remote storage (via httpfs) or a local storage.

This returns DuckDB rows, you can expand them using * or you can select specific columns using the r['mycol'] syntax. If you want to select specific columns, you should give the function call an easy alias, like r. For example:

SELECT * FROM read_csv('file.csv');
SELECT r['id'], r['name'] FROM read_csv('file.csv') r WHERE r['age'] > 21;
SELECT COUNT(*) FROM read_csv('file.csv');

Further information:

The following arguments are required:

Optional parameters mirror DuckDB read_csv function. To specify optional parameters, use parameter := 'value'.

Compatibility notes:

Reads a JSON file, either from a remote storage (via httpfs) or a local storage.

This returns DuckDB rows, you can expand them using * or you can select specific columns using the r['mycol'] syntax. If you want to select specific columns, you should give the function call an easy alias, like r. For example:

SELECT * FROM read_parquet('file.parquet');
SELECT r['id'], r['name'] FROM read_parquet('file.parquet') r WHERE r['age'] > 21;
SELECT COUNT(*) FROM read_parquet('file.parquet');

Further information:

The following arguments are required:

Optional parameters mirror DuckDB read_json function. To specify optional parameters, use parameter := 'value'.

Compatibility notes:

Reads an Iceberg table, either from a remote storage (via httpfs) or a local storage.

To use iceberg_scan, you must enable the iceberg extension:

SELECT duckdb.install_extension('iceberg');

This returns DuckDB rows, you can expand them using * or you can select specific columns using the r['mycol'] syntax. If you want to select specific columns, you should give the function call an easy alias, like r. For example:

SELECT * FROM iceberg_scan('data/iceberg/table');
SELECT r['id'], r['name'] FROM iceberg_scan('data/iceberg/table') r WHERE r['age'] > 21;
SELECT COUNT(*) FROM iceberg_scan('data/iceberg/table');

Further information:

The following arguments are required:

Optional parameters mirror DuckDB iceberg_scan function. To specify optional parameters, use parameter := 'value'.

To use iceberg_metadata, you must enable the iceberg extension:

SELECT duckdb.install_extension('iceberg');

Returns metadata about an Iceberg table. Data is returned as a set of icerberg_metadata_record, which is defined as follows:

CREATE TYPE duckdb.iceberg_metadata_record AS (
  manifest_path TEXT,
  manifest_sequence_number NUMERIC,
  manifest_content TEXT,
  status TEXT,
  content TEXT,
  file_path TEXT
);

Further information:

The following arguments are required:

Optional parameters mirror DuckDB iceberg_metadata function. To specify optional parameters, use parameter := 'value'.

Reads a Delta dataset, either from a remote (via httpfs) or a local storage.

To use delta_scan, you must enable the delta extension:

SELECT duckdb.install_extension('delta');

This returns DuckDB rows, you can expand them using * or you can select specific columns using the r['mycol'] syntax. If you want to select specific columns, you should give the function call an easy alias, like r. For example:

SELECT * FROM delta_scan('/path/to/delta/dataset');
SELECT r['id'], r['name'] FROM delta_scan('/path/to/delta/dataset') r WHERE r['age'] > 21;
SELECT COUNT(*) FROM delta_scan('/path/to/delta/dataset');

Further information:

The following arguments are required:

Installs a DuckDB extension and configures it to be loaded automatically in every session that uses pgpro_duckdb.

SELECT duckdb.install_extension('iceberg');
SELECT duckdb.install_extension('avro', 'community');

Since this function can be used to install and download any extensions, it can only be executed by a superuser by default. To allow execution by some other admin user, such as my_admin, you can grant such a user the following permissions:

GRANT ALL ON FUNCTION duckdb.install_extension(TEXT, TEXT) TO my_admin;
GRANT ALL ON TABLE duckdb.extensions TO my_admin;
GRANT ALL ON SEQUENCE duckdb.extensions_table_seq TO my_admin;

The following arguments are required:

Executes the given SELECT query directly against DuckDB. This can be useful if DuckDB syntax makes the query easier to write or if you want to use a function that is not exposed by pgpro_duckdb yet. For example, the below query shows a query that puts FROM before SELECT and uses a list comprehension. Both of those features are not supported in Postgres Pro.

SELECT * FROM duckdb.query('FROM range(10) as a(a) SELECT [a for i in generate_series(0, a)] as arr');

Runs an arbitrary query directly against DuckDB. Compared to duckdb.query, this function can execute any query, not just SELECT queries. The main downside is that it does not return its result as rows, but instead sends the query result to the logs. So the recommendation is to use duckdb.query when possible, but if you need to run e.g. some DDL you can use this function.

pgpro_duckdb keeps the DuckDB instance open inbetween transactions. This is done to save session level state, such as manually done SET commands. If you want to clear this session level state for some reason you can close the currently open DuckDB instance using the following query:

CALL duckdb.recycle_ddb();