DuckDB FDW Guide

Overview

This guide provides a set of helper functions for managing DuckDB FDW in PostgreSQL, specifically for interacting with MotherDuck. These functions streamline tasks such as creating a server, importing tables, and managing foreign data.

This function can be access by going to:

SQL editor -> Geobase Templates -> DuckDB FDW Scripts

1️⃣ Creating a DuckDB FDW Server

Function: duckdb_create_server

Creates a DuckDB FDW server dynamically using an authentication token.

Usage

SELECT duckdb_create_server('motherduck_server', 'your_motherduck_token');

2️⃣ Importing or Dropping a Foreign Table

Function: duckdb_manage_foreign_table

Allows importing or dropping a specific foreign table instead of the entire schema.

Usage

✅ Import a table

SELECT duckdb_manage_foreign_table('add', 'motherduck_server', 'hex350_grid_cardio_1920');

✅ Drop a table

SELECT duckdb_manage_foreign_table('delete', 'motherduck_server', 'hex350_grid_cardio_1920');

3️⃣ Listing Foreign Tables

Function: duckdb_list_foreign_tables

Lists all foreign tables available in PostgreSQL.

Usage

SELECT * FROM duckdb_list_foreign_tables();

4️⃣ Listing Columns of a Foreign Table

Function: duckdb_list_foreign_table_columns

Retrieves the column names and data types of a foreign table.

Usage

SELECT * FROM duckdb_list_foreign_table_columns('hex350_grid_cardio_1920');

5️⃣ Dropping All Foreign Tables

Function: duckdb_drop_all_foreign_tables

Drops all foreign tables dynamically from the database.

Usage

SELECT duckdb_drop_all_foreign_tables();

6️⃣ Dropping a DuckDB FDW Server

Function: duckdb_drop_server

Deletes a DuckDB FDW server and its dependencies.

Usage

SELECT duckdb_drop_server('motherduck_server');

7️⃣ Listing All Foreign Servers

Function: duckdb_list_servers

Retrieves all foreign servers configured in PostgreSQL.

Usage

SELECT * FROM duckdb_list_servers();

8️⃣ Refreshing a Foreign Table Schema

Function: duckdb_refresh_foreign_table

Refreshes the imported schema of a foreign table. Note it works by dropping and recreating the foreign table.

Usage

SELECT duckdb_refresh_foreign_table('motherduck_server', 'hex350_grid_cardio_1920', 'public');

9️⃣ Updating Server Authentication Token

Function: duckdb_change_server_options

Updates the authentication token or other server options.

Usage

SELECT duckdb_change_server_options('motherduck_server', 'new_motherduck_token');

🔟 Checking Server Connectivity

Function: duckdb_check_connection

Verifies whether a foreign server is reachable.

Usage

SELECT duckdb_check_connection('motherduck_server');

✅ Summary of Functions