CODEWITHSUNDEEP
rpostgresqlrodbcrpostgresqlrpostgres
psysky
psysky

Reputation: 3195

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

I try connect to my table in postgres. Here screen of my db. enter image description here

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.

Upvotes: 2

Views: 733

Answers (2)

Arthur Yip
Arthur Yip

Reputation: 6230

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

Upvotes: 0

Vinnix
Vinnix

Reputation: 354

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.

  1. https://github.com/r-dbi/RPostgres/issues/160
  2. https://github.com/r-dbi/DBI/issues/277

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

Upvotes: 3

Related Questions