Probably a better implementation and one that is closer to the current one:

SELECT c.relname AS name
FROM   pg_class AS c 
  JOIN pg_namespace AS n 
  ON   c.relnamespace = n.oid
WHERE  (n.nspname = ANY (current_schemas(true)))
  AND  (n.nspname <> 'pg_catalog')
  AND  (relkind IN ('r', 'p', 'f', 'v', 'm'))
  AND NOT relispartition
ORDER BY name

This also includes foreign and partitioned tables like INFORMATION_SCHEMA.tables (pg_class documentation).

AND NOT relispartition omits the partitions of a parrtitioned table. Tables with many partitions would otherwise clutter the list and I don't see a use-case for accessing the partitions directly from R. I might be missing something, though.

Thanks. This sounds like a useful extension. Would you like to submit a pull request?

I submitted a PR for dbListTables() (#261), but now realise that this probably should extend to dbExistsTable() and dbListFields() as well?! Update: And also dbListObjects().

I assume that find_table(), which currently uses INFORMATION_SCHEMA, would have to be adjusted, but I have not fully grasped how that function works, yet.

Thanks for the PR. Yes, we should support all methods.

find_table() composes a query against INFORMATION_SCHEMA. We need to determine where and how materialized views are stored in INFORMATION_SCHEMA, and adjust accordingly.

If you prefer staying in line with your pull request and use Postgres-specific internal views, we need to rewrite so that all use cases of find_table() are covered.

Comment by @jaredlander: https://twitter.com/jaredlander/status/1410673131686084614.

Ancient dplyr issue: tidyverse/dplyr#1007.