How can I enable case-insensitive search on openGauss

Question

How can I enable case-insensitive search on openGauss ? My fields are all uppercase when creating the tables, and when searching, it is converted to lowercase and causes failure.

SELECT XH,QYBH from user
Error info： No xh column found

Answer 1

The column names in openGauss will be case-sensitive if the column name was enclosed in double quotes when defining it, otherwise they will be case-insensitive. When you run a SELECT query unquoted column names are automatically converted to lower-case. As a result, if a column name is case-sensitive you need to wrap the name in double quotes.

Here are some examples:

CREATE TABLE t1 ("COLA" int, "COLB" int);
SELECT "COLA","COLB" FROM t1; -- works
SELECT cola,colb FROM t1; -- does not work
SELECT COLA,COLB FROM t1; -- does not work

CREATE TABLE t2 (COLA int, COLB int);
SELECT cola,colb FROM t2; -- works 
SELECT COLA,COLB FROM t2; -- works
SELECT CoLa,cOLb FROM t2; -- works
SELECT "cola","colb" FROM t2; -- works
SELECT "cola","colb" FROM t2; -- does not work