From: Richard Huxton <dev@archonet.com>
To: Klas Stockhem <Klas.Stockhem@benders.se>
Cc: PostgreSQL <pgsql-sql@postgresql.org>
Subject: Re: Problems when copy data from dump file
Date: Thu, 06 Aug 2009 08:36:17 +0100
Message-ID: <4A7A87F1.60904@archonet.com> (raw)
In-Reply-To: <2A250781A3744D48B1529FCC3C2EB8ABCB483B@comserver.benders.se>
References: <2A250781A3744D48B1529FCC3C2EB8ABCB483B@comserver.benders.se>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, Klas.Stockhem@benders.se
Subject: Re: Problems when copy data from dump file
In-Reply-To: <4A7A87F1.60904@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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