Schema in PostgreSQL

When people with an Oracle background who starts learning PostgreSQL DBA there is always a bit of confusion about schemas and users. 

1) In Oracle a schema and a user is a one to one relationship and there is no real distinction between a user and a schema. 

2) In PostgreSQL the situation is different: 

-> All the objects a user is creating are created in a specific schema (or namespace). 

-> Other users may or may not have permissions to work with this objects or even to create new objects in a specific schema. 

-> Compared to Oracle there is one layer more.

The hierarchy in PostgreSQL is this:

1 2 3 4 5 6 7 8 9

|-------------------------------------------|---| | PostgreSQL instance                       |   | |-------------------------------------------| U | |     Database 1      |     Database 2      | S | |---------------------|---------------------| E | | Schema 1 | Schema 2 | Schema 1 | Schema 2 | R | |----------|----------|----------|----------| S | | t1,t2,t3 | t1,t2,t3 | t1,t2,t3 | t1,t2,t3 |   | -------------------------------------------------

What this little ASCII image shall tell you: Users (and roles) in PostgreSQL are global objects and are not defined in a database but on the instance level. 

Schemas are created by users in a specific database and contain database objects. Where a lot of people get confused with is this:

edbstore=# create schema app1;

CREATE SCHEMA
edbstore=#
edbstore=# create table app1.emp(id integer, name varchar(30));
CREATE TABLE
edbstore=#
edbstore=#
edbstore=# select * from app1.emp;
 id | name
----+------
(0 rows)

edbstore=# insert into app1.emp values(1,'Ramesh');
INSERT 0 1
edbstore=#

edbstore=# \dn
  List of schemas
  Name  |  Owner
--------+----------
 app1   | postgres
 public | postgres

(2 rows)