Database, schemas, and the system catalog

Create a database:

α=> CREATE DATABASE data_logical;

CREATE DATABASE

α=> \c data_logical

You are now connected to database "data_logical" as user "student".

Schemas:

α=> CREATE SCHEMA student;

CREATE SCHEMA

α=> CREATE SCHEMA app;

CREATE SCHEMA

Tables for the student schema:

α=> CREATE TABLE a(s text);

CREATE TABLE

α=> INSERT INTO a VALUES ('student');

INSERT 0 1

α=> CREATE TABLE b(s text);

CREATE TABLE

α=> INSERT INTO b VALUES ('student');

INSERT 0 1

Table for the app schema:

α=> CREATE TABLE app.a(s text);

CREATE TABLE

α=> INSERT INTO app.a VALUES ('app');

INSERT 0 1

α=> CREATE TABLE app.c(s text);

CREATE TABLE

α=> INSERT INTO app.c VALUES ('app');

INSERT 0 1

Description of schemas and tables:

α=> \dn

  List of schemas
  Name   |  Owner   
---------+----------
 app     | student
 public  | postgres
 student | student
(3 rows)

α=> \dt student.*

        List of relations
 Schema  | Name | Type  |  Owner  
---------+------+-------+---------
 student | a    | table | student
 student | b    | table | student
(2 rows)

α=> \dt app.*

        List of relations
 Schema | Name | Type  |  Owner  
--------+------+-------+---------
 app    | a    | table | student
 app    | c    | table | student
(2 rows)

The current setting of the search path allows to access tables from the student schema only:

α=> SELECT * FROM a;

    s    
---------
 student
(1 row)

α=> SELECT * FROM b;

    s    
---------
 student
(1 row)

α=> SELECT * FROM c;

ERROR:  relation "c" does not exist
LINE 1: SELECT * FROM c;
                      ^

Change the search path.

α=> ALTER DATABASE data_logical SET search_path = "$user",app,public;

ALTER DATABASE

α=> \c

You are now connected to database "data_logical" as user "student".

α=> SHOW search_path;

     search_path      
----------------------
 "$user", app, public
(1 row)

Now we can access tables from both schemas, but the student schema takes precedence:

α=> SELECT * FROM a;

    s    
---------
 student
(1 row)

α=> SELECT * FROM b;

    s    
---------
 student
(1 row)

α=> SELECT * FROM c;

  s  
-----
 app
(1 row)