R connect to postgres return false in dbExistsTable, but it is wrong

Question

I try connect to my table in postgres. Here screen of my db.

So i need dbo.social and table profiles. So to do this, i try

library(RPostgres)
library(DBI)

pw<- {
  "1234"
}

con <- dbConnect(RPostgres::Postgres()
                 , host='1.2.3.4.'
                 , port='5432'
                 , dbname='social'
                 , user='analyst'
                 , password=pw)


#rm(pw) # removes the password

dbExistsTable(con, "social")

and the resutl

[1] FALSE

why false, i marked as black line that it exists.

so

> dbListTables(con)
 [1] "cache"                         "available_permission_modules" 
 [3] "available_permissions"         "counters"                     
 [5] "permissions"                   "group_direction"              
 [7] "jobs"                          "oauth_auth_codes"             
 [9] "oauth_access_tokens"           "oauth_refresh_tokens"         
[11] "permissions_rules"             "permissions_rule_user"        
[13] "oauth_clients"                 "oauth_personal_access_clients"
[15] "users"                         "directions"                   
[17] "themes"                        "profiles_without_rating"      
[19] "failed_jobs"                   "model_has_permissions"        
[21] "regions_oktmo"                 "ch_profiles"                  
[23] "user_reports"                  "roles"                        
[25] "migrations"                    "crime_minor"                  
[27] "governments"                   "mapping_candidates"           
[29] "password_resets"               "responsible"                  
[31] "spatial_ref_sys"               "model_has_roles"              
[33] "population"                    "role_has_permissions"         
[35] "geo_point"                     "geo_polygon"                  
[37] "crime_all"                     "geography_columns"            
[39] "geometry_columns"              "raster_columns"               
[41] "raster_overviews"              "schools"                      
[43] "post_grabber"

Why in list, there is no social.profiles_bstms?

How can i get social.profiles_bstms tables to work with it.

If I'm not mistaken, "social" is a schema and not a table in your picture? — AEF, Sep 10 '20 at 11:44
@AEF, yes, it is scheme, but in this,there is db social. How work with it?" — psysky, Sep 10 '20 at 12:21
You need to specify the full name of the table you are interested in unless it is in schema `public`. So `dbExistsTable(con, "social.analysis")` should return `TRUE`. There is no table named `social` in your database as far as I can see. `dbListTables()` will list the tables in schema `public`. — AEF, Sep 11 '20 at 14:05

score 3 · Accepted Answer · answered Sep 10 '20 at 13:50

As mentioned on the direct replies by AEF and Data Miner, what you are trying to do is verifying the existence of table profile_bstms which is inside the schema social (and this schema is inside a database with the same name).

Please note that Postgres Schemas [ref.1] does not contain data per se; they are used to organize your tables.

I've never used R before, but I have found some links that can bring some light.

Regarding your question on why dbListTables(con) does not show those tables: I would say that the list you are observing is from tables that are present on schema public, am I right?

If that's the case, this is happening because the parameter search_path does not contain the schemas that you want to list, in this example social.

My suggestion would be ask you to try change your search_path parameter. Something like: "$user", public, social. It does not require restart the database.

[1] https://www.postgresql.org/docs/current/ddl-schemas.html

score 0 · Answer 2 · answered Nov 10 '20 at 04:06

0

No need to set search_path dbExistsTable(con, Id(schema = "social", table="profile_bstms"))

You can also use dbListObjects(con) to see all schemas and then dbListObjects(con, Id(schema = "social")) to dig deeper

answered Nov 10 '20 at 04:06

Arthur Yip

5,810
2
31
50

R connect to postgres return false in dbExistsTable, but it is wrong

2 Answers2