How can I list tables but not views from a Postgres DB, using R?

Question

In ?DBI::dbListTables we can read :

This should include views and temporary objects

And indeed it does.

How can I see only tables though, excluding views ?

I'm using the driver RPostgres::Postgres() if it matters.

Answer 1

Use the system catalog view pg_tables for tables:

dbGetQuery(con, "SELECT * FROM pg_tables")

The manual:

The view pg_tables provides access to useful information about each table in the database.

Does not contain views, materialized views or temporary tables, only regular tables (including UNLOGGED tables). See:

• How to check if a table exists in a given schema

You may want to exclude system tables and only retrieve schema and table name:

dbGetQuery(con, "SELECT schemaname, tablename FROM pg_catalog.pg_tables WHERE schemaname !~ '^pg_' AND schemaname <> 'information_schema'")

I added explicit schema-qualification for the catalog table: pg_catalog.pg_tables. Typically not necessary, but to defend against a messed up search_path setting. See:

• How does the search_path influence identifier resolution and the "current schema"

pg_views for views - if you need that:

dbGetQuery(con, "SELECT * FROM pg_views")

The view pg_views provides access to useful information about each view in the database.