Adding pg_duckdb to PostgreSQL on NixOS

pg_duckdb lets you run DuckDB queries directly from PostgreSQL. Here's how to package and enable it on NixOS. This used the latest commit on the master branch as of 2026-02-18.

The httpfs problem

pg_duckdb's build system uses CMake FetchContent to git-clone the httpfs extension at build time. This fails in the Nix sandbox. The fix is to pre-fetch the httpfs source and patch the build to use a local path instead.

Package definition

Create a patch file pg_duckdb-httpfs-local.patch that replaces the GIT_URL/GIT_TAG with a SOURCE_DIR placeholder:

--- a/third_party/pg_duckdb_extensions.cmake
+++ b/third_party/pg_duckdb_extensions.cmake
@@ -1,6 +1,4 @@
 duckdb_extension_load(json)
 duckdb_extension_load(icu)
-duckdb_extension_load(httpfs
-    GIT_URL https://github.com/duckdb/duckdb-httpfs
-    GIT_TAG 9c7d34977b10346d0b4cbbde5df807d1dab0b2bf
+duckdb_extension_load(httpfs SOURCE_DIR @httpfs@
 )

Then in pg_duckdb.nix, fetch httpfs separately and substitute the placeholder with its store path:

{
  postgresql,
  postgresqlBuildExtension,
  fetchFromGitHub,
  cmake,
  ninja,
  openssl,
  curl,
}: let
  duckdb-httpfs = fetchFromGitHub {
    owner = "duckdb";
    repo = "duckdb-httpfs";
    rev = "9c7d34977b10346d0b4cbbde5df807d1dab0b2bf";
    hash = "sha256-dIf3JazCE37y5KPaqDplnD39JmO5tTKeso0KCp1+V2w=";
  };
in
  postgresqlBuildExtension (finalAttrs: {
    name = "pg_duckdb";
    nativeBuildInputs = [cmake ninja];
    buildInputs = [openssl curl];
    patches = [./pg_duckdb-httpfs-local.patch];
    postPatch = ''
      substituteInPlace third_party/pg_duckdb_extensions.cmake \
        --subst-var-by httpfs ${duckdb-httpfs}
    '';
    dontConfigure = true;
    dontBuild = true;
    dontCheck = true;
    installPhase = ''
      make install DESTDIR=$PWD
      mkdir $out
      mv $PWD${postgresql}/* $out
    '';
    src = fetchFromGitHub {
      owner = "duckdb";
      repo = "pg_duckdb";
      rev = "dafe12d88830fe2e5ee07cfcc3ca810ba63cbbd4";
      hash = "sha256-aH0+o3aUKzN8uXhgDapVCrUGdHNrsvfSSKGFbi66oZI=";
      fetchSubmodules = true;
      leaveDotGit = true;
    };
  })

fetchSubmodules and leaveDotGit are both required. The build system pulls in DuckDB as a submodule and its Makefile relies on .git to check that submodules have been downloaded.

The standard Nix build phases are disabled because pg_duckdb's Makefile handles everything in a single make install invocation — configuring, building, and installing in one step. The httpfs extension needs openssl and curl as build inputs.

The installPhase deserves some explanation: pg_duckdb's make install installs files into the PostgreSQL prefix (e.g. /nix/store/...-postgresql-18/), which doesn't work for Nix extensions that need their own output. Using DESTDIR=$PWD redirects the install into the build directory, then we move the files from the PostgreSQL prefix subtree into $out.

NixOS configuration

Enable the extension in your PostgreSQL service configuration:

services.postgresql = {
  package = pkgs.postgresql_18;
  extensions = ps: [(ps.callPackage ./pg_duckdb.nix {})];
};

Although this post was generated using Claude Opus 4.6 (an LLM), the information contained it in it was derived from an actual use case, and is verified to work.