Multiple schemas

Started by rob stoneabout 2 months ago7 messagesgeneral
Jump to latest
#1rob stone
floriparob@tpg.com.au

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

#2Alban Hertroys
haramrae@gmail.com
In reply to: rob stone (#1)
Re: Multiple schemas

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.

#3Rob Sargent
robjsargent@gmail.com
In reply to: rob stone (#1)
Re: Multiple schemas

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/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".

\d bsmdl.system_defaults
Or
\dt bsmdl.*

#4Tom Lane
tgl@sss.pgh.pa.us
In reply to: Rob Sargent (#3)
Re: Multiple schemas

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

#5Justin
zzzzz.graf@gmail.com
In reply to: Rob Sargent (#3)
Re: Multiple schemas

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
--------+------+-----------+----------+---------

#6Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Alban Hertroys (#2)
Re: Multiple schemas

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

#7rob stone
floriparob@tpg.com.au
In reply to: Adrian Klaver (#6)
Re: Multiple schemas

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