Tables without primary keys

file.sql

SELECT relname FROM pg_class pgc
JOIN pg_namespace pgns ON pgns.oid = pgc.relnamespace
WHERE pgns.nspname = 'public'
AND pgc.relkind = 'r'
AND pgc.oid NOT IN

(SELECT pgc.oid
FROM pg_class pgc
JOIN pg_index pgi ON pgi.indrelid = pgc.oid
JOIN pg_namespace pgns ON pgns.oid = pgc.relnamespace

WHERE pgi.indisprimary = true
AND pgc.relkind = 'r'
AND pgns.nspname = 'public'
);

Tables without a certain column (eg created_at, updated_at):

              select t.table_name
        from information_schema.tables t
        left join (select table_schema, table_name
                  from information_schema.columns
                  where column_name = #{columnName}) c
                            on c.table_name = t.table_name
                            and c.table_schema = t.table_schema
        where c.table_name is null
              and t.table_schema not in ('information_schema', 'pg_catalog')
              and t.table_type = 'BASE TABLE'
              and t.table_name not in %{exceptions}
        order by t.table_schema,
                t.table_name;

Tables without a certain trigger name (eg update_timestamps)

        WITH tables_with_trigger AS (SELECT pgcl.*
            FROM pg_class pgcl
            JOIN pg_namespace pgns on pgns.oid = pgcl.relnamespace
            LEFT JOIN pg_trigger pgtg ON pgtg.tgrelid = pgcl.oid
            LEFT JOIN pg_proc proc ON proc.oid = pgtg.tgfoid

            WHERE pgns.nspname = 'public'
            AND relkind = 'r'
            AND proc.proname = #{functionName}
            )
        SELECT pgcl.relname FROM pg_class pgcl
        JOIN pg_namespace pgns on pgns.oid = pgcl.relnamespace
        LEFT JOIN tables_with_trigger twt ON twt.oid = pgcl.oid
        WHERE twt.oid IS NULL
        AND pgns.nspname = 'public'
        AND pgcl.relkind = 'r'
        AND pgcl.relname NOT IN %{exceptions}
        AND pgcl.relname NOT LIKE '%_history';
        ;