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
_______________________________________________
Gnumed-devel mailing list
Gnumed-devel@gnu....
https://lists.gnu.org/mailman/listinfo/gnumed-devel