pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Case insensitivity in column and table names
6+ messages / 4 participants
[nested] [flat]

* Case insensitivity in column and table names
@ 2003-05-14 16:46 Patrick Gibson <postgres_list@patrickg.com>
  2003-05-14 16:48 ` Re: Case insensitivity in column and table names Roj Niyogi <niyogi@pghoster.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Patrick Gibson @ 2003-05-14 16:46 UTC (permalink / raw)
  To: pgsql-php

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/




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Case insensitivity in column and table names
  2003-05-14 16:46 Case insensitivity in column and table names Patrick Gibson <postgres_list@patrickg.com>
@ 2003-05-14 16:48 ` Roj Niyogi <niyogi@pghoster.com>
  2003-05-14 17:27   ` Re: Case insensitivity in column and table names Patrick Gibson <postgres_list@patrickg.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Roj Niyogi @ 2003-05-14 16:48 UTC (permalink / raw)
  To: Patrick Gibson <postgres_list@patrickg.com>; +Cc: pgsql-php

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






^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Case insensitivity in column and table names
  2003-05-14 16:46 Case insensitivity in column and table names Patrick Gibson <postgres_list@patrickg.com>
  2003-05-14 16:48 ` Re: Case insensitivity in column and table names Roj Niyogi <niyogi@pghoster.com>
@ 2003-05-14 17:27   ` Patrick Gibson <postgres_list@patrickg.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Patrick Gibson @ 2003-05-14 17:27 UTC (permalink / raw)
  To: Roj Niyogi <niyogi@pghoster.com>; +Cc: pgsql-php

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/




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Case insensitivity in column and table names
@ 2003-05-15 17:47 Patrick Gibson <postgres_list@patrickg.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Patrick Gibson @ 2003-05-15 17:47 UTC (permalink / raw)
  To: Dani Oderbolz <oderbolz@ecologic.de>; +Cc: pgsql-php

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/




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Fw: Case insensitivity in column and table names
@ 2003-05-15 19:54 David Busby <busby@pnts.com>
  2003-05-19 08:32 ` Re: Case insensitivity in column and table names Jona <jonanews@orioninformationservices.com>
  0 siblings, 1 reply; 6+ messages in thread

From: David Busby @ 2003-05-15 19:54 UTC (permalink / raw)
  To: pgsql-php

Patrick,
    Could you simply place some strtolower() or strtoupper() in the code?
Or are is it when you do something like `pg_fetch_array($resource);` that it
becomes an issue?

/B




----- Original Message ----- 
From: "Patrick Gibson" <postgres_list@patrickg.com>
To: "Dani Oderbolz" <oderbolz@ecologic.de>
Cc: <pgsql-php@postgresql.org>
Sent: Thursday, May 15, 2003 10:47
Subject: Re: [PHP] Case insensitivity in column and table names


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/


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Case insensitivity in column and table names
  2003-05-15 19:54 Fw: Case insensitivity in column and table names David Busby <busby@pnts.com>
@ 2003-05-19 08:32 ` Jona <jonanews@orioninformationservices.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Jona @ 2003-05-19 08:32 UTC (permalink / raw)
  To: David Busby <busby@pnts.com>; pgsql-php

You could change the key case in the array after having called
pg_fetch_array($resource)
Use the buildin PHP function: array_change_key_case
http://dk.php.net/manual/en/function.array-change-key-case.php

Works for me with Firebird, PostGre and MySQL

/Jona

----- Original Message -----
From: "David Busby" <busby@pnts.com>
To: <pgsql-php@postgresql.org>
Sent: Thursday, May 15, 2003 9:54 PM
Subject: Fw: [PHP] Case insensitivity in column and table names


> Patrick,
>     Could you simply place some strtolower() or strtoupper() in the code?
> Or are is it when you do something like `pg_fetch_array($resource);` that
it
> becomes an issue?
>
> /B
>
>
>
>
> ----- Original Message -----
> From: "Patrick Gibson" <postgres_list@patrickg.com>
> To: "Dani Oderbolz" <oderbolz@ecologic.de>
> Cc: <pgsql-php@postgresql.org>
> Sent: Thursday, May 15, 2003 10:47
> Subject: Re: [PHP] Case insensitivity in column and table names
>
>
> 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/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>




^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2003-05-19 08:32 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-14 16:46 Case insensitivity in column and table names Patrick Gibson <postgres_list@patrickg.com>
2003-05-14 16:48 ` Roj Niyogi <niyogi@pghoster.com>
2003-05-14 17:27   ` Patrick Gibson <postgres_list@patrickg.com>
2003-05-15 17:47 Re: Case insensitivity in column and table names Patrick Gibson <postgres_list@patrickg.com>
2003-05-15 19:54 Fw: Case insensitivity in column and table names David Busby <busby@pnts.com>
2003-05-19 08:32 ` Jona <jonanews@orioninformationservices.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox