---
warning: DO NOT CHANGE THIS MANUALLY, THIS IS GENERATED BY https://github.com/duckdb/community-extensions repository, check README there
title: plinking_duck
excerpt: |
  DuckDB Community Extensions
  Read and write PLINK 2 genomics file formats and run common genetic analyses directly in SQL

extension:
  name: plinking_duck
  description: Read and write PLINK 2 genomics file formats and run common genetic analyses directly in SQL
  version: 0.14.2
  language: C++
  build: cmake
  license: MIT
  excluded_platforms: "windows_amd64;windows_amd64_mingw;windows_amd64_rtools;wasm_mvp;wasm_eh;wasm_threads;linux_amd64_musl"
  maintainers:
    - teaguesterling

repo:
  github: teaguesterling/plinking_duck
  # Post-#3079 keys: ref = the default build (the DuckDB 2.0 line, v2.0.0 once tagged);
  # variegata = the DuckDB v1.5.6 build (explicit, not left to fall back to ref);
  # ref_next = the dormant prerelease slot (build_next test_against_latest is if:false).
  # All three are the one v0.14.2 commit: a single source tree builds green on BOTH
  # lines. v0.14.2 is the DuckDB v2.0-cyanoptera compatibility release (and renames the howard catalog to pfile_catalog) — the pinned
  # cyanoptera CI leg built clean (linux amd64/arm64, osx amd64/arm64) and the full
  # unittest suite passes on both cyanoptera and the v1.5.6 ship line; the named-
  # parameter rework goes through named_parameter_compat.hpp (GetSignature().
  # WithTypedKwargs() on v2.0, the flat map on v1.5), same mechanism as the fleet.
  ref: 7a34f3ab8d76534aaebed0aaa88e8ef27099f4e6
  variegata: 7a34f3ab8d76534aaebed0aaa88e8ef27099f4e6
  ref_next: 7a34f3ab8d76534aaebed0aaa88e8ef27099f4e6

