Re: [Gnumed-devel] database replication with bucardo

[Busser, Jim]

On 2012-06-26, at 6:18 AM, Slappinjohn wrote:

> that's the source of my wisdom:
> 
> http://petereisentraut.blogspot.de/2009/07/how-to-find-all-tables-without-primary.html

That thread offered some alternatives... the first listed out my staging 
tables, but also listed out

        ref | loinc_staging
        ref | atc_staging 

it was:

SELECT table_catalog, table_schema, table_name
FROM information_schema.tables
WHERE (table_catalog, table_schema, table_name) NOT IN
(SELECT table_catalog, table_schema, table_name
FROM information_schema.table_constraints
WHERE constraint_type NOT IN ('PRIMARY KEY', 'UNIQUE'))
AND table_schema NOT IN ('information_schema', 'pg_catalog')
AND table_type = 'BASE TABLE';

however one thread poster commented that the above had failed to list out 
tables whose (only) constraint was a foreign key reference. Another that was 
offered was the following which, for me, returned nothing more than what Marc's 
original did:

select c.oid::regclass from pg_class c join pg_namespace n on 
(relnamespace=n.oid) where relkind = 'r' and nspname in ('schema_list') except 
select indrelid::regclass from pg_index where indisunique ;

-- Jim