DuckDB supports prepared statements where parameters are substituted when the query is executed. This can improve readability and is useful for preventing SQL injections.

Syntax

There are three syntaxes for denoting parameters in prepared statements: auto-incremented (?), positional ($1), and named ($param). Note that not all clients support all of these syntaxes, e.g., the JDBC client only supports auto-incremented parameters in prepared statements.

Example Dataset

In the following, we introduce the three different syntaxes and illustrate them with examples using the following table.

CREATE TABLE person (name VARCHAR, age BIGINT);
INSERT INTO person VALUES ('Alice', 37), ('Ana', 35), ('Bob', 41), ('Bea', 25);

In our example query, we'll look for people whose name starts with a B and are at least 40 years old. This will return a single row <'Bob', 41>.

Auto-Incremented Parameters: ?

DuckDB supports using prepared statements with auto-incremented indexing, i.e., the position of the parameters in the query corresponds to their position in the execution statement. For example:

PREPARE query_person AS
    SELECT *
    FROM person
    WHERE starts_with(name, ?)
      AND age >= ?;

Using the CLI client, the statement is executed as follows.

EXECUTE query_person('B', 40);

Positional Parameters: $1

Prepared statements can use positional parameters, where parameters are denoted with an integer ($1, $2). For example:

PREPARE query_person AS
    SELECT *
    FROM person
    WHERE starts_with(name, $2)
      AND age >= $1;

Using the CLI client, the statement is executed as follows. Note that the first parameter corresponds to $1, the second to $2, and so on.

EXECUTE query_person(40, 'B');

Named Parameters: $parameter

DuckDB also supports named parameters where parameters are denoted with $parameter_name. For example:

PREPARE query_person AS
    SELECT *
    FROM person
    WHERE starts_with(name, $name_start_letter)
      AND age >= $minimum_age;

Using the CLI client, the statement is executed as follows.

EXECUTE query_person(name_start_letter := 'B', minimum_age := 40);

Session Variables as Default Values

New Using session variables as default values for named parameters is introduced in DuckDB 2.0.

When a named parameter is not supplied at execution time, DuckDB fills it from the session variable of the same name, if one is set. An explicit argument always takes precedence over the variable.

SET VARIABLE multiplier = 10;
SET VARIABLE x = 42;
PREPARE compute AS SELECT $multiplier * $x AS result;

With no arguments, both parameters come from the variables, computing 10 * 42:

EXECUTE compute;

Supplying an argument overrides the matching variable, while omitted parameters still fall back to theirs:

EXECUTE compute(multiplier := 3);

Because the fallback is resolved at execution time, re-executing the statement after changing a variable picks up the new value:

SET VARIABLE multiplier = 100;
EXECUTE compute;

The fallback applies to named parameters only. Auto-incremented (?) and positional ($1) parameters are matched by position and are never filled from a session variable, even one whose name is a number such as 1.

Dropping Prepared Statements: DEALLOCATE

To drop a prepared statement, use the DEALLOCATE statement:

DEALLOCATE query_person;

Alternatively, use:

DEALLOCATE PREPARE query_person;