=> CREATE DATABASE data_databases;
CREATE DATABASE
=> \c data_databases
You are now connected to database "data_databases" as user "postgres".
=> SELECT pg_size_pretty(pg_database_size('data_databases'));
pg_size_pretty ---------------- 7038 kB (1 row)
=> SELECT pg_database_size('data_databases') AS oldsize \gset
=> CREATE SCHEMA app;
CREATE SCHEMA
=> CREATE SCHEMA postgres;
CREATE SCHEMA
Таблицы для схемы postgres:
=> CREATE TABLE a(s text);
CREATE TABLE
=> INSERT INTO a VALUES ('postgres');
INSERT 0 1
=> CREATE TABLE b(s text);
CREATE TABLE
=> INSERT INTO b VALUES ('postgres');
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
=> SELECT pg_size_pretty(pg_database_size('data_databases'));
pg_size_pretty ---------------- 7110 kB (1 row)
=> SELECT pg_database_size('data_databases') AS newsize \gset
Размер изменился на:
=> SELECT pg_size_pretty(:newsize::bigint - :oldsize::bigint);
pg_size_pretty ---------------- 72 kB (1 row)
С текущими настройками пути поиска видны таблицы только схемы postgres:
=> SELECT * FROM a;
s
----------
postgres
(1 row)
=> SELECT * FROM b;
s
----------
postgres
(1 row)
=> SELECT * FROM c;
ERROR: relation "c" does not exist
LINE 1: SELECT * FROM c;
^
Изменим путь поиска.
=> ALTER DATABASE data_databases SET search_path = "$user",app,public;
ALTER DATABASE
=> \c
You are now connected to database "data_databases" as user "postgres".
=> SHOW search_path;
search_path
----------------------
"$user", app, public
(1 row)
Теперь видны таблицы из обеих схем, но приоритет остается за postgres:
=> SELECT * FROM a;
s
----------
postgres
(1 row)
=> SELECT * FROM b;
s
----------
postgres
(1 row)
=> SELECT * FROM c;
s ----- app (1 row)