postgres and case insensitive column names.

2005 March 17 at 12:54 » Tagged as :

One of the issues that I overlooked when converting the Rad User Manager, from mysql to postgresql is the fact that postgresql squashes all column names to lowercase. Does it matter? it does if you try to fetch associative arrays.

The lower casing of column names is said to be the sql standard. In other words column names are case insensitive unless the name is quoted. Mysql does not follow that, the Rad User Manager was first written using mysql as a backend and it's stands to reason that we should use the mysql_fetch_array() method instead of mysql_fetch_row() when the query involves many columns. Unfortunately, when you do the same with pg_fetch_assoc() all the fields appear as unset for the simple reason that the column name is in lower case while the keys are in 'camel case'.

There were two solutions proposed on the web, the first is that column names should be quoted and that is a very good suggestion :-) The second suggestion is that you make use of the array_change_case() method of PHP, which is also a very good suggestion if you have already created your database and done a hell of a lot of coding before you encountered this problem.

In the case of the Rad User Manager there is time to change and we will change the schema so that all the column names are quoted. The online convertor as well as the migration guide will have to change too.

And these ladies and gentlemen are the references:

http://www.issociate.de/board/post/10328/Fw:_Case_insensitivity_in_column_and_table_names.html

http://archives.postgresql.org/pgsql-jdbc/2004-10/msg00124.php

http://www.php.net/array_change_key_case

And these were dug up with the help of big brother