> ## Documentation Index
> Fetch the complete documentation index at: https://docs.lancedb.com/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL identifiers and strings

> Quote camelCase column names in SQL, table filters, and update expressions.

Enterprise only

Use **backticks** around column names in SQL expressions when you need to
preserve case or include spaces. Backticks work in both Enterprise SQL statements
and table SDK expression strings. Use **single quotes** for SQL string literals,
and pass **bare column names** as the keys of `values` and `values_sql`.

| Context | Column reference | String literal |
| - | - | - |
| SQL statements, including `execute_query` and materialized view definitions | `"averageRating"` or `` `averageRating` `` | `'reviewed'` |
| `table.update(values_sql=...)` expression values | `` `averageRating` `` | `'reviewed'` |
| Table SDK filters, including `where=` and `.where(...)` | `` `movieId` `` | `'reviewed'` |
| `values` and `values_sql` dictionary keys | `"averageRating"` as a Python string, without SQL quote characters inside it | Not an expression |

> [!WARNING]
>
> Double quotes have different meanings across these interfaces. In SQL statements,
> `"averageRating"` is a column reference. In `values_sql` expressions it is a string
> literal, so `"averageRating" + 0.1` fails with an error such as
> `Cannot coerce arithmetic expression Utf8 + Float64 to valid types`.
> Some SDK filter paths also accept double-quoted identifiers; use backticks to
> keep the same spelling across filters and update expressions.

## Preserve camelCase names in SQL

SQL lowercases unquoted identifiers. If your table has a column named `movieId`,
`SELECT movieId FROM movies` looks for `movieid` and fails. Quote each column
reference to preserve its exact name:

```python theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
db.execute_query('''
    SELECT m."movieId", m."averageRating"
    FROM movies AS m
    WHERE m."numVotes" > 100
''').read_all()
```

Backticks work in the same query:

```sql theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT m.`movieId`, m.`averageRating`
FROM movies AS m
WHERE m.`numVotes` > 100
```

Apply the same rule inside the `SELECT` query of a
[materialized view definition](https://docs.lancedb.com/enterprise/v1-13-0/sql/ddl/materialized-view.md).

## Update columns and filter rows with the SDK

The following examples assume `movies` has numeric columns `movieId`,
`averageRating`, and `numVotes`, and a string column `reviewStatus`.

Use backticks for column references inside both `where` and `values_sql`
expression strings:

```python theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
table = db.open_table("movies")
table.update(
    where="`movieId` = 1",
    values_sql={"averageRating": "`averageRating` + 0.1"},
)

rows = table.search().where("`numVotes` > 100").to_list()
count = table.count_rows("`numVotes` > 100")
```

The dictionary key `"averageRating"` contains only the column name. Its enclosing
quotes are Python syntax. A key such as `'"averageRating"'` or
``"`averageRating`"`` includes SQL quote characters in the name and fails because
that column does not exist.

For a constant value, use `values` and pass the Python value directly:

```python theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
table.update(where="`movieId` = 1", values={"reviewStatus": "reviewed"})
```

With `values_sql`, write the constant as a SQL string literal instead:

```python theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
table.update(
    where="`movieId` = 1",
    values_sql={"reviewStatus": "'reviewed'"},
)
```

The outer double quotes delimit a Python string; the inner single quotes are
part of the SQL expression.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.