Multiple schemas
Hello,
I've been using a single schema in a database for years and I decided
to try using multiple schemas in the same database.
So, I set up a test database and followed the same procedure that I
have used in the past but this time specifying multiple schemas.
O/S
Linux 7.2.6+deb14-amd64 #1 SMP PREEMPT_DYNAMIC Debian 7.2.6-1 (2026-09-
16) x86_64 GNU/Linux
Postgres
psql (18.6 (Debian 18.6-3))
\dn
List of schemas
Name | Owner
--------+-------------------
bsmdl | teamone
foots | teamone
public | pg_database_owner
(3 rows)
show search_path;
search_path
----------------
"bsmdl, foots"
(1 row)
A \dn runs a query against the catalogue whereas "show search_path"
displays what was obtained from the connection.
I ran a create table script where all tables were fully qualified
schema.table_name and it completed without any errors.
Then I ran:-
\d system_defaults (one of the newly created tables)
and this was the result:-
Did not find any relation named "system_defaults".
select * from pg_tables where tablename = 'system_defaults';
schemaname | tablename | tableowner | tablespace | hasindexes
| hasrules | hastriggers | rowsecurity
------------+-----------------+------------+--------------+------------
+----------+-------------+-------------
bsmdl | system_defaults | teamone | basemodldata | f
| f | t | f
(1 row)
I don't know why "hastriggers IS TRUE" as there are none.
According to 5.10.3 in the doco:-
"The first schema named in the search path is called the current
schema. Aside from being the first schema searched, it is also the
schema in which new tables will be created if the CREATE TABLE command
does not specify a schema name."
When you run a \d table_name the first query that it runs to obtain
pg_catalog.pg_class c.oid is the same if the database contains a single
schema or multiple schemas. So in my test it should have looked first
in schema bsmdl and if it didn't find the table it should have looked
in the next schema in the search_path, and so on.
According to the doco you can have tables with the same name appearing
in multiple schemas, and it is the sequence in which schemas are
defined in the search path which determines which one is accessed
unless you specify the schema name.
So, I'm doing something wrong with this set-up.
If anybody else is using multiple schemas could you advise what you did
that was different to having just a single schema.
TIA,
Rob
On 24 Sep 2026, at 10:24, rob stone <floriparob@tpg.com.au> wrote:
Hello,
I've been using a single schema in a database for years and I decided
to try using multiple schemas in the same database.So, I set up a test database and followed the same procedure that I
have used in the past but this time specifying multiple schemas.
…
show search_path;
search_path
----------------
"bsmdl, foots"
(1 row)
I think there’s your problem. You have schemas named “bsmdl” and “foots”, not a schema named "bsmdl, foots”.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
On Sep 24, 2026, at 10:25 AM, rob stone <floriparob@tpg.com.au> wrote:
Hello,
I've been using a single schema in a database for years and I decided
to try using multiple schemas in the same database.So, I set up a test database and followed the same procedure that I
have used in the past but this time specifying multiple schemas.O/S
Linux 7.2.6+deb14-amd64 #1 SMP PREEMPT_DYNAMIC Debian 7.2.6-1 (2026-09-
16) x86_64 GNU/LinuxPostgres
psql (18.6 (Debian 18.6-3))\dn
List of schemas
Name | Owner
--------+-------------------
bsmdl | teamone
foots | teamone
public | pg_database_owner
(3 rows)show search_path;
search_path
----------------
"bsmdl, foots"
(1 row)A \dn runs a query against the catalogue whereas "show search_path"
displays what was obtained from the connection.I ran a create table script where all tables were fully qualified
schema.table_name and it completed without any errors.Then I ran:-
\d system_defaults (one of the newly created tables)
and this was the result:-
Did not find any relation named "system_defaults".
\d bsmdl.system_defaults
Or
\dt bsmdl.*
Rob Sargent <robjsargent@gmail.com> writes:
On Sep 24, 2026, at 10:25 AM, rob stone <floriparob@tpg.com.au> wrote:
Then I ran:-
\d system_defaults (one of the newly created tables)
and this was the result:-
Did not find any relation named "system_defaults".
\d bsmdl.system_defaults
Or
\dt bsmdl.*
Or perhaps more usefully,
\d *.system_defaults
since this table is evidently not in Rob's search path.
regards, tom lane
Hi Rob Stone
I can not create the behavior you are describing.
I suspect the
set search_path to bsmdl,foots,public
has an issue.
I abuse the search path to override PostgreSQL internal functions.
example search_paths I have created.
search_path to overload_functions,pg_catalog,ar,ap,manufacturing,public,
Keep i mind the session temp space can not be overridden it will always be
searched first.
Below is example
test=# CREATE SCHEMA _1;
CREATE SCHEMA
test=# CREATE SCHEMA _2;
CREATE SCHEMA
test=# create table _1.ff(id int);
CREATE TABLE
test=# create table _2.ff(id int);
CREATE TABLE
test=# set search_path to _1,_2;
SET
test=# insert into ff values (1);
INSERT 0 1
test=# select * from _1.ff ;
id
----
1
(1 row)
test=# \d ff
Table "_1.ff"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
test=# set search_path to _2, _1;
SET
test=# \d ff
Table "_2.ff"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
test=# create table _1.aa();
CREATE TABLE
test=# \d aa
Table "_1.aa"
Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
On 9/24/26 2:19 AM, Alban Hertroys wrote:
On 24 Sep 2026, at 10:24, rob stone <floriparob@tpg.com.au> wrote:
Hello,
I've been using a single schema in a database for years and I decided
to try using multiple schemas in the same database.So, I set up a test database and followed the same procedure that I
have used in the past but this time specifying multiple schemas.…
show search_path;
search_path
----------------
"bsmdl, foots"
(1 row)I think there’s your problem. You have schemas named “bsmdl” and “foots”, not a schema named "bsmdl, foots”.
In other words you did:
SET search_path TO 'bsmdl, foots'
and got
SHOW search_path ;
search_path
----------------
"bsmdl, foots"
Instead you should do:
SET search_path TO bsmdl, foots;
to get:
SHOW search_path ;
search_path
--------------
bsmdl, foots
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
--
Adrian Klaver
adrian.klaver@aklaver.com
On Thu, 2026-09-24 at 08:01 -0700, Adrian Klaver wrote:
On 9/24/26 2:19 AM, Alban Hertroys wrote:
On 24 Sep 2026, at 10:24, rob stone <floriparob@tpg.com.au>
wrote:Hello,
I've been using a single schema in a database for years and I
decided
to try using multiple schemas in the same database.So, I set up a test database and followed the same procedure that
I
have used in the past but this time specifying multiple schemas.…
show search_path;
search_path
----------------
"bsmdl, foots"
(1 row)I think there’s your problem. You have schemas named “bsmdl” and
“foots”, not a schema named "bsmdl, foots”.In other words you did:
SET search_path TO 'bsmdl, foots'
and got
SHOW search_path ;
search_path
----------------
"bsmdl, foots"Instead you should do:
SET search_path TO bsmdl, foots;
to get:
SHOW search_path ;
search_path
--------------
bsmdl, foots
Thanks Adrian.
That was the problem -- putting single quotes around the schema names.
Now it is working as intended.
Cheers,
Rob