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

Glen Selle · Accepted Answer · 2013-12-11 17:23:55Z

11

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.

edited Dec 11, 2013 at 17:23

Glen Selle

3,9564 gold badges39 silver badges59 bronze badges

answered Aug 31, 2011 at 20:20

Maxem

2,6741 gold badge20 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kuberchaun Over a year ago

A good rule of thumb that I live by in the PG world is use underscores instead of camel case like you might do in Microsoft SQL Server. So do fast_table instead of "FastTable". I find extra quotes at the minimum to be annoying. Though others might find underscores to be the same. But I come from and Oracle background so I'm use to it.

Frank Heikens · Accepted Answer · 2011-08-31 20:15:42Z

6

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

answered Aug 31, 2011 at 20:15

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

2 Comments

Grzegorz Szpetkowski Over a year ago

Just for completeness. Am I right that SQL standard requires that names without quotes should act as same as upper-cased quoted equivalent ? In other words Abc ≡ "ABC" ?

mu is too short Over a year ago

@Grzegorz: AFAIK, table and column names are supposed to be normalized to upper case unless quoted; PostgreSQL, OTOH, converts the names to lower case instead.

wildplasser · Accepted Answer · 2011-08-31 20:12:46Z

5

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.

answered Aug 31, 2011 at 20:12

wildplasser

44.4k9 gold badges72 silver badges116 bronze badges

1 Comment

wildplasser Over a year ago

The reason for this advice is that you quickly run into problems when your model gets exported to another installation, with other settings for case(in)sensitivity.

Collectives™ on Stack Overflow

Postgresql: table name / schema confusion

3 Answers 3

1 Comment

2 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

1 Comment

Linked

Related