On Jul 23, 2004, at 5:15 PM, Christopher Browne wrote:

> If you want _all_ the tables in a particular namespace, then you might
> try something like:
>
>  select table_schema || '.' || table_name
>  from information_schema.tables
>  where table_schema in ('app1', 'app2', 'app3') and
>        table_type = 'BASE TABLE';
>
> That grabs the fully-qualified table names for all tables in
> namespaces app1, app2, and app3.  It's easy to then cut'n'paste that
> into a configuration file.

So based on the three primary lists for replication -- sequences, 
constrained tables, and unconstrained tables -- I was considering the 
following queries:

1. Sequences

SELECT c.relname as "Name"
FROM pg_catalog.pg_class c
      LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'S'
       AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
       AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1;

2. Constrained Tables

SELECT c.relname AS "Name"
FROM pg_catalog.pg_class c
      LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
       AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
       AND pg_catalog.pg_table_is_visible(c.oid)
       AND EXISTS (
             SELECT 1
             FROM pg_catalog.pg_class c2, pg_catalog.pg_index i
             WHERE c.oid = i.indrelid
             AND i.indexrelid = c2.oid
             AND i.indisunique = 't'
       )
ORDER BY 1;

3. Unconstrained Tables

SELECT c.relname AS "Name"
FROM pg_catalog.pg_class c
      LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
       AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
       AND pg_catalog.pg_table_is_visible(c.oid)
       AND NOT EXISTS (
             SELECT 1
             FROM pg_catalog.pg_class c2, pg_catalog.pg_index i
             WHERE c.oid = i.indrelid
             AND i.indexrelid = c2.oid
             AND i.indisunique = 't'
       )
ORDER BY 1;

These queries are based on knowledge gleaned from running psql -E and 
general knowledge of the relationships between tables and indexes. 
What's the difference between using the schema and using the catalog? 
Can the schema actually relate to indexes for determining tables that 
don't have unique constraints?

Anyway, I figured I'd post these queries for use (and improvement) by 
the community.

-tfo