RPostgres: No applicable method for 'st_as_sfc' applied to an object of class “pq_geometry”

Question

Setup

I am using sf::st_write() to load data into a PostGIS database. In order to do this, I first had to convert the geometry column of my dataframe from a character to a sfc vector.

df$geom <- st_as_sfc(df$geom)

Then, the entire df was converted into a sf object.

df <- st_as_sf(df)

Once I have an sf object, I then write my data to a PostGIS database.

# db connection object
con <- dbConnect(drv = RPostgres::Postgres()
    ,dbname = db
    ,host = host
    ,port = port
    ,password = pw
    ,user = user
    )

# write sf dataframe to db
st_write(dsn = con
    ,obj = df
    ,table = 'table_name'
    ,query = 'INSERT INTO table_name ON CONFLICT DO NOTHING;'
    ,layer_options = 'OVERWRITE=true'
    ,drop_table = FALSE
    ,try_drop = FALSE
    ,debug = TRUE
    ,append = TRUE
    )

Issue

The above code works fine, but problems arise once I use the st_read() function to pull data from my database.

df1 <- st_read(dsn = con
    ,geom_column = 'geom' # name of my geometry column
    ,table = 'table_name'
    ,query = 'SELECT * FROM table_name;'
    )


Error in st_sf(x, ..., agr = agr, sf_column_name = sf_column_name) : 
  no simple features geometry column present

I tried calling dbReadTable() instead, but ran into related issues. I can read in the data, but I am unable to convert from a pq_geometry class to sfc.

df1 <- dbReadTable(conn = con
    ,name = 'table_name'
    )

df1$geom <- st_as_sfc(df1$geom)


Error in UseMethod("st_as_sfc") : 
  no applicable method for 'st_as_sfc' applied to an object of class "pq_geometry"

Answer 1

Try use st_read. I was trying to do the same thing and st_read works for me.