docs:
  hello_world: |
    -- Read variant metadata from a .pvar file
    SELECT CHROM, POS, ID, REF, ALT
    FROM read_pvar('cohort.pvar')
    WHERE CHROM = '22';

    -- Read genotypes in tidy format (one row per variant x sample)
    SELECT chrom, pos, iid, genotype
    FROM read_pfile('cohort', orient := 'genotype')
    LIMIT 10;

    -- Compute allele frequencies
    SELECT * FROM plink_freq('cohort.pgen')
    WHERE ALT_FREQ > 0.01;

    -- Run a GWAS association test
    SELECT * FROM plink_glm('cohort')
    WHERE p_value < 5e-8;

  extended_description: |
    PlinkingDuck brings [PLINK 2](https://www.cog-genomics.org/plink/2.0/) genotype,
    variant, and sample data into DuckDB, letting you query genomics datasets with
    standard SQL instead of format-specific command-line tools.

    **File readers:**
    - `read_pvar(path | [paths])` — variant metadata (.pvar/.bim); a list row-concatenates multiple files
    - `read_psam(path)` — sample metadata (.psam/.fam)
    - `read_pgen(path)` — binary genotypes (.pgen)
    - `read_pfile(prefix | [prefixes])` — unified fileset reader with orient modes (variant/genotype/sample), sample subsetting, region and variant filtering; accepts a list of prefixes to read a variant-sharded fileset (identical samples) as one table
    - `read_plink_vcf(path)` — fast biallelic genotype extraction from VCF/VCF.gz (~3x faster than htslib)

    **Writing & catalogs:**
    - `COPY (…) TO 'out' (FORMAT pvar | psam | pgen | pfile)` — write PLINK 2 filesets from SQL, including a `shard_by '<col>'` mode that splits a cohort into one fileset per key (e.g. per chromosome)
    - `.howard` catalog — a thin JSON catalog over a set of filesets (a shared `.pvar`/`.psam` plus per-file partition and genomic-range annotations); the sharded writer emits one as its manifest
    - `read_howard_manifest(path)` — parse, verify, and region-prune a `.howard` catalog, returning one row per surviving shard (with resolved companion paths, text or parquet)

    **Analysis functions:**
    - `plink_freq` — per-variant allele frequencies via fast genotype counting
    - `plink_hardy` — Hardy-Weinberg equilibrium exact test
    - `plink_missing` — per-variant or per-sample missingness rates
    - `plink_ld` — pairwise linkage disequilibrium (r², D, D')
    - `plink_score` — polygenic risk scoring with mean imputation
    - `plink_glm` — per-variant GWAS regression (linear, logistic, Firth)
    - `plink_pca` — principal component analysis via randomized SVD

    **Genotype output modes:**
    - `genotypes='struct'` — STRUCT with named fields per sample
    - `genotypes='counts'` — fast genotype counting (no decompression)
    - `genotypes='stats'` — counts + AF, MAF, missingness, heterozygosity

    **Flexible inputs:**
    - Unified `variants` parameter: indices, rsids, CPRA strings/structs, ranges
    - Parquet/CSV/table companions for variant and sample metadata
    - `af_range` / `ac_range` filter pushdown, and `include_genotypes` (hardcall
      category) / `genotype_range` for fast carrier lookups — in `orient := 'sample'`
      only the matching subjects are materialized

    **Remote / cloud reads:**
    - Read `.pgen` filesets directly from `s3://`, `https://`, and other DuckDB
      filesystems (companions too) — targeted/region queries fetch only the bytes
      they need via HTTP range reads. `plinking_pgen_io := 'localize'` downloads once
      for remote full scans. Split-index (`.pgen.pgi`) filesets are read transparently.

    All functions support projection pushdown (skip genotype decompression for
    metadata-only queries), parallel scanning, sample subsetting, and region
    filtering. Legacy PLINK 1 formats (.bim/.fam) are auto-detected.

    Built on [pgenlib](https://github.com/chrchang/plink-ng) for efficient access
    to the compressed .pgen binary format without full decompression.

    For full documentation, see [plinking-duck.readthedocs.io](https://plinking-duck.readthedocs.io).

extension_star_count: 6
extension_star_count_pretty: 6
extension_download_count: 16058
extension_download_count_pretty: 16.1k
image: '/images/community_extensions/social_preview/preview_community_extension_plinking_duck.png'
layout: community_extension_doc
---
### Compatibility Matrix

This table covers all DuckDB releases that have not reached [end-of-life](https://duckdb.org/release_calendar), including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.

<div class="extension_compatibility_table"></div>

| Platform | 1.4.4 | 1.4.5 | 1.5.5 | 1.5.6 | 2.0.0 |
| :--- | :---: | :---: | :---: | :---: | :---: |
| Linux AMD64 | ✅ | ❌ | ✅ | ✅ | ✅ |
| Linux ARM64 | ✅ | ❌ | ✅ | ✅ | ✅ |
| macOS AMD64 | ✅ | ❌ | ✅ | ✅ | ✅ |
| macOS ARM64 | ✅ | ❌ | ✅ | ✅ | ✅ |
| Windows AMD64 | ❌ | ❌ | ❌ | ❌ | ❌ |
| Windows ARM64 | ❌ | ❌ | ❌ | ❌ | ❌ |
| WebAssembly | ❌ | ❌ | ❌ | ❌ | ❌ |

### Installing and Loading
```sql
INSTALL {{ page.extension.name }} FROM community;
LOAD {{ page.extension.name }};
```

{% if page.docs.hello_world %}
### Example
```sql
{{ page.docs.hello_world }}```
{% endif %}

{% if page.docs.extended_description %}
### About {{ page.extension.name }}
{{ page.docs.extended_description }}
{% endif %}
### Added Functions

<div class="extension_functions_table"></div>

|       function_name        | function_type |                                                                                                    description                                                                                                    | comment |                                                                   examples                                                                   |
|----------------------------|---------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|---------|----------------------------------------------------------------------------------------------------------------------------------------------|
| pfile_partitions           | table         | Compute the ordinal-range partitions of a pfile along the variants dimension (half-open [lo, hi)).                                                                                                                | NULL    | [SELECT * FROM pfile_partitions('data/cohort')]                                                                                              |
| plink_freq                 | table         | Compute allele frequencies and counts from PLINK files.                                                                                                                                                           | NULL    | [SELECT * FROM plink_freq('data/cohort.pgen')]                                                                                               |
| plink_glm                  | table         | Fit generalized linear models (linear/logistic regression) across variants.                                                                                                                                       | NULL    | [SELECT * FROM plink_glm('data/cohort.pgen', phenotype := 'trait')]                                                                          |
| plink_hardy                | table         | Compute Hardy-Weinberg equilibrium exact test statistics.                                                                                                                                                         | NULL    | [SELECT * FROM plink_hardy('data/cohort.pgen')]                                                                                              |
| plink_ld                   | table         | Compute pairwise linkage disequilibrium metrics.                                                                                                                                                                  | NULL    | [SELECT * FROM plink_ld('data/cohort.pgen', window_kb := 500, r2_threshold := 0.2)]                                                          |
| plink_missing              | table         | Compute missingness rates per variant or per sample.                                                                                                                                                              | NULL    | [SELECT * FROM plink_missing('data/cohort.pgen', mode := 'variant')]                                                                         |
| plink_pca                  | table         | Compute principal components analysis from PLINK files.                                                                                                                                                           | NULL    | [SELECT * FROM plink_pca('data/cohort.pgen', n_pcs := 10)]                                                                                   |
| plink_score                | table         | Compute polygenic risk scores from variant weights.                                                                                                                                                               | NULL    | [SELECT * FROM plink_score('data/cohort.pgen', weights := 'weights.tsv')]                                                                    |
| plinking_duck_capabilities | scalar        | List the capability tokens this plinking_duck build supports (for SQL feature detection).                                                                                                                         | NULL    | [SELECT list_contains(plinking_duck_capabilities(), 'variant_range')]                                                                        |
| read_pfile                 | table         | Read PLINK dataset files into tabular format.                                                                                                                                                                     | NULL    | [SELECT * FROM read_pfile('data/cohort.pgen')]                                                                                               |
| read_pfile_catalog         | table         | Parse, verify, and prune a .pfile.json shard catalog, returning one row per surviving shard. The parse+verify+prune surface plinking_duck owns for pfile_catalog reading (write side = the sharded pfile writer). | NULL    | [SELECT * FROM read_pfile_catalog('cohort.pfile.json'), SELECT prefix FROM read_pfile_catalog('cohort.pfile.json', region := '2:1-5000000')] |
| read_pgen                  | table         | Read PLINK .pgen or .bed genotype files into tabular format.                                                                                                                                                      | NULL    | [SELECT * FROM read_pgen('data/cohort.pgen')]                                                                                                |
| read_plink_vcf             | table         | Read VCF files using PLINK engine into tabular format.                                                                                                                                                            | NULL    | [SELECT * FROM read_plink_vcf('data/cohort.vcf.gz')]                                                                                         |
| read_psam                  | table         | Read PLINK .psam or .fam sample files into tabular format.                                                                                                                                                        | NULL    | [SELECT * FROM read_psam('data/cohort.psam')]                                                                                                |
| read_pvar                  | table         | Read PLINK .pvar or .bim variant files into tabular format.                                                                                                                                                       | NULL    | [SELECT * FROM read_pvar('data/cohort.pvar')]                                                                                                |

### Overloaded Functions

<div class="extension_functions_table"></div>

This extension does not add any function overloads.

### Added Types

<div class="extension_types_table"></div>

This extension does not add any types.

### Added Settings

<div class="extension_settings_table"></div>

|              name               |                                                                                                                                                                description                                                                                                                                                                | input_type | scope  | aliases | typed_value |
|---------------------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|------------|--------|---------|-------------|
| plinking_localize_dir           | Directory (local) for temp copies made by plinking_pgen_io := 'localize'. Empty (default) uses DuckDB's temporary_directory, else the current directory. Created if absent (one level only — parent dirs must already exist). Temps are per-query and removed when the query's bind data is destroyed.                                    | VARCHAR    | GLOBAL | []      |             |
| plinking_max_matrix_elements    | Maximum genotype matrix elements for orient := 'sample' pre-read (variants x samples). Default 16 billion (~16 GB of int8).                                                                                                                                                                                                               | BIGINT     | GLOBAL | []      | 17179869184 |
| plinking_max_threads            | Maximum threads for parallel scan operations. 0 = default (hardcoded cap of 16), >0 = cap at this value.                                                                                                                                                                                                                                  | BIGINT     | GLOBAL | []      | 0           |
| plinking_pgen_io                | How .pgen bytes are read: 'auto' (default — remote/VFS paths via DuckDB's VFS, local via native fopen), 'native' (always native fopen; errors on remote), 'vfs' (always via DuckDB's VFS, even local), 'localize' (materialize a local temp copy then read natively — best for remote full scans; always copies, even a local source).    | VARCHAR    | GLOBAL | []      | auto        |
| plinking_sample_counts_sparse   | orient := 'sample' + genotypes := 'counts'\|'stats': when true, use the sparse (pgen difflist) accumulation path — reads only the non-hom_ref carriers of rare variants (auto-falls-back to the dense full-decode path per variant). When false, always use the dense path. Toggle to A/B time both; both paths produce identical counts. | BOOLEAN    | GLOBAL | []      | false       |
| plinking_use_parquet_companions | Auto-discover .pvar.parquet and .psam.parquet companion files. When true, parquet companions are preferred over text formats.                                                                                                                                                                                                             | BOOLEAN    | GLOBAL | []      | true        |
| temp_directory                  | Set the directory to which temporary files are written.                                                                                                                                                                                                                                                                                   | VARCHAR    | GLOBAL | []      | post.db.tmp |