Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE SCHEMA master;
CREATE SCHEMA a;
CREATE SCHEMA b;

CREATE TABLE master.product (title text);

CREATE  TABLE a.product (product_id serial PRIMARY KEY, col2 text)
INHERITS (master.product);

CREATE  TABLE b.product (product_id serial PRIMARY KEY, col2 text, col3 text)
INHERITS (master.product);

INSERT INTO master.product(title) VALUES ('master');

INSERT INTO a.product(title, col2) VALUES
  ('a_test', 'foo')
, ('a_test2', 'foo')
, ('a', 'foo')
;

INSERT INTO b.product(title, col2, col3) VALUES
  ('b_test', 'foo', 'bar')
, ('b_test2', 'foo', 'bar')
, ('b', 'foo', 'bar')
;

CREATE SCHEMA

CREATE SCHEMA

CREATE SCHEMA

CREATE TABLE

CREATE TABLE

CREATE TABLE

INSERT 0 1

INSERT 0 3

INSERT 0 3

-- schema is only printed if otherwise ambiguous with current search_path
SET search_path = public, a, b;
SELECT title, tableoid::regclass::text AS source
FROM   master.product
WHERE  title ILIKE '%test%';

SET

title
source

a_test
product

a_test2
product

b_test
b.product

b_test2
b.product

SELECT 4

-- force output of schema with empty search_path
SET search_path = '';
SELECT title, tableoid::regclass::text AS source
FROM   master.product
WHERE  title ILIKE '%test%';

SET

title
source

a_test
a.product

a_test2
a.product

b_test
b.product

b_test2
b.product

SELECT 4

title	source
a_test	a.product
a_test2	a.product
b_test	b.product
b_test2	b.product