Got it, the issue you're hitting is that both information_schema.tables and pg_tables are database-specific views—they only show data for the database you're currently connected to. PostgreSQL doesn't have a built-in way to query tables across all databases directly since each database is isolated from others. But there are two solid workarounds to get the full list of tables with their schemas across all your databases:

Method 1: Use a shell script with psql #

This approach is great if you prefer working from the command line and don't want to set up any database extensions. It loops through all non-template databases, connects to each one, and pulls the table/schema info:

# Get all non-template databases, then loop through each
psql -t -c "SELECT datname FROM pg_database WHERE datistemplate = false;" | grep -v '^$' | while read db_name; do
  echo "--- Tables in database: $db_name ---"
  # Query tables with their schema, exclude system schemas
  psql -d "$db_name" -t -c "SELECT CONCAT(table_schema, '.', table_name) AS full_table_name FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'pg_catalog');"
done

Notes:

• The grep -v '^$' cleans up empty lines from the database list.
• Remove the WHERE table_schema NOT IN (...) clause if you want to include system tables like those in pg_catalog.

Method 2: Use a PL/pgSQL function with dblink #

If you want to run this directly within PostgreSQL (e.g., from a client like pgAdmin), you can use the dblink extension to connect to other databases and aggregate the results. Here's how:

1. First, enable the dblink extension (you only need to do this once, usually in the postgres database):

CREATE EXTENSION IF NOT EXISTS dblink;

2. Create a function that loops through all databases and fetches the table info:

CREATE OR REPLACE FUNCTION get_all_tables_with_schemas()
RETURNS TABLE(database_name text, full_table_name text) AS $$
DECLARE
  db_rec record;
BEGIN
  -- Loop through all non-template databases
  FOR db_rec IN SELECT datname FROM pg_database WHERE datistemplate = false LOOP
    RETURN QUERY
    SELECT 
      db_rec.datname::text,
      table_info.full_table_name::text
    FROM dblink(
      'dbname=' || db_rec.datname, -- Connect to the target database
      'SELECT table_schema || ''.'' || table_name AS full_table_name FROM information_schema.tables WHERE table_schema NOT IN (''information_schema'', ''pg_catalog'')'
    ) AS table_info(full_table_name text);
  END LOOP;
END;
$$ LANGUAGE plpgsql;

3. Call the function to get your results:

SELECT * FROM get_all_tables_with_schemas();

Notes:

• The user running this function needs CONNECT privileges on all databases you want to query.
• If you get connection errors, double-check that the user has access to each database, and that dblink is properly installed.

Either method will give you the full list of tables with their schema names across all your PostgreSQL databases.

内容的提问来源于stack exchange，提问作者Jayesh