DuckDB supports the definition of SQL-level variables using the SET VARIABLE and RESET VARIABLE statements.

Variable Scopes

DuckDB supports two levels of variable scopes:

SET VARIABLE

The SET VARIABLE statement assigns a value to a variable, which can be accessed using the getvariable call:

SET VARIABLE my_var = 30;
SELECT 20 + getvariable('my_var') AS total;

New The $⟨variable⟩ shorthand was introduced in DuckDB 2.0.

As a shorthand for getvariable, a variable can be referenced directly with the $⟨variable⟩ syntax anywhere an expression is allowed:

SET VARIABLE my_var = 30;
SELECT 20 + $my_var AS total;

Variable names that are not valid identifiers can be quoted, for example $"my var".

If SET VARIABLE is invoked on an existing variable, it will overwrite its value:

SET VARIABLE my_var = 30;
SET VARIABLE my_var = 100;
SELECT 20 + getvariable('my_var') AS total;

Variables can have different types:

SET VARIABLE my_date = DATE '2018-07-13';
SET VARIABLE my_string = 'Hello world';
SET VARIABLE my_map = MAP {'k1': 10, 'k2': 20};

Variables can also be assigned to results of queries:

-- write some CSV files
COPY (SELECT 42 AS a) TO 'test1.csv';
COPY (SELECT 84 AS a) TO 'test2.csv';

-- add a list of CSV files to a table
CREATE TABLE csv_files (file VARCHAR);
INSERT INTO csv_files VALUES ('test1.csv'), ('test2.csv');

-- initialize a variable with the list of csv files
SET VARIABLE list_of_files = (SELECT list(file) FROM csv_files);

-- read the CSV files
SELECT * FROM read_csv(getvariable('list_of_files'), filename := True);

If a variable is not set, the getvariable function returns NULL:

SELECT getvariable('undefined_var') AS result;

The getvariable function can also be used in a COLUMNS expression:

SET VARIABLE column_to_exclude = 'col1';
CREATE TABLE tbl AS SELECT 12 AS col0, 34 AS col1, 56 AS col2;
SELECT COLUMNS(lambda c: c != getvariable('column_to_exclude')) FROM tbl;

The $name Shorthand

New Referencing a variable with the $name shorthand was introduced in DuckDB 2.0.

As a shorthand for getvariable, you can reference a session variable directly with $name:

SET VARIABLE my_var = 30;
SELECT 20 + $my_var AS total;

For a variable that is set, $name and getvariable('name') return the same value:

SELECT $my_var = getvariable('my_var') AS same;

The two forms differ when the variable is not set: getvariable returns NULL, while a $name reference raises an error.

SELECT $undefined_var;

Invalid Input Error:
Values were not provided for the following parameters: undefined_var

This is because $name is a prepared statement parameter that is filled from the session variable of the same name. This also lets variables act as default values for prepared statements.

Syntax

RESET VARIABLE

The RESET VARIABLE statement unsets a variable.

SET VARIABLE my_var = 30;
RESET VARIABLE my_var;
SELECT getvariable('my_var') AS my_var;

Syntax