pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedMultiple schemas
7+ messages / 6 participants
[nested] [flat]
* Multiple schemas
@ 2026-09-24 08:24 rob stone <floriparob@tpg.com.au>
0 siblings, 2 replies; 7+ messages in thread
From: rob stone @ 2026-09-24 08:24 UTC (permalink / raw)
To: pgsql-general
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Multiple schemas
@ 2026-09-24 09:19 Alban Hertroys <haramrae@gmail.com>
parent: rob stone <floriparob@tpg.com.au>
1 sibling, 1 reply; 7+ messages in thread
From: Alban Hertroys @ 2026-09-24 09:19 UTC (permalink / raw)
To: rob stone <floriparob@tpg.com.au>; +Cc: pgsql-general
> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Multiple schemas
@ 2026-09-24 10:41 Rob Sargent <robjsargent@gmail.com>
parent: rob stone <floriparob@tpg.com.au>
1 sibling, 2 replies; 7+ messages in thread
From: Rob Sargent @ 2026-09-24 10:41 UTC (permalink / raw)
To: rob stone <floriparob@tpg.com.au>; +Cc: pgsql-general
> 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.*
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Multiple schemas
@ 2026-09-24 14:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rob Sargent <robjsargent@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2026-09-24 14:15 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; +Cc: rob stone <floriparob@tpg.com.au>; pgsql-general
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Multiple schemas
@ 2026-09-24 14:17 Justin <zzzzz.graf@gmail.com>
parent: Rob Sargent <robjsargent@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Justin @ 2026-09-24 14:17 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; +Cc: rob stone <floriparob@tpg.com.au>; pgsql-general
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
--------+------+-----------+----------+---------
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Multiple schemas
@ 2026-09-24 15:01 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Alban Hertroys <haramrae@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Adrian Klaver @ 2026-09-24 15:01 UTC (permalink / raw)
To: Alban Hertroys <haramrae@gmail.com>; rob stone <floriparob@tpg.com.au>; +Cc: pgsql-general
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Multiple schemas
@ 2026-09-25 06:48 rob stone <floriparob@tpg.com.au>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 0 replies; 7+ messages in thread
From: rob stone @ 2026-09-25 06:48 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; Alban Hertroys <haramrae@gmail.com>; +Cc: pgsql-general
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2026-09-25 06:48 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-24 08:24 Multiple schemas rob stone <floriparob@tpg.com.au>
2026-09-24 09:19 ` Alban Hertroys <haramrae@gmail.com>
2026-09-24 15:01 ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-09-25 06:48 ` rob stone <floriparob@tpg.com.au>
2026-09-24 10:41 ` Rob Sargent <robjsargent@gmail.com>
2026-09-24 14:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-09-24 14:17 ` Justin <zzzzz.graf@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox