DECLARE keyword

DECLARE specifies a series of variable bindings used throughout your query.

This syntax is supported within SELECT queries.

Syntax

Standalone query

DECLARE @variable := expression [, @variable := expression ...]
[WITH ...]
SELECT ...

Inside a view definition (with optional OVERRIDABLE and AUDITED)

DECLARE [OVERRIDABLE] [AUDITED] @variable := expression
    [, [OVERRIDABLE] [AUDITED] @variable := expression ...]
[WITH ...]
SELECT ...

Value list, for the right-hand side of IN

DECLARE @variable := ( value [, value ...] [,] )

The OVERRIDABLE keyword only takes effect inside a view definition. It marks a variable as a parameter that the caller of the view can override at query time. Variables without OVERRIDABLE use the value set in the view and cannot be changed by the caller.

The AUDITED keyword only takes effect inside the definition of an audited view, a QuestDB Enterprise feature. It marks a variable whose resolved value each read of the view records in the audit trail. It is independent of OVERRIDABLE, and the two may appear in either order. Elsewhere it is accepted and has no effect.

A value list declares the set of values an IN filter tests against.

Mechanics

The DECLARE keyword comes before the SELECT clause in your query:

Basic DECLAREDemo this query

DECLARE
    @x := 5
SELECT @x;

Use the variable binding operator := (walrus) to associate expressions to names.

tip

It is easy to accidentally omit the : when writing variable binding expressions.

Don't confuse the := operator with a simple equality =!

You should see an error message like this:

expected variable assignment operator :=

The above example declares a single binding, which states that the variable @x is replaced with the constant integer 5.

The variables are resolved at parse-time, meaning that the variable is no longer present when the query is compiled.

So the above example reduces to this simple query:

basic DECLARE post-reductionDemo this query

SELECT 5;

5
5

Multiple bindings

To declare multiple variables, set the bind expressions with commas ,:

Multiple variable bindingsDemo this query

DECLARE
    @x := 5,
    @y := 2
SELECT @x + @y;

column
7

Variables as functions

A variable need not be just a constant. It could also be a function call, and variables with function values can be nested:

declaring function variableDemo this query

DECLARE
  @today := today(),
  @start := interval_start(@today),
  @end := interval_end(@today)
SELECT @today = interval(@start, @end);

column
true

Declarations in subqueries

Declarations made in parent queries are available in subqueries.

variable shadowingDemo this query

DECLARE
    @x := 5
SELECT y FROM (
    SELECT @x AS y
);

y
5

Shadowing

If a subquery declares a variable of the same name, then the variable is shadowed and takes on the new value.

However, any queries above this subquery are unaffected - the variable bind is not globally mutated.

variable shadowingDemo this query

DECLARE
    @x := 5
SELECT @x + y FROM (
    DECLARE @x := 10
    SELECT @x AS y
);

column
15

Declarations as subqueries

Declarations themselves can be subqueries.

We suggest that this is not overused, as removing the subquery definition from its execution location may make queries harder to debug.

Nevertheless, it is possible to define a variable as a subquery:

table cursor as a variableDemo this query

DECLARE
    @subquery := (SELECT timestamp FROM trades)
SELECT * FROM @subquery;

You can even use already-declared variables to define your subquery variable:

nesting decls inside decl subqueriesDemo this query

DECLARE
    @timestamp := timestamp,
    @symbol := symbol,
    @subquery := (SELECT @timestamp, @symbol FROM trades)
SELECT * FROM @subquery;

Declarations in CTEs

Naturally, DECLARE also works with CTEs:

declarations inside CTEsDemo this query

DECLARE
  @x := 5
WITH first AS (
  DECLARE @x := 10
  SELECT @x as a -- a = 10
),
second AS (
  DECLARE @y := 4
  SELECT
    @x + @y as b, -- b = 5 + 4 = 9
    a -- a = 10
    FROM first
)
SELECT a, b
FROM second;

a	b
10	9

Value lists

A parenthesised, comma-separated list declares the values of an IN filter once, so that a query or a view can name the set instead of spelling it out:

Declare the values of an IN filter

DECLARE @symbols := ('BTC-USDT', 'ETH-USDT')
SELECT timestamp, symbol, price, amount
FROM trades
WHERE symbol IN @symbols AND timestamp IN '$now-1h..$now';

The list is expanded into the IN when the query is parsed, so the query above is exactly symbol IN ('BTC-USDT', 'ETH-USDT'). Each member keeps its own type, and every form of IN works as it does with a written-out list, including NOT IN and interval scans on the designated timestamp.

