postgres@[local]:5432:postgres]
SQL> create table x(x int);
CREATE TABLE
postgres@[local]:5432:postgres]
SQL> SELECT c.table_schema, c.table_name, c.table_type
FROM information_schema.tables c
WHERE c.table_schema NOT IN('information_schema', 'pg_catalog') AND c.table_type = 'BASE TABLE'
AND NOT EXISTS(SELECT i.tablename
FROM pg_catalog.pg_indexes i
WHERE i.schemaname = c.table_schema
AND i.tablename = c.table_name AND indexdef LIKE '%UNIQUE%')
AND
NOT EXISTS (SELECT cu.table_name
FROM information_schema.key_column_usage cu
WHERE cu.table_schema = c.table_schema AND
cu.table_name = c.table_name)
ORDER BY c.table_schema, c.table_name;
table_schema | table_name | table_type
--------------+------------+------------
public | x | BASE TABLE
(1 row)
postgres@[local]:5432:postgres]
SQL>