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.

How about dbGetQuery with these queries for tables and views? — tonytonov
– tonytonov, Commented Mar 13, 2020 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
– user2957945, Commented Mar 13, 2020 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
– moodymudskipper, Commented Mar 15, 2020 at 9:15
I'm a bit late, and there's a good answer there already, which is fine by me. — tonytonov
– tonytonov, Commented Mar 17, 2020 at 17:29

Erwin Brandstetter · Accepted Answer · 2023-11-12 20:32:50Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related