Postgresql: table name / schema confusion

Question

I created a table Abc in the public schema of my postgresql database. According to the documentation public should be the default schema. search_path is set to "$user",public as expected. But the following fails:

select * from Abc

and this call fails too:

select * from public.Abc

Both produce an error saying that relation ... does not exist. But this one works fine:

select * from public."Abc"

I'm an experienced T-SQL developer, but new to postgresql. According to the documenation, it should be possible to use "normal" SQL with postgresql. But it does not work in my case. Any hint what I might have messed up!?

Answer 1

Postgresql defaults to lower case characters while being case sensitive with column/table names:

select * from public.Abc 

is actually:

select * from public.abc

That's because your table is called Abc, so it cannot be found.

You can override this lower case behavior via quotation marks, so "Abc" is handled as Abc.