I hope this is an easy one.. I am migrating a small application from using MSSQL to us Postgres.

In neither MSSQL nor MySQL do I have to quote table and field names in the query (and then escape them). But Postgres is requiring that table names and field names are in quotes when it gets sent through PG_EXEC.

Is this just something I'll have to deal with or there a blaringly obvious answer?

Thanks!

Jerry

    I've never had this problem before, I'd guess something somewhere is misconfigured.

    (I use postgresql a lot too.)

    What platforms are you running on?

      misconfigured ... [or ] What platforms are you running on?

      Another possibility--what kind of magical characters do you have in your mysql table names?

        I kind of figured that it might be a configuration problem. Magic-quotes are on for post/get only. (runtime & sybase magicquotes are off)

        The box is RedHat 6.2 w/ Apache 1.3.14, PHP 4.0.4pl1 and Postgres 7.0 (RPMs from PosgreSQLs site, not RedHats Dist CD)

        Also running is MySQL 3.23. Plus PHP is compiled with FreeTDS 0.50 for connecting to MSSQL 7.02

        Table names are letters and the underscore, that is all. Table names are letters only.

        A clarification of the problem is that my queries, I have to put quotes around all table and field names ( single quote around data values as usual, though) or I get SQL errors from Posgres.

        It really has me stumped... I've never run into this before and I've used several different databases with PHP Just never Postgres.

        Thanks all for the help!

          How does the psql monitor work on that box? Does it too require quotes around table and field names?

            Scott Marlowe wrote:

            How does the psql monitor work on that box? Does it too require quotes around table and field names?

            psql gives the exact same behavior. ie:

            select * from Profiles;

            ERROR: Relation 'profiles' does not exist

            But this works just fine in psql:

            select * from "Profiles";

            My PHP program works just fine as long as my queries are properly quoted and escaped. It's rather annoying but something I can live with if necessary.

            The main concerns that I have over this is the potential for data loss/corruption if something isn't right, and then the issues of porting if I switch to a different database in the future.

            Thanks all for your help!

              Something to do with uppercase in names?

                Possibly.. I created a test table with table and cols in all lower case and the quoting wasn't necessary. An interesting tidbid of info....

                Maybe this should be in the manual somewhere, especially considering this behavior differs from several other RDBMS systems.

                Thanks!

                  I just made a table:

                  postgres=# create table Test4 (City text, State text, ZIP text);
                  CREATE
                  postgres=# insert into Test4 (City, State, ZIP) values ('Jacksonville','FL','32256');
                  INSERT 348739 1
                  postgres=# select City, State, ZIP from Test4
                  postgres-# ;
                  city | state | zip
                  --------------+-------+-------
                  Jacksonville | FL | 32256
                  (1 row)

                  postgres=#

                  No escapes or anything, it just worked. If that doesn't work in your postgresql then I wonder what is configured different from yours to mine. could we see your ./configure script used to build it? System it's on, stuff like that. My box doesn't do this, and neither should yours... If it's a semi common problem it should probably be put into the postgresql faq to help others if we can figure out what's wrong.

                    Well, I would but I used the i386 RPMs for RH6.x from Postgresql's FTP site.

                    It's installed on RH6.2.

                    I'll grab the source and recompile it all and see what happens...

                      7 months later

                      Did this ever get resolved. I've got the same problem with the version that ships with suse7.2? I'm in the proces of porting JDBC/ORACLE code and this is really getting to be drag. I've compiled on redhat 7.0 and it does not have this problem so I'll try to compile on Suse next and see if that fixes.

                      Thanks in advance for any help/pointers.

                        I should have mentioned that I am running postgresql 7.1.2 and 7.1.3 on my boxes at home and at work respectively. Postgresql and PHP have (ahem) issues with upper and lower case table and column names.

                        I've found I have to stick to all lower case to be safe across all versions of postgresql and php.

                        It would appear that in 7.1.2, everything is in upper case. This command:

                        create table Tellers23 (name text, id serial, balance numeric(20,2));

                        resulted in this output:

                        NOTICE: CREATE TABLE will create implicit sequence 'tellers23_id_seq' for SERIAL column 'tellers23.id'
                        NOTICE: CREATE TABLE/UNIQUE will create implicit index 'tellers23_id_key' for table 'tellers23'

                        \d showed this:

                        List of relations
                        tellers23 | table | postgres
                        tellers23_id_seq | sequence | postgres

                        I've found that the interaction of php with older versions of postgresql isn't very good, but with php 4.0.5 and postgresql 7.1.2 and up, it's all fixed. User upper or lower, postgres turns it all lower, and php seems case insensetive on my box (RH 7.0 at home/7.1 at work) to table and column names.

                          I meant to say that it appears in 7.1.2 everything is in LOWER case, not upper case.

                            Write a Reply...