Create multiple output tables from one query
I'm an inexperienced Postgres user and need help if someone can suggest it.


A few things, this is a bad idea, you probably want Table Partitioning because essentially you're creating partitions of another table.

If you don't want to partition a table, you probably want a MATERIALIZED VIEW which sits as a table on the disk, but it can be refreshed from the query which is was created from with a simple command REFRESH MATERIALIZED VIEW.

What I think you want is something like this,

SELECT FORMAT ($$
  CREATE MATERIALIZED VIEW %I.%I AS
    SELECT *
    FROM my_countries
    WHERE country = %L;
  $$,
  'myschema',
  'test_' || country,
  country
)
FROM (
  SELECT DISTINCT county
  FROM my_counties
) AS t(country);


Run that in psql and then run \gexec, or loop through that result set and run EXECUTE in plpgsql.