5

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.

moodymudskipper
  • 46,417
  • 11
  • 121
  • 167
  • 2
    How about `dbGetQuery` with these queries for [tables](https://stackoverflow.com/questions/15644152/list-tables-in-a-postgresql-schema/15644435#15644435) and [views](https://dba.stackexchange.com/a/23837)? – tonytonov Mar 13 '20 at 14:14
  • ... so from Tony's links: `dbGetQuery(con, "SELECT * FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE'")` – user2957945 Mar 13 '20 at 14:51
  • This seems like a neat way to do it. If you want to add it as an answer I'll accept it. Do you know what are the different possible values of table_type? – moodymudskipper Mar 15 '20 at 09:15
  • 1
    I'm a bit late, and there's a good answer there already, which is fine by me. – tonytonov Mar 17 '20 at 17:29

1 Answers1

2

I suggest to 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:

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:

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228