CODEWITHSUNDEEP
sqlpostgresqldatabase-schema
Jan Rüegg
Jan Rüegg

Reputation: 10045

How to select top level schema in SQL?

I have a table foo on top level and a table foo in schema bar.

When using this query:

select  *
from bar.foo
natural join foo
where foo.baz = 'test'

I get the error:

ERROR:  table reference "foo" is ambiguous
LINE 4: where foo.baz = 'test'

I can resolve this by using an alias:

select  *
from bar.foo
natural join foo as f
where f.baz = 'test'

But I was wondering if it is possible to specifiy the top level schema directly (to make clear to SQL that I'm not talking about bar.foo), without using an alias? For example something like this:

select  *
from bar.foo
natural join foo
where .foo.baz = 'test'

or like this:

select  *
from bar.foo
natural join foo
where root_schema.foo.baz = 'test'

Upvotes: 0

Views: 237

Answers (1)

user330315
user330315

Reputation:

There is no such thing as a "top-level" schema. All schemas are on the same level. There is a priority in searching unqualified objects, which is defined by the search_path

The default schema in a default installation is called public where all unqualified objects are created. So in your case you most probably want to use that.

As both tables have the same name, you also need to use a table alias for each of them to be able to distinguish them.

select  *
from bar.foo bf
  join public.foo pf on pf.id = bf.id
where bf.baz = 'test'

Upvotes: 1

Related Questions