Coming from an Oracle background where schemas are roughly equivalent to owners the important differences between users, owners, and schemas / users, can be slightly confusing when dealing with Redshift or PostgreSQL (Redshift was forked from PostgreSQL 8.x) and uses the same concepts as PostgreSQL when it comes to Owners, Users, and Schemas (to my knowledge).
From this point forward in this point, I’ll just refer to Redshift and both Redshift and PostgreSQL will apply.
An owner in Redshift is a user, a user with privileges to own objects. Schemas are “owned” by owners/users. A user can own more than one schema – which is different than Oracle Enterprise in my experience.
A schema in Redshift, is a way to group objects (e.g. tables) in a database. In that regard, schemas are like directories in a file system except, schemas cannot be nested.
An object name can exist in more than one schema. When that occurs two distinct objects exist.
Redshift uses the concept of a “search_path”. Search path is defined in a parameter group – a file/spfile in Oracle speak.
So for example, if an other has two “LineItem” tables, on in the schema Public and the other in the schema “All_Orders” how do we know what is referred to when the owner issues “select order_no from LineItems;”? Which schema is being referenced?
Well you probably guessed right because I gave the answer to that question away before I asked it. The answer is: it depends on what comes first in the search path…
from psql
# to show who you are connected as:
select session_user;
# to show your search path type:
show search_path
By default, a schema named public is created in every new Redshift database.
References:
http://www.lonzodb.com/?p=4112
https://info.crunchydata.com/blog/demystifying-schemas-search_path-through-examples