Using dplyr to create and retrieve data from temporary postgres table

Question

I am trying to figure out how to retrieve data from a temporary table created on a Postgres database using dplyr. The following code works (I am able to create a temp table using R, and retrieve those results).

flights.db <- src_postgres(dbname = dbname, host = host, port = port,
                           user = user, password=pwd)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, host = host, port = port, dbname = dbname, user = user, password = pwd)

## Writes the SQL code to create a temporary table on the Postgres Database
sql_tmp_create <- paste0('create temp table tmptblaa as 
              select * from connections
              where \"Carrier\" = \'AA\' and \"Year\" = \'2015\'   ')

## Executes the code
dbSendQuery(con, sql_tmp_create)

## Return 10 results from temporary table on Postgres
dbGetQuery(con, 'select * from tmptblaa limit 10')

However, this does not work for me, and I receive an error that the table does not exist:

flights <-tbl(flights.db, "tmptblaa")

Any ideas what I could be doing wrong? For more context, this is to improve the efficiency of a Shiny app that I am working on for a school project. Thanks.

jess · Accepted Answer · 2017-03-21 22:24:44Z

2

The problem with temp tables is that they only exist within one session initiated with one connection object. If you create your temp table using the code above, than the temp table can only be accessed using the con object. dplyr doesn't allow you to create tables afaik, so I'm afraid you can't do what you'd like to.

However, you could consider a materialized view in case you want to pre aggregate stuff in the db. dplyr can query from a materialized view and as it is persistent, there is no need to create the mv with dplyr. You can query data from a materialized view by using e.g. con %>% tbl(sql("select * from my_mv")) %>% collect(n = Inf). The important detail here is the sql function, the syntax is slightly different from regular tables.

edited Mar 21, 2017 at 22:24

answered Mar 21, 2017 at 21:01

jess

5342 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Julian Hui Over a year ago

Thanks. This is the error that I get... flights <-tbl(flights.db, sql('select * from tmpaa')) Error in postgresqlExecStatement(conn, statement, ...) : RS-DBI driver: (could not Retrieve the result : ERROR: relation "tmpaa" does not exist LINE 1: SELECT * FROM (select * from tmpaa) "zzz38" WHERE 0=1

jess Over a year ago

Ah, my bad. You're right, it's because of the nature of temp tables. I've updated my answer.

Julian Hui Over a year ago

Thanks! The materialized view does the job!

Collectives™ on Stack Overflow

Using dplyr to create and retrieve data from temporary postgres table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related