Using r sf::st_write to non-public schema in PostgreSQL

This happens because you are connecting to the db via package RPostgreSQL, but the syntax used for specifying table and schema is that used with connections made with package RPostgres. You can solve this using:

    require(RPostgres)
    conn <- dbConnect(Postgres(), dbname = dbname, host = host, port = port, 
                      user = username, password = password)
    st_write(obj = cycle_hire, dsn = conn, Id(schema="roads_spatial", table = "myCycle"))