This is a followup question to Writeogr to PostgreSQL/PostGIS database with R

How can I choose the schema where to put the SPDF (or any other spatial object)?

postgis_insert(con, df=catchments, tbl="land_use", geom_name="st_astext")

I can't just type schema1.land_use in the tbl argument and there is no further argument to assign the schema. It seems that I can only put it into the public schema of the DB (which is kind of the default schema in PostgreSQL)

  • It doesn't look like you can directly, as dbConnect does not contain an option for schema. You can always change the database search_path order in Postgres first, and then the table inserted into will be the one that matches the first schema in the search path. ALTER DATABASE foo SET search_path to bar, public, etc.... See postgresql.org/docs/9.1/static/sql-set.html I accept that this is a bit of a hack. – John Powell Aug 26 '16 at 12:53
  • Ok thanks, this is also posible. I finished by adding a line of code in R which changes the schema in the DB dbSendQuery(con, "ALTER TABLE land_use SET SCHEMA spatial;") So, I don't have to change to PostgreSQL – andschar Aug 26 '16 at 13:04
  • I'm glad you found a solution. I think that dbconnect aims to be generic and different databases have different concepts about what a schema is? – John Powell Aug 26 '16 at 13:18
  • I guess so, though I've only worked with PostgreSQL. Maybe they will include this feature in the RPostgreSQL in the future. – andschar Aug 26 '16 at 13:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.