11

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).

Improve this question
4
  • 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.
    – Erwin Brandstetter
    Jan 12, 2012 at 10:01
  • 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.
    – JamesRyan
    Jan 12, 2012 at 10:51
  • @JamesRyan: that's wrong. select * from TaBlEnAmE will reference the same table as select * from tablename or select * from TABLENAME
    – user1822
    Jan 12, 2012 at 20:15
  • @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".
    – Mike Sherrill 'Cat Recall'
    Jan 13, 2012 at 1:35

2 Answers 2

Reset to default
3

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.

Improve this answer
1
  • i use quotes ("blahblablah") to make it case sensitive.. it works smooth for me..
    – Anuj Patel
    Jan 31, 2012 at 9:30
1

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.

Improve this answer
1
  • 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.
    – Greg Hewgill
    Jan 13, 2012 at 4:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.