Message-ID: <4A7A87F1.60904@archonet.com>
Date: Thu, 06 Aug 2009 08:36:17 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.21 (X11/20090320)
MIME-Version: 1.0
To: Klas Stockhem <Klas.Stockhem@benders.se>
CC: PostgreSQL <pgsql-sql@postgresql.org>
Subject: Re: Problems when copy data from dump file
References: <2A250781A3744D48B1529FCC3C2EB8ABCB483B@comserver.benders.se>
In-Reply-To: <2A250781A3744D48B1529FCC3C2EB8ABCB483B@comserver.benders.se>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Klas Stockhem wrote:
 > Does the php engine interpret the big P like a small one?

It's not PHP, but PostgreSQL itself. All names in PostgreSQL are case 
insensitive unless you double-quote them. PostgreSQL actually translates 
everything to lower-case internally.

CREATE TABLE1 ...;    -- gives "table1"
CREATE "TABLE2" ...;  -- gives "TABLE2"

SELECT * FROM table1   -- works
SELECT * FROM TABLE1   -- works
SELECT * FROM TaBlE1   -- works
SELECT * FROM "table1" -- works
SELECT * FROM "TABLE1" -- FAILS, actually "table1"
SELECT * FROM table2   -- FAILS, looks for "table2"
SELECT * FROM TABLE2   -- FAILS, still looking for "table2"
SELECT * FROM "TABLE2" -- works

So - if you double-quote a table-name (or function or schema name) when 
you create it, you should always double-quote it when using it. I'm 
guessing something added double-quotes for you when you created the schema.

 > Is it recommended to always have small letters in schema names?

Personally, I do. I think it looks neater.

 > How do I make so I not must type the schema name in every sql query?
 > The best would be if I just can type the table name.

There is a variable called "search_path" which controls what schemas are 
checked for tables, functions etc. If you have two tables with the same 
name but different schemas you'll need to use the <schema>.<table> 
format though.

SET search_path = public2,public;
ALTER USER myuser SET search_path = ...
ALTER DATABASE mydb SET search_path = ...

-- 
   Richard Huxton
   Archonet Ltd