To retrieve results a chunk at a time, use dbSendQuery(), dbFetch(), then dbClearResult(). Alternatively, if you want all the results (and they'll fit in memory) use dbGetQuery() which sends, fetches and clears for you.

# S4 method for PqConnection,character
dbSendQuery(conn, statement, params = NULL, ...)

# S4 method for PqResult
dbFetch(res, n = -1, ..., row.names = FALSE)

# S4 method for PqResult
dbBind(res, params, ...)

# S4 method for PqResult
dbHasCompleted(res, ...)

# S4 method for PqResult
dbClearResult(res, ...)



A PqConnection created by dbConnect().


An SQL string to execute.


A list of query parameters to be substituted into a parameterised query. Query parameters are sent as strings, and the correct type is imputed by PostgreSQL. If this fails, you can manually cast the parameter with e.g. "$1::bigint".


Other arguments needed for compatibility with generic (currently ignored).


Code a PqResult produced by DBI::dbSendQuery().


Number of rows to return. If less than zero returns all rows.


Either TRUE, FALSE, NA or a string.

If TRUE, always translate row names to a column called "row_names". If FALSE, never translate row names. If NA, translate rownames only if they're a character vector.

A string is equivalent to TRUE, but allows you to override the default name.

For backward compatibility, NULL is equivalent to FALSE.


# For running the examples on systems without PostgreSQL connection: run <- postgresHasDefault()
#> Could not initialise default postgres database. If postgres is running #> check that the environment variables PGHOST, PGPORT, #> PGUSER, PGPASSWORD, and PGDATABASE, are defined and #> point to your database.
library(DBI) if (run) db <- dbConnect(RPostgres::Postgres()) if (run) dbWriteTable(db, "usarrests", datasets::USArrests, temporary = TRUE) # Run query to get results as dataframe if (run) dbGetQuery(db, "SELECT * FROM usarrests LIMIT 3") # Send query to pull requests in batches if (run) res <- dbSendQuery(db, "SELECT * FROM usarrests") if (run) dbFetch(res, n = 2) if (run) dbFetch(res, n = 2) if (run) dbHasCompleted(res) if (run) dbClearResult(res) if (run) dbRemoveTable(db, "usarrests") if (run) dbDisconnect(db)