Configuration – Configure PostgreSQL Column Capitalization for Flexible Querying

configurationpostgresql

I have an existing database that we need to replace from MySQL to PostgreSQL. The problem being that we currently have two applications that use the database (one to create and one to view the data) that use different capitalization.

As the viewing application is in php it will grab the data using the following:

$row["Data"]

But the column "Data" doesn't exist because PostgreSQL converts it to lowercase to there is only the column "data".

A solution to this would be to rewrite the database and use "Data" in the queries and make the queries case-sensitive. The only problem is that this would break the creating application as it uses the lowercase variant.

So the question is, if there is some kind of setting within PostgreSQL that would allow to use capitalization for the column names while having case-insensitive queries?

Best Answer

As documentation claims unquoted identifiers are case insensitive. If you see that the identifiers are case-sensitive, this means that your application (connection library?) adds identifiers quoting. Configure it properly. - akina

Is there some kind of setting within PostgreSQL that would allow to use capitalization for the column names while having case-insensitive queries?

You will have to modify at least one of your applications. There is no parameter in PostgreSQL that makes it "partly case sensitive". - laurenz-albe

Related Solutions

Postgresql – Can PostgreSQL create a case sensitive database

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.

Postgresql – WorkAround for PHP PDO(with libpq V 9.1.4) binding for use of CITEXT

Locate the namespace inside which the citext type resides:

select nspname from pg_type t join pg_namespace n
  on n.oid=t.typnamespace where typname='citext';

Prepend that namespace (normally, 'public', but it might be different in your case and it might explain the problem) to the cast to citext:

$sql = "SELECT column1 from schema1.column1 where column1 = 'value'::public.citext";

If that solves the problem but in a way you find inelegant, you might reconsider how you're using schemas and search_path: make sure that all your custom types are accessible no matter what.

Best Answer

Related Solutions

Postgresql – Can PostgreSQL create a case sensitive database

Postgresql – WorkAround for PHP PDO(with libpq V 9.1.4) binding for use of CITEXT

Related Question