R dplyr: function db_list_table is returning an error using PostgreSQL

Question

I created a connection with:

con <- src_postgres(dbname = 'mydb',
                host = 'localhost',
                port = 5432,
                user = 'postgres',
                password = 'postgres')

However, functions like db_list_tables(con) are not working: no applicable method for db_list_tables applied to an object of class "c('src_postgres', 'src_sql', 'src')"

but I can get the tables list using SQL:

print(tbl(con, sql("SELECT table_schema,table_name 
FROM information_schema.tables
ORDER BY table_schema,table_name")), n=400)

Also in the second case how to get the number of row in the tbl? It displays ?? X2, and trying to set print n=Inf or options(tibble.print_max = Inf) is not working:

Source:   query [?? x 2]
Database: postgres 9.4.12 [postgres@localhost:5432/mydb]
Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : ERROR:  column "inf" does not exist
LINE 5: LIMIT Inf
              ^


devtools::session_info()
Session info -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 setting  value                       
 version  R version 3.3.3 (2017-03-06)
 system   x86_64, linux-gnu           
 ui       RStudio (1.0.136)           
 language (EN)                        
 collate  en_US.UTF-8                 
 tz       America/Los_Angeles         
 date     2017-06-01                  

Packages -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 package     * version date       source        
 assertthat    0.2.0   2017-04-11 CRAN (R 3.3.3)
 DBI         * 0.6-1   2017-04-01 CRAN (R 3.3.3)
 devtools      1.12.0  2016-06-24 CRAN (R 3.3.1)
 digest        0.6.12  2017-01-27 CRAN (R 3.3.2)
 dplyr       * 0.5.0   2016-06-24 CRAN (R 3.3.1)
 magrittr      1.5     2014-11-22 CRAN (R 3.1.1)
 memoise       1.1.0   2017-04-21 CRAN (R 3.3.3)
 R6            2.2.0   2016-10-05 CRAN (R 3.3.1)
 Rcpp          0.12.11 2017-05-22 CRAN (R 3.3.3)
 RPostgreSQL * 0.4-1   2016-05-08 CRAN (R 3.3.3)
 rstudioapi    0.6     2016-06-27 CRAN (R 3.3.1)
 tibble        1.3.3   2017-05-28 CRAN (R 3.3.3)
 withr         1.0.2   2016-06-20 CRAN (R 3.3.1)

Je Hsers · Accepted Answer · 2017-06-18 11:46:34Z

1

You should try src_tbls(con) to list the tables in the database.

To get the rows of the table your should try tbl(con, 'tbl_name') %>% tally.

dplyr returns only the first 10 rows and does not calculate the actual number of the rows, that why ?? is used in this case.

edited Jun 18, 2017 at 11:46

answered Jun 1, 2017 at 21:50

Je Hsers

1861 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1946989 Over a year ago

there is a missing 's' at the end of src_tbl function name, I can't edit your answer as it is only one character :)

Collectives™ on Stack Overflow

R dplyr: function db_list_table is returning an error using PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related