---
warning: DO NOT CHANGE THIS MANUALLY, THIS IS GENERATED BY https://github.com/duckdb/community-extensions repository, check README there
title: pql
excerpt: |
  DuckDB Community Extensions
  Predictive queries as SQL. TRAIN a model on the tables you already have, PREDICT with it, BACKTEST it, and OPTIMIZE a plan from its predictions.

extension:
  name: pql
  description: Predictive queries as SQL. TRAIN a model on the tables you already have, PREDICT with it, BACKTEST it, and OPTIMIZE a plan from its predictions.
  version: 0.3.0
  language: C++
  build: cmake
  license: MIT
  maintainers:
    - waelbenamara

repo:
  github: Guepard-Corp/duckdb-pql
  variegata: 021b33aebe70bb427f6fbc3d8bd367bd25cfe9f5
  ref: 246e4d08436ae750fbfea1f0bf709df6ff9d5f70

docs:
  hello_world: |
    -- 1,200 customers, their orders, and a question about the future.
    CREATE TABLE customers AS
      SELECT i AS id, 1 + i % 4 AS tier,
             DATE '2025-01-01' + INTERVAL (i % 300) DAY AS last_seen
      FROM range(1200) t(i);
    CREATE TABLE orders AS
      SELECT c.id AS customer_id,
             c.last_seen - INTERVAL (7 * k + (c.id * 7 + k * 13) % 5) DAY AS ts,
             10 + (c.id * 31 + k * 17) % 90 AS amount
      FROM customers c, range(1, 9) t(k)
      WHERE k <= c.tier + c.id % 3;
    INSERT INTO orders
      SELECT id, last_seen + INTERVAL (1 + id * 13 % 28) DAY, 10 + id * 3 % 90
      FROM customers WHERE tier * 20 + id * 37 % 60 > 70;

    -- Will each customer order again within 30 days of when we last saw them?
    TRAIN MODEL reorder PREDICT EXISTS(orders) FOR customers AT last_seen HORIZON 30 DAYS;

    -- One row per customer, with a probability.
    PREDICT EXISTS(orders) FOR customers WHERE tier >= 3 USING MODEL reorder;
  extended_description: |
    PQL adds predictive statements to SQL. A model is trained directly on the
    tables in the catalog: PQL follows the foreign keys, builds features from the
    related tables (counts, averages, recency, spacing), holds out a slice by
    time, and reports how good the model is before you use it. No feature
    engineering, no export, no separate service.

    ### The statements

    ```sql
    TRAIN [OR REPLACE] MODEL <name>
      PREDICT <target>                        -- a column, or COUNT / EXISTS / SUM / AVG / MIN / MAX over a related table
      FOR <table> [AS <alias>]
      [ WHERE <filter> ]                      -- which entities take part
      [ AT <column> | EVERY <n> <unit> ]      -- the anchor time of each example
      [ HORIZON <n> <unit> ]                  -- how far ahead the target is counted
      [ USING GRAPH (<table>, ...) ]          -- restrict the tables used
      [ EXCLUDE (<column>, ...) ]             -- columns the model must not see
      [ SPLIT TEMPORAL VALIDATE FROM <ts> TEST FROM <ts> ]
      [ OPTIONS (<key> = <value>, ...) ];

    PREDICT <target> FOR <table> [ WHERE <filter> ] USING MODEL <name>;
    BACKTEST MODEL <name> [ FROM <ts> ] [ TO <ts> ];   -- replay it row by row against what happened
    EXPLAIN MODEL <name>;                                -- which features it relies on
    DROP MODEL [IF EXISTS] <name>;

    [EXPLAIN] OPTIMIZE <table> [ WHERE <filter> ]       -- a plan from the predictions (three forms, below)
      [ CHOOSE <column> IN (<value>, ...) | ASSIGN <column> OVER <table>.<key> [ ... ] ]
      MAXIMIZE | MINIMIZE <total> [ SUBJECT TO <limit> [ AND ... ] ]
      USING MODEL <name>;
    ```

    Every statement is also a table function, `pql_exec('<statement>')`, so a
    prediction joins to anything. `pql_models()` lists the models in the session.

    ### What TRAIN returns

    The hello-world above prints one row like this:

    ```text
    model    target          train_rows val_rows test_rows metric  val    test   pr_auc  baseline features
    reorder  EXISTS(orders)  710        237      237       auroc   0.871  0.815  0.891   0.608    26
    ```

    - `test` is the number to trust: scored once, on a later slice the model never
      saw during selection.
    - `baseline` is what you get with no model at all: the positive rate for a
      yes/no target, "the same as last window" for a quantity. If `test` does not
      beat it, PQL has just told you so.
    - `pr_auc` is average precision, the honest score when positives are rare.
    - `val` picked the epoch and is optimistic by construction. Do not quote it.

    ### Two kinds of question

    **A column that already exists** is imputation and needs no dates:

    ```sql
    TRAIN MODEL vip PREDICT customers.is_vip FOR customers;
    ```

    **Something in the future** is a forecast. `AT` says where "now" is for each
    row, `HORIZON` how far to look, and the target is any aggregate over a related
    table, optionally narrowed:

    ```sql
    TRAIN MODEL spend   PREDICT SUM(orders.amount) FOR customers AT last_seen HORIZON 60 DAYS;
    TRAIN MODEL refunds PREDICT EXISTS(orders WHERE orders.status = 'refunded')
      FOR customers AT last_seen HORIZON 60 DAYS;
    ```

    For forecasts the engine computes the label itself from the window after the
    anchor and builds features only from before it, so the answer cannot leak
    into the inputs. Rows without a natural timestamp can use `EVERY 1 MONTH`
    to generate a grid of anchors from the data's own span.

    ### See why, and check it row by row

    ```sql
    EXPLAIN MODEL reorder;
    ```
    ```text
    feature              slots importance
    tier                 1     0.381
    orders: count 365d   1     0.018
    orders: count 30d    1     0.010
    ```

    Each feature group is shuffled in turn and `importance` is what the model
    loses. If something you did not expect sits at the top, that is usually a
    leak; `EXCLUDE (that_column)` removes it before anything is fitted.

    ```sql
    SELECT count(*) AS n,
           round(avg(abs(error)), 2)             AS model_mae,
           round(avg(abs(baseline - actual)), 2) AS persistence_mae
    FROM pql_exec('BACKTEST MODEL spend FROM ''2025-09-15''');
    ```

    ### From a prediction to a plan: OPTIMIZE

    `OPTIMIZE` is one statement. It turns a model's predictions into a plan that
    maximises or minimises a total under limits, in one of three forms:

    | form | decides | limits | rows interact |
    |---|---|---|---|
    | `OPTIMIZE t ... SUBJECT TO ...` | which rows to act on | totals over the plan | only through the totals |
    | `OPTIMIZE t CHOOSE col IN (...)` | one value per row, from a list | totals over the plan | only through the totals |
    | `OPTIMIZE t ASSIGN col OVER r.key` | which row of another table each row goes to, or none | per resource (`PER col`), and with `SEQUENCE` order, setups and time windows | through the resource they share |

    ```sql
    -- Which rows: fifty calls, to the customers worth most who would not reorder on their own.
    OPTIMIZE customers
      MAXIMIZE SUM((1 - prediction) * tier * 100)
      SUBJECT TO COUNT(*) <= 50
      USING MODEL reorder;

    -- A value per row: the offer for each customer, under a budget.
    OPTIMIZE customers
      CHOOSE offer IN ('none', '10%', '20%')
      MAXIMIZE SUM(prediction - CASE offer WHEN '10%' THEN 10 WHEN '20%' THEN 20 ELSE 0 END)
      SUBJECT TO SUM(CASE offer WHEN '10%' THEN 10 WHEN '20%' THEN 20 ELSE 0 END) <= 5000
      USING MODEL spend;

    -- A resource per row, in sequence: jobs on crews, with travel, time windows and each crew's hours.
    OPTIMIZE jobs WHERE open = 1 AT received_at
      ASSIGN crew OVER crews.crew ELSE 'none'
        ONLY IF resource.kind = kind
        SEQUENCE FROM resource.free_from
          START    DISTANCE(resource.lat, resource.lon, from_lat, from_lon) / 40
          SETUP    DISTANCE(prev.to_lat, prev.to_lon, from_lat, from_lon) / 40
          DURATION miles / 40 + 1
          WINDOW BETWEEN window_from AND window_to
      MAXIMIZE SUM(prediction - plan.setup * 60)
      SUBJECT TO SUM(plan.setup + plan.duration) PER crew <= resource.hours
      USING MODEL job_margin;
    ```

    With `CHOOSE`, one row's choice never changes what another row is worth. With
    `ASSIGN` it does: a job uses its crew's hours, and its travel depends on the job
    before it. So `ASSIGN` has its own solver and plan columns (`sequence`, `begin`,
    `setup`, `duration`). In every form, `EXPLAIN OPTIMIZE` reports the plan's total
    beside a bound no plan can beat; when the two are equal, the plan is proven best.

    ### It is still SQL

    ```sql
    SELECT c.tier, round(avg(p.prediction), 3) AS chance_to_reorder
    FROM pql_exec('PREDICT EXISTS(orders) FOR customers USING MODEL reorder') p
    JOIN customers c ON c.id = p.id
    GROUP BY c.tier ORDER BY 1;
    ```

    ### Good to know

    - Needs DuckDB v1.5.5 or later; built against the current release and tested
      against the next one on every change.
    - Models live for the session and are not saved in the database file.
      Retraining is seconds, so `TRAIN` in the same job you `PREDICT` in.
    - Predicting a column that already exists has no leakage guarantee: every
      other column on the row is a feature, including ones that are consequences
      of the target. A near-perfect score there usually means a tautology.
      Forecast targets are the path with the structural guarantee.
    - Filters compare a column to a literal (`=`, `<`, `IN`, `IS NULL`, `AND`,
      `OR`). Tables are named without a schema. Text targets are refused rather
      than trained as multi-class.
    - `SHOW MODELS` is not available on its own (DuckDB parses it first); use
      `pql_models()` or `pql_exec('SHOW MODELS')`.
    - On RelBench, the standard benchmark for prediction over relational
      databases, PQL scores in the same class as the published graph neural
      network and as gradient boosting over hand-built features, from a single
      statement on a laptop CPU.

    ### Learn more

    - [Tutorial](https://github.com/Guepard-Corp/duckdb-pql/blob/main/docs/tutorial.md):
      a 15-minute walk from first model to backtest, with real output.
    - [Reference](https://github.com/Guepard-Corp/duckdb-pql/blob/main/docs/reference.md):
      full syntax, options, result columns, and the current limitations.
    - [How it works](https://github.com/Guepard-Corp/duckdb-pql/blob/main/docs/how-it-works.md):
      features, leakage, the two model architectures, and the OPTIMIZE solvers.
    - Issues and questions: https://github.com/Guepard-Corp/duckdb-pql/issues

extension_star_count: 35
extension_star_count_pretty: 35
extension_download_count: 927
extension_download_count_pretty: 927
image: '/images/community_extensions/social_preview/preview_community_extension_pql.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                                        |
|---------------|---------------|----------------------------------------------------------------------------------------|---------|---------------------------------------------------------------------------------------|
| pql_exec      | table         | Run a PQL statement and return its result as a table.                                  | NULL    | [SELECT * FROM pql_exec('PREDICT customers.churned FOR customers USING MODEL churn')] |
| pql_models    | table         | List the PQL models trained in this session, with the statement that defined each one. | NULL    | [SELECT * FROM pql_models()]                                                          |

### 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 |
|----------------|---------------------------------------------------------|------------|--------|---------|-------------|
| temp_directory | Set the directory to which temporary files are written. | VARCHAR    | GLOBAL | []      | post.db.tmp |