Re: php cant see new table!! - Mailing list pgsql-novice

On Thu, Feb 23, 2006 at 10:38:27AM -0500, Sears, Jeremy wrote:
> Thanks for the response Michael. I have read over the documentation but I am
> still unclear as to why php doesnt see my new table. I can see it in my
> database, however php is unable to access it. I suspect that in the process
> of creating the table I must have missed an essential command or somthing..
[...]
> this is the sql I used to creat the table:
>
> CREATE TABLE "st"."IAM_appid" (
>   "id" INTEGER NOT NULL,
>   "application" CHAR(25) NOT NULL,
>   CONSTRAINT "IAM_appid_id_key" UNIQUE("id")
> ) WITH OIDS;

Is this the same table you were trying to access when you got the
error?  The error in your original message was

> > Warning: pg_query(): Query failed: ERROR: relation "st.iam" does not exist

which isn't the same table.  Aside from that it still looks like
the problem is due to quoted identifiers.  Your code is probably
doing something like this:

SELECT * FROM st.IAM_appid;

when it should be doing this:

SELECT * FROM st."IAM_appid";

Notice the quotes around the table name in the second case.  Unquoted
identifiers are folded to lowercase, as you can see in the following
error message:

test=> SELECT * FROM st.IAM_appid;
ERROR:  relation "st.iam_appid" does not exist

Since you created the table with mixed case and quotes you'll always
have to use a quoted identifier and that exact case; some people
avoid quoted identifiers for that reason.  Without quotes you can
still use mixed case but the identifiers will be folded to lower
case:

test=> CREATE TABLE Foo (bAR integer);
CREATE TABLE
test=> \d
       List of relations
Schema | Name | Type  | Owner
--------+------+-------+-------
public | foo  | table | mfuhr
(1 row)

test=> \d foo
      Table "public.foo"
Column |  Type   | Modifiers
--------+---------+-----------
bar    | integer |

SELECT Bar FROM fOO;
bar
-----
(0 rows)

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match