Database Object Case-Sensitivity
Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
Database objects allow you to store, reference, and operate on data.
Note
You can only change the setting of the variable table_
when the cluster is empty.
The engine variable table_
defines the case-sensitivity of a database object.ON
(the default setting), all database objects are case-sensitive, except:
-
Stored procedures
-
User-defined scalar-valued functions (UDFs)
-
User-defined aggregate functions (UDAFs)
-
information_
table namesschema
When the variable is set to OFF
, the four database objects noted in the previous list are case-insensitive, in addition to the following database objects.
-
Tables
-
Views
-
Table aliases
-
User-defined table-valued functions (TVFs)
-
External functions
Pipeline names are always case-sensitive.
The following example shows how you can refer to an existing table test_
.table_
is set to OFF
.
SELECT @@table_name_case_sensitivity;
+-------------------------------+
| @@table_name_case_sensitivity |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (0.04 sec)
INSERT INTO Test_Table(a) VALUES (10);
SELECT * FROM TEST_Table;
When you create a database object which is not case-sensitive, you must use a unique and case-independent name.table_
is set to OFF
, running the following two commands results in an error:
CREATE TABLE test_table_2(a INT);
CREATE TABLE test_TABLE_2(a INT);
The following example illustrates that column names are not subject to case-sensitivity even when table_
is set to ON
:
select @@table_name_case_sensitivity;+-------------------------------+| @@table_name_case_sensitivity |+-------------------------------+| 1 |+-------------------------------+
CREATE TABLE test_table_3 (a int, b INT);
INSERT INTO test_table_3 (a, b) VALUES (1, 2);
SELECT a, b FROM test_table_3;
+------+------+
| a | b |
+------+------+
| 1 | 2 |
+------+------+
SELECT A, B FROM test_table_3;
+------+------+
| A | B |
+------+------+
| 1 | 2 |
+------+------+
Last modified: April 11, 2024