CODEWITHSUNDEEP
rpostgresqldplyr
user1946989
user1946989

Reputation: 387

R dplyr: function db_list_table is returning an error using PostgreSQL

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)

Upvotes: 0

Views: 306

Answers (1)

Je Hsers
Je Hsers

Reputation: 186

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.

Upvotes: 1

Related Questions