Thread: Case insensitivity in column and table names

We are in the process of converting some databases from MySQL to PostgreSQL
-- our code to access these databases is in PHP. Our convention for column
and table names is to use uppercase words.

e.g. select ID, USERNAME from USERS where ID=42;

(We find that this convention visually makes it easy to see the important
details of the query.)

In the PHP, we may have something like:

$result = mysql_query('select ID, USERNAME from USERS where ID=42');
$row = mysql_fetch_assoc($result);

$id = $row['ID'];
$username = $row['USERNAME'];

We have encountered a big problem when doing the same thing in Postgres, as
Postgres seems to lowercase all the column names. Since associative arrays
in PHP *are* case-sensitive, all of our existing code thus breaks with the
exact same tables.

Is there any way possible to get Postgres to preserve the case we give it
when we create our tables? I've done many searches, and can't seem to find
anything helpful. Surely others have encountered this problem in the past.

Thanks,

Patrick

---
 patrick gibson
 http://patrickgibson.com/

Hi Patrick:

You can use quotes around each column to preserve case when creating
your tables in PostgreSQL.  That should help retrieval of data via PHP
in the manner you desire.

Regards,
Roj

--
niyogi@pghoster.com

pgHoster - PostgreSQL Database & Web Hosting
www.pghoster.com



Patrick Gibson wrote:

>We are in the process of converting some databases from MySQL to PostgreSQL
>-- our code to access these databases is in PHP. Our convention for column
>and table names is to use uppercase words.
>
>e.g. select ID, USERNAME from USERS where ID=42;
>
>(We find that this convention visually makes it easy to see the important
>details of the query.)
>
>In the PHP, we may have something like:
>
>$result = mysql_query('select ID, USERNAME from USERS where ID=42');
>$row = mysql_fetch_assoc($result);
>
>$id = $row['ID'];
>$username = $row['USERNAME'];
>
>We have encountered a big problem when doing the same thing in Postgres, as
>Postgres seems to lowercase all the column names. Since associative arrays
>in PHP *are* case-sensitive, all of our existing code thus breaks with the
>exact same tables.
>
>Is there any way possible to get Postgres to preserve the case we give it
>when we create our tables? I've done many searches, and can't seem to find
>anything helpful. Surely others have encountered this problem in the past.
>
>Thanks,
>
>Patrick
>
>---
> patrick gibson
> http://patrickgibson.com/
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>

On 5/14/03 9:48, Roj Niyogi wrote:

> You can use quotes around each column to preserve case when creating
> your tables in PostgreSQL.  That should help retrieval of data via PHP
> in the manner you desire.

Hi Roj, thanks for the tip. It sort of works:

> create table USERS
> (
>     "ID" int not null primary key,
>     "EMAIL" varchar(255) not null
> );

> insert into USERS values ('1', 'user@email.com');

This query works:

> # select * from USERS;
>  ID |     EMAIL
> ----+----------------
>   1 | user@email.com

... but:

> # select ID, EMAIL from USERS;
> ERROR:  Attribute "id" not found

does not. If I quote the columns like ``select "ID", "EMAIL" from USERS;''
it works, however the same query does not work in MySQL. I'm hoping to find
a solution that works with both databases, as we still plan to use MySQL for
some stuff. Our framework has some required tables and shared code, so the
same queries must work on both.

Ultimately if we have to, we will convert everything to lowercase -- but
that is really undesirable for us.

Any ideas?

Thanks,

Patrick

---
 patrick gibson
 http://patrickgibson.com/

On 5/15/03 5:56, Dani Oderbolz wrote:

>> We have encountered a big problem when doing the same thing in Postgres, as
>> Postgres seems to lowercase all the column names. Since associative arrays
>> in PHP *are* case-sensitive, all of our existing code thus breaks with the
>> exact same tables.
>>
> I don´t quite get your point.
> In Postgres 7.3.2 I can do this:
>
> Select MyColumn from MyTable;
> --Works
>
> as well as
> Select mycolumn from mytable;
> --Works as well
>
> Its just that internally postgres stores your object names in lowercase -
> which should not matter to you.

It does matter because when it comes back to PHP, the keys in the array are
lowercase, even though the query specified them as uppercase. In the past
(with MySQL) we've always been able to rely on the database returning keys
in the same case as we specify -- PHP arrays are case sensitive, which is
where the problem comes in.

If we have the query, 'select ID, USERNAME from USERS' with a MySQL
database, we'd get an associate array with 'ID' and 'USERNAME' being the
keys. The same query with Postgres will give us keys of 'id' and 'username',
which is different.

The goal of what we want to do is to be able to use the same code no matter
which database we are using. We ultimately will switch to a lowercase
convention to accommodate Postgres, but I'm trying to find out if that
really is the only option. The quoting technique does not work either
because MySQL handles it differently.

Patrick


---
 patrick gibson
 http://patrickgibson.com/