Re: Problem with tables and columns names

On Mon, Dec 19, 2011 at 12:16 AM, Gabriel Filipiak <
gabriel(dot)filipiak(at)gmail(dot)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.