Can PostgreSQL create a case sensitive database?

Question

I'm porting some code from some version of Sybase to PostgreSQL. This is a C application that uses the Sybase client library. My approach is to write a translation layer that translates calls to dbsqlexec() to PQexec() (for example). That part is mostly working.

It appears that the Sybase database is set up in a case sensitive manner (with respect to database object names). For example, there is both a WIDGET table and a widget table. It looks like the convention in this application is that the all-uppercase names indicate the actual data tables, while the lowercase names are used as temporary tables when running some processing.

According to 4.1 Lexical Structure, "Key words and unquoted identifiers are case insensitive." I know that I can double-quote identifiers to disable automatic folding to lowercase, but I don't want to have to do that manually through the zillions of lines of code that uses this database.

Is there a way to set up PostgreSQL to disable this automatic case folding for database object identifiers?

My alternative will be to write some code that examines each SQL statement and puts double quotes around every identifier (that is not a keyword).

• Erwin Brandstetter: Keywords can be used as identifiers if double quoted - even though you shouldn't do that. In any case, you can't be sure that some identifiers from your Sybase code base aren't keywords in PostgreSQL. All the more reason to double-quote identifiers or, preferably, rename those.
• JamesRyan: unquoted identifiers in postgres are actually not case insensitive at all, they are treated as all lower case. so tAbLeNaMe will match a single table called tablename but not one called tableName. I would rename the tables because otherwise people will forget the " and end up accessing the lowercase version by mistake.
• user1822: @JamesRyan: that's wrong. select * from TaBlEnAmE will reference the same table as select * from tablename or select * from TABLENAME
• Mike Sherrill 'Cat Recall': @a_horse_with_no_name: If you create table "tableName" (id integer primary key);, and then create table "tablename" (id integer primary key); then this query select * from TaBlEnAmE; will select from "tablename", not from "tableName". "Unquoted names are always folded to lowercase".

Answer 1

I ended up writing some code that transforms the SQL generated by the application into PostgreSQL-compatible SQL. It's pretty straightforward:

• Split the statement into sensible tokens, skipping single-quoted string literals
• Double-quote anything that is not a keyword or number

I also took advantage of this layer to transform calls to isnull to coalesce. So far it's working pretty well.

• Anuj Patel: i use quotes ("blahblablah") to make it case sensitive.. it works smooth for me..

Answer 2

Is there a way to set up PostgreSQL to disable this automatic case folding for database object identifiers?

Not directly. You might be able to make a relatively minor change to the PostgreSQL source code, and recompile it. (Start in src/backend/parser/parser.c?) But I'd be surprised if it were very simple.

• Greg Hewgill: I'd rather not mess with the source code, as that would require a custom change to the PostgreSQL install every time anything changed (host, version, etc), and binary installs would be unavailable.