From: Klas Stockhem <Klas.Stockhem@benders.se>
To: Richard Huxton <dev@archonet.com>
Cc: PostgreSQL <pgsql-sql@postgresql.org>
Subject: Re: Problems when copy data from dump file
Date: Thu, 6 Aug 2009 10:39:46 +0200
Message-ID: <2A250781A3744D48B1529FCC3C2EB8ABCB48FF@comserver.benders.se> (raw)
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
>
>
>
Yes It works fine when I execute the SET command and the ALTER USER
command in the EMS software!
Now my web application return data from the database tables that
contains some data. (I have manually put data in some tables from the
EMS software).
I have "a lot of" data still left in the dump file to put in the
database. I can manually put the data in the tables but this will take
some times.
The data in the dump file are structured and I want to execute this
automatically (copy+paste it) in the EMS software.
I still get the syntax error even if I use the command you send me:
COPY artikkel (id, tittel, tekst) FROM stdin; 1<tab>Title one<tab>Some
text \.
This is the error I get : "ERROR: syntax error at or near "1" at
character 47".
It feels like it is some simple error but I can't find it out. I would
help me a lot if I get a solution.
//Klas
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: Klas.Stockhem@benders.se, dev@archonet.com
Subject: Re: Problems when copy data from dump file
In-Reply-To: <2A250781A3744D48B1529FCC3C2EB8ABCB48FF@comserver.benders.se>
* 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