Создаем базу данных:
=> CREATE DATABASE data_logical;
CREATE DATABASE
=> \c data_logical
You are now connected to database "data_logical" as user "student".
Схемы:
=> CREATE SCHEMA student;
CREATE SCHEMA
=> CREATE SCHEMA app;
CREATE SCHEMA
Таблицы для схемы student:
=> 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
Таблицы для схемы app:
=> 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
Описание схем и таблиц:
=> \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)
С текущими настройками пути поиска видны таблицы только схемы student:
=> 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;
^
Изменим путь поиска.
=> 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)
Теперь видны таблицы из обоих схем, но приоритет остается за student:
=> SELECT * FROM a;
s
---------
student
(1 row)
=> SELECT * FROM b;
s
---------
student
(1 row)
=> SELECT * FROM c;
s ----- app (1 row)