Hello all,

Have a very annoying problem when coding queries in php to be sent to postgres. I have to type the query specifically like this:

'select "User"."UserName",
"Contact"."Nickname"

from "User"
inner join "Contact"
on "User"."UserID" = "Contact"."UserID"
where "User"."UserID" = 3'

with all of the quoting or it throws an error. Took me a while to figure out this problem since I have not found any documentation online or otherwise, that shows the need to code this way.

Platform,
Mac OS X(dev box), Max OS X Server, php, apache, postgresql

While stuff works, it is really an annoyance to have to quote this way when my friends on their machines dont have these issues.

Thanks for the help,
David

    It looks like this may be a capitlization problem. Try using all lowercase names when creating your tables and such and see how that works.

    Also, look through the postgresql developer lists for known bugs with Mac OSX, as there have been a few problems that Apple and postgresql have released patches for that may be needed.

      You've used mixed case in your identifiers, you you're going to need to use double quotes all the time. Just make them all lowercase.

      Chris

        14 days later

        PostgreSQL has a rather funny way of dealing with case-sensitivity. In fact, the reason you are having this problem is because PostgreSQL allows you TOO much flexibility 😉.

        1. The simplest way to work with PostgreSQL is to use only lower-case table and column names.

        2. If you don't use quotes when creating your table, then you can actually use ANY case when querying, as long as you don't use quotes when querying.

          CREATE TABLE Users (
UserID SERIAL,
UserName varchar(50)
);

        Actually creates a table called "users". You can now query this table with complete case insensitivity:

        SELECT UserID, UserName FROM Users;
        SELECT userid, username FROM users;
        SELECT usErid, useRnaMe FROM USERs;

        All of these queries will return columns with lowercase names, though, so remember that when creating PHP variable names.

        1. If you use quotes when creating your table name and column names, now you must use quotes when querying the table. One benefit of this is that the quotes allow you to have unusual table and column names (including spaces or other characters) that would cause problems on simpler systems like MySQL. This can be very useful if you need the system to collaborate with some unusual software requirement, such as Access, which allows spaces in tablenames, etc... Also helpful for migration from same systems 😉.

        My guess is that you used phpPgAdmin or some other front-end admin kit, which automatically added the quotes to your tablenames on creation.

          Write a Reply...