GBase 8s: How DELIMIDENT Controls Case Sensitivity

Source: Dev.to

Demonstration of DELIMIDENT

Set the environment variable DELIMIDENT=y and run the statements:

export DELIMIDENT=y
dbaccess testdb <<!
-- Uppercase table name, double‑quoted
CREATE TABLE "Tab04"(id INT, name VARCHAR(20));
INSERT INTO "Tab04" VALUES(1, 'T DA XIE');

-- Lowercase table name, double‑quoted
CREATE TABLE "tab04" (id INT, name VARCHAR(20));
INSERT INTO "tab04" VALUES(1, 't xiao xie');

-- Without double quotes, this collides with the lowercase one (error 310)
CREATE TABLE tab04 (id INT, name VARCHAR(20));  -- fails
!

Verify tables in the system catalog

SELECT tabname FROM systables WHERE tabname LIKE '%ab04%';
-- Returns: tab04 and Tab04 (displayed without quotes, but distinguished)

Query Behavior with DELIMIDENT=y

Unquoted or lowercase‑quoted references resolve to the lowercase table tab04:

SELECT * FROM Tab04;     -- → tab04
SELECT * FROM tab04;     -- → tab04
SELECT * FROM "tab04";   -- → tab04

Only an exactly matching double‑quoted uppercase name reaches the uppercase table:

SELECT * FROM "Tab04";   -- → Tab04

After Unsetting DELIMIDENT

unset DELIMIDENT
dbaccess -e testdb <<!
SELECT * FROM Tab04;       -- → tab04 (still works)
SELECT * FROM tab04;       -- → tab04
SELECT * FROM "tab04";     -- error 201
SELECT * FROM "Tab04";     -- error 201
!

With DELIMIDENT unset, double‑quote wrapping becomes illegal, making the uppercase table unreachable.

DELIMIDENT Configurations

Recommendation

GBase 8s does not provide a way to achieve case‑sensitive identifiers without double quotes. Use DELIMIDENT=y and always write identifiers exactly as they were created. This ensures predictable behavior and avoids silent data‑access mistakes in a GBase environment.