Message-ID: <12e2ffff149842d8762774e8ef307743cdb96736.camel@tpg.com.au>
Subject: Multiple schemas
From: rob stone <floriparob@tpg.com.au>
To: pgsql-general@postgresql.org
Date: Thu, 24 Sep 2026 18:24:58 +1000
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2-10 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/12e2ffff149842d8762774e8ef307743cdb96736.camel%40tpg.com.au>
Precedence: bulk

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      =20
--------+-------------------
 bsmdl  | teamone
 foots  | teamone
 public | pg_database_owner
(3 rows)

show search_path;
  search_path  =20
----------------
 "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 =3D 'system_defaults';
 schemaname |    tablename    | tableowner |  tablespace  | hasindexes
| hasrules | hastriggers | rowsecurity=20
------------+-----------------+------------+--------------+------------
+----------+-------------+-------------
 bsmdl      | system_defaults | teamone    | basemodldata | f        =20
| 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
=20