Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test1_local as select 'hello';

SELECT 1

--this is all just to create a similar table on another db on db<>fiddle
create extension dblink;
select dblink_connect('db_2_dblink', 
                      'dbname=template1');

select dblink_exec('db_2_dblink',
$long_literal$ 
create table test2_db_2 as select 'hi';
$long_literal$);

select dblink_disconnect('db_2_dblink');

CREATE EXTENSION

dblink_connect

OK

SELECT 1

dblink_exec

SELECT 1

SELECT 1

dblink_disconnect

OK

SELECT 1

select string_agg(column_name,' text, ') from information_schema.columns 
  where table_schema='information_schema' and table_name='tables';

string_agg

table_catalog text, table_schema text, table_name text, table_type text, self_referencing_column_name text, reference_generation text, user_defined_type_catalog text, user_defined_type_schema text, user_defined_type_name text, is_insertable_into text, is_typed text, commit_action

SELECT 1

create extension postgres_fdw;
create schema if not exists dev_information_schema;

CREATE SERVER foreign_server
        FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (dbname 'template1');
CREATE USER MAPPING FOR current_user
        SERVER foreign_server
        OPTIONS (user 'postgres');
IMPORT FOREIGN SCHEMA information_schema 
   FROM SERVER foreign_server 
   INTO dev_information_schema;

CREATE EXTENSION

CREATE SCHEMA

CREATE SERVER

CREATE USER MAPPING

IMPORT FOREIGN SCHEMA

select * from dev_information_schema.tables where table_name='test2_db_2';
select * from information_schema.tables where table_name='test2_db_2';

table_catalog
table_schema
table_name
table_type
self_referencing_column_name
reference_generation
user_defined_type_catalog
user_defined_type_schema
user_defined_type_name
is_insertable_into
is_typed
commit_action

template1
public
test2_db_2
BASE TABLE
null
null
null
null
null
YES
NO
null

SELECT 1

table_catalog
table_schema
table_name
table_type
self_referencing_column_name
reference_generation
user_defined_type_catalog
user_defined_type_schema
user_defined_type_name
is_insertable_into
is_typed
commit_action

SELECT 0

refresh materialized view mv_pg_all_tables;

select * from mv_pg_all_tables where table_schema='public' order by random();

ERROR:  relation "mv_pg_all_tables" does not exist