CODEWITHSUNDEEP
sqlpostgresql
Achim
Achim

Reputation: 15702

Postgresql: table name / schema confusion

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!?

Upvotes: 13

Views: 8885

Answers (3)

Maxem
Maxem

Reputation: 2684

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.

Upvotes: 11

Frank Heikens
Frank Heikens

Reputation: 127086

This is normal, object names like tables and columns are case insensitive, unless you put them between double quotes ". This is standard SQL behaviour.

Upvotes: 6

wildplasser
wildplasser

Reputation: 44240

Don't trust on mixed case. Best for your sanity is never to use mixed case. The public schema is (mostly) the default schema, and can be omitted.

Upvotes: 5

Related Questions