Postgres Pro
Downloads
Home   >   mailing lists

Thread: Problem with tables and columns names

Problem with tables and columns names

From
Gabriel Filipiak
Date:
Hi,<br /><br />so I am working on PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro
4.5.2-8ubuntu4)4.5.2, 64-bit.<br /><br />It seems that i can't give a table name for example testTable it has to be
test_table,because I can't access it via psql is that right or am I doing something wrong? The same thing for columns.
Cananyone give me a hint about that?<br /><br />What is really confusing is that I can access those tables and columns
viaSQLAlchemy.<br /><br />Thx in advance<br />best regards<br />Gabe<br />

Re: Problem with tables and columns names

From
Andreas Kretschmer
Date:
Gabriel Filipiak <gabriel.filipiak@gmail.com> wrote:

> Hi,
> 
> so I am working on PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by
> gcc (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 64-bit.
> 
> It seems that i can't give a table name for example testTable it has to be
> test_table, because I can't access it via psql is that right or am I doing
> something wrong? The same thing for columns. Can anyone give me a hint about
> that?

Please consider that "testTable" isn't the same as "testtable" or
"TestTable". If you create a table called "testTable" you have to use
this name everytime. Within psql you have to quote, for instance select
* from "testTable" will work, select from testTable doesn't.


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: Problem with tables and columns names

From
Samuel Gendler
Date:


On Mon, Dec 19, 2011 at 12:16 AM, Gabriel Filipiak <gabriel.filipiak@gmail.com> wrote:
Hi,

so I am working on PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 64-bit.

It seems that i can't give a table name for example testTable it has to be test_table, because I can't access it via psql is that right or am I doing something wrong? The same thing for columns. Can anyone give me a hint about that?

What is really confusing is that I can access those tables and columns via SQLAlchemy.

Are you creating your table within a schema that is not in your search_path?  Perhaps SQLAlchemy is providing the fully qualified name but your query in psql is not?  The other option is case-sensitivity

table and column names should not be case sensitive unless you quote the names in the create statement, in which case, you must always quote them and capitalize them in the same way.

create table myschema.testtable (column1 int);  -- no quotes means case-insensitive table and column name

should work with a query like this:

select * from myschema.TeStTaBlE

but 

create table "mySchema"."TestTable" ("Column1" int) -- quotes forces case sensitivity

will only work with queries like this:

select "Column1" from "mySchema"."TestTable"

but won't work with a query like this:

select Column1 from mySchema.TestTable 

without the quotes, postgres won't recognize table and column names that were quoted at creation, even though they are capitalized in the same way.