Skip to main content

Edit this page

Keywords and Identifiers

Identifiers​

Similarly to other SQL dialects and programming languages, identifiers in SereneDB's SQL are subject to several rules.

  • Unquoted identifiers need to conform to a number of rules:
    • They must not be a reserved keyword (see duckdb_keywords()), e.g., SELECT 123 AS SELECT will fail.
    • They must not start with a number or special character, e.g., SELECT 123 AS 1col is invalid.
    • They cannot contain whitespaces (including tabs and newline characters).
  • Identifiers can be quoted using double-quote characters ("). Quoted identifiers can use any keyword, whitespace or special character, e.g., "SELECT" and " § 🌊 ¶ " are valid identifiers.
  • Double quotes can be escaped by repeating the quote character, e.g., to create an identifier named IDENTIFIER "X", use "IDENTIFIER ""X""".

Duplicate Identifiers​

In some cases, duplicate identifiers can occur, e.g., column names may conflict when unnesting a nested data structure. Following PostgreSQL, SereneDB allows duplicate column names in a result and preserves them as-is — they are not renamed or deduplicated.

For example, recursively unnesting a struct whose nested fields repeat a name yields a result with repeated column names:

Query
SELECT *FROM (SELECT unnest({'a': 42, 'b': {'a': 88, 'b': 99}}, recursive := true));
Result
 a  | a  | b----+----+---- 42 | 88 | 99

Database Names​

Database names are subject to the rules for identifiers.

Additionally, it is best practice to avoid SereneDB's two internal database schema names, system and temp. By default, persistent databases are named after their filename without the extension. Therefore, the filenames system.db and temp.db (as well as system.duckdb and temp.duckdb) result in the database names system and temp, respectively. If you need to attach to a database that has one of these names, use an alias, e.g.:

Query
ATTACH 'temp.db' AS temp2;
USE temp2;

Rules for Case-Sensitivity​

Keywords and Function Names​

SQL keywords and function names are case-insensitive in SereneDB.

For example, the following two queries are equivalent:

Query
select COS(Pi()) as CosineOfPi;
SELECT cos(pi()) AS CosineOfPi;
Result
 cosineofpi------------         -1
 cosineofpi------------         -1

Case-Sensitivity of Identifiers​

Identifiers in SereneDB are always case-insensitive, similarly to PostgreSQL. However, unlike PostgreSQL (and some other major SQL implementations), SereneDB also treats quoted identifiers as case-insensitive.

Comparison of identifiers: Case-insensitivity is implemented using an ASCII-based comparison: col_A and col_a are equal but col_á is not equal to them.

Query
SELECT col_A FROM (SELECT 'x' AS col_a);
SELECT col_á FROM (SELECT 'x' AS col_a);
Result
 col_a------- x
db error: ERROR: Referenced column "col_á" not found in FROM clause!Candidate bindings: "col_a"

Preserving cases: While SereneDB treats identifiers in a case-insensitive manner, it preserves the cases of these identifiers. That is, each character's case (uppercase/lowercase) is maintained as originally specified by the user even if a query uses different cases when referring to the identifier. For example:

Query
CREATE TABLE tbl AS SELECT cos(pi()) AS CosineOfPi;
SELECT cosineofpi FROM tbl;
Result
 cosineofpi------------         -1

To change this behavior, set the preserve_identifier_case configuration option to false.

Case-Sensitivity of Keys in Nested Data Structures​

The keys of MAPs are case-sensitive (looking up A when the key is a finds nothing):

Query
SELECT (MAP {'a': 1})['A'] IS NOT NULL AS found;
Result
 found------- f

The keys of UNIONs and STRUCTs are case-insensitive:

Query
SELECT union_extract(union_value(a := 1), 'A') IS NOT NULL AS found;
Result
 found------- t
Query
SELECT ({'a': 1}).A IS NOT NULL AS found;
Result
 found------- t

Handling Conflicts​

When the same identifier is spelt with different cases within a nested structure, SereneDB raises an error rather than silently picking one. For example:

Query
SELECT {'a': 1, 'A': 2};
USE $__DATABASE__;
DETACH IF EXISTS db91;
Result
db error: ERROR: Duplicate struct entry name "A"

Disabling Preserving Cases​

With the preserve_identifier_case configuration option set to false, all identifiers are turned into lowercase:

Query
SET preserve_identifier_case = false;
CREATE TABLE tbl AS SELECT cos(pi()) AS CosineOfPi;
SELECT CosineOfPi FROM tbl;
Result
 cosineofpi------------         -1