Reference identifier case conversion design

1. Objective

  • In order to meet the case compatibility of PG and Oracle’s reference identifiers, ivorysql has designed three case conversion modes for reference identifiers. Select the conversion mode via the GUC parameter "identifier_case_switch";

2. Function

2.1. Three modes of case conversion (interchange by default)

  • If the value of the guc parameter "identifier_case_switch" is "interchange":

    1). If the letters in the identifier referenced by the double quotation mark are all uppercase, uppercase is converted to lowercase.
    2). If the letters in the identifier referenced by the double quotation mark are all lowercase, lowercase is converted to uppercase.
    3). If the letters in the identifier enclosed in double quotation marks are mixed-case, the identifier is left unchanged.

2.2. When the database cluster is initialized

  • Add the -C option to the initdb program to set the case conversion mode, and the corresponding value of -C is:

    "normal"   ------ "0"synonymy
    "interchange" ------ "1"synonymy
    "lowercase"  ------ "2"synonymy

During initialization of the database cluster, the case conversion pattern is saved to the global/pg_control file in the data directory;

2.3. Test cases

SET ivorysql.enable_case_switch = true;
SET ivorysql.identifier_case_switch = interchange;
CREATE TABLE "ABC"(c1 int, c2 int);
SELECT relname FROM pg_class WHERE relname = 'ABC';
SELECT relname FROM pg_class WHERE relname = 'abc';
SELECT * FROM "ABC";
SELECT * FROM ABC;
SELECT * FROM abc;
SELECT * FROM Abc;
SELECT * FROM "Abc"; -- ERROR
DROP TABLE abc;

CREATE TABLE "Abc"(c1 int, c2 int);
SELECT relname FROM pg_class WHERE relname = 'ABC';
SELECT relname FROM pg_class WHERE relname = 'abc';
SELECT relname FROM pg_class WHERE relname = 'Abc';
SELECT * FROM "ABC"; -- ERROR
SELECT * FROM ABC; -- ERROR
SELECT * FROM abc; -- ERROR
SELECT * FROM Abc; -- ERROR
SELECT * FROM "Abc";
DROP TABLE "Abc";