• IN @symbols and IN (@symbols) are equivalent.
• A list mixes with literals: symbol IN ('SOL-USDT', @symbols).
• One list variable can be assigned to another: @majors := @symbols.
• A list of one needs a trailing comma, ('BTC-USDT',). Without it, ('BTC-USDT') is a parenthesised value. A trailing comma is also accepted after the last member of a longer list.
• A bracketed sub-query, (SELECT ...), is a sub-query and not a list.

Members can be bind variables, which lets one prepared statement filter on a different set of values each time:

A list of bind variables

DECLARE @symbols := ($1, $2)
SELECT timestamp, symbol, price FROM trades WHERE symbol IN @symbols;

In a view, an OVERRIDABLE list can be overridden with a list of a different length:

A list parameter in a view

CREATE VIEW trades_for AS (
  DECLARE OVERRIDABLE @symbols := ('BTC-USDT', 'ETH-USDT')
  SELECT timestamp, symbol, price FROM trades WHERE symbol IN @symbols
);

DECLARE @symbols := ('SOL-USDT',) SELECT * FROM trades_for;

A list has no value of its own, so it can only be used on the right-hand side of IN. These fail:

Query	Error
SELECT @symbols, WHERE symbol = @symbols	declared list can only be used on the right-hand side of IN
OVER (PARTITION BY @symbols)	declared list can only be used on the right-hand side of IN
@all := (@symbols, 'SOL-USDT')	declared list can only be used on the right-hand side of IN
@x := ('BTC-USDT', ('ETH-USDT', 'SOL-USDT'))	nested lists are not supported
@x := ()	value expected in list

To combine a list with more values, write both in the IN: symbol IN (@symbols, 'SOL-USDT').

Bind variables

DECLARE syntax will work with prepared statements over PG Wire, so long as the client library does not perform syntax validation that rejects the DECLARE syntax:

DECLARE @x := ?, @y := ?
SELECT @x::int + @y::int;

-- Then bind the following values: (1, 2)

column
3

This can be useful to minimise repeated bind variables.

For example, rather than passing the same value to multiple positional arguments, you could instead use a declared variable and send a single bind variable:

-- instead of this:
SELECT ? as name, id FROM users WHERE name = ?;

-- do this:
DECLARE @name := ?
SELECT @name as name, id FROM users WHERE name = @name;

Or for repeating columns:

DECLARE
    @col = ?,
    @symbol = ?
SELECT avg(@col), min(@col), max(@col)
FROM trades
WHERE symbol = @symbol;

Limitations

Most basic expressions are supported, and we provide examples later in this document.

We suggest you use variables to simplify repeated constants within your code, and minimise how many places you need to update the constant.

Disallowed expressions

However, not all expressions are supported. The following are explicitly disallowed:

SQL statement fragments

sql fragments are not allowed

DECLARE
    @x := FROM trades
SELECT 5 @x;

-- table and column names that are SQL keywords have to be enclosed in double quotes, such as "FROM"```

Language client support

Some language SQL clients do not allow identifiers to be passed as if it was a normal value. One example is psycopg. In this case, you should use an alternate API to splice in identifiers, for example:

psycopg

cur.execute(
    sql.SQL("""
        DECLARE @col := {}
        SELECT max(@col), min(@col), avg(price)
        FROM btc_trades;
    """).format(sql.Identifier('price')))

Examples

SAMPLE BY

DECLARE with SAMPLE BYDemo this query

DECLARE
    @period := 1m,
    @window := '2024-11-25',
    @symbol := 'ETH-USDT'
SELECT
   timestamp, symbol, side, sum(amount) as volume
FROM trades
WHERE side = 'sell'
AND timestamp IN @window
AND symbol = @symbol
SAMPLE BY @period
FILL(NULL);

timestamp	symbol	side	volume
2024-11-25T00:00:00.000000Z	ETH-USDT	sell	153.470574999999
2024-11-25T00:01:00.000000Z	ETH-USDT	sell	298.927738
2024-11-25T00:02:00.000000Z	ETH-USDT	sell	66.253058
...	...	...	...

INSERT INTO SELECT

INSERT INTO trades (timestamp, symbol)
SELECT * FROM
(
    DECLARE
        @x := now(),
        @y := 'ETH-USDT'
    SELECT @x as timestamp, @y as symbol
);

CREATE TABLE AS SELECT

CREATE TABLE trades AS (
    DECLARE
        @x := now(),
        @y := 'ETH-USDT'
    SELECT @x as timestamp, @y as symbol, 123 as price
);