# DECLARE keyword

DECLARE SQL keyword reference documentation.

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

This syntax is supported within `SELECT` queries.

## Syntax

```questdb-sql title="Standalone query"
DECLARE @variable := expression [, @variable := expression ...]
[WITH ...]
SELECT ...
```

```questdb-sql title="Inside a view definition (with optional OVERRIDABLE)"
DECLARE [OVERRIDABLE] @variable := expression
    [, [OVERRIDABLE] @variable := expression ...]
[WITH ...]
SELECT ...
```

The `OVERRIDABLE` keyword only takes effect inside a
[view definition](/docs/query/sql/create-view/#declare-with-overridable). 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.

## Mechanics

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

```questdb-sql title="Basic DECLARE" demo
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:

```questdb-sql title="basic DECLARE post-reduction" demo
SELECT 5;
```

| 5 |
|---|
| 5 |

### Multiple bindings

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

```questdb-sql title="Multiple variable bindings" demo
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:

```questdb-sql title="declaring function variable" demo
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.

```questdb-sql title="variable shadowing" demo
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.

```questdb-sql title="variable shadowing" demo
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:

```questdb-sql title="table cursor as a variable" demo
DECLARE
    @subquery := (SELECT timestamp FROM trades)
SELECT * FROM @subquery;
```

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

```questdb-sql title="nesting decls inside decl subqueries" demo
DECLARE
    @timestamp := timestamp,
    @symbol := symbol,
    @subquery := (SELECT @timestamp, @symbol FROM trades)
SELECT * FROM @subquery;
```

### Declarations in CTEs

Naturally, `DECLARE` also works with CTEs:

```questdb-sql title="declarations inside CTEs" demo
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 |

### 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:

```questdb-sql
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:

```questdb-sql
-- 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:

```questdb-sql
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:

#### Bracket lists

```questdb-sql title="bracket lists are not allowed"
DECLARE
    @symbols := ('BTC-USDT', 'ETH-USDT')
SELECT timestamp, price, symbol
FROM trades
WHERE symbol IN @symbols;

-- error: unexpected bind expression - bracket lists not supported
```

#### SQL statement fragments

```questdb-sql title="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:

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

## Examples

### SAMPLE BY

```questdb-sql title="DECLARE with SAMPLE BY" demo
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

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

### CREATE TABLE AS SELECT

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