RpostgreSQL doesn't support hstore data type

[Ravi Varma Alluru]

Hi Team,

I am trying to a update query on a table in postgreSQL database from GNU R using the RpostgreSQL package. And while trying to do so I am facing an issue with the hstore data type. Below is the sample query I am passing to the database

txt <- paste("UPDATE adminvectors.county SET attributes= attributes || hstore('usco@TP-TotPop@2010',",new_data$TP.TotPop[new_data$FIPS == i],") where geoid=",i)

  dbGetQuery(con, txt)

In the above query I am passing the value of a variable in a data frame as value for hstore key.

And I am getting the following error

In addition: Warning message:

In postgresqlQuickSQL(conn, statement, ...) :

  Could not create executeUPDATE adminvectors.county SET attributes= attributes || hstore('usco@TP-TotPop@2010', 9.315 ) where geoid= 21055

Error in postgresqlExecStatement(conn, statement, ...) : 

  RS-DBI driver: (could not Retrieve the result : ERROR:  function hstore(unknown, numeric) does not exist

LINE 1: ...adminvectors.county SET attributes= attributes || hstore('us...

                                                                                               ^

HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Is there a way to resolve this driver issue?

Any lead for this issue is appreciated.

Thanks and Regards,

Ravi Varma Alluru.

[Neil Tiffin]

The key to the error is ‘function hstore(unknown, numeric) does not exist’.

This is returned from the database, not the driver.  I’m not an hstore user, but my manual does not show any hstore function that takes unknown and numeric arguments.

Depending on what you are trying to accomplish you might try: 

SET attributes= attributes || hstore(‘usco@TP-TotPop@2010’::TEXT, 9.315::TEXT ) 

Which is casting both arguments to TEXT.  The red text is added.

In any case this is not the RPostgreSQL driver.

Neil

--
You received this message because you are subscribed to the Google Groups "RPostgreSQL Development and Discussion List" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rpostgresql-d...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Ravi Varma Alluru]

Thank you Neil for your help.Casting both the arguments to TEXT has helped in resolving the error.

Right now i am trying to execute the modified query in a loop. Although the loop is getting executed successfully the update statements are not getting committed properly in the database. In other words I couldn't find any changes to the database happening although the query is running without errors. Below is the piece of code I am trying to run in R command prompt.

for(i in new_data$FIPS) {
  drv <- dbDriver("PostgreSQL")
  con <- dbConnect(drv, dbname="ip_platform", host="******", port="5432", user="data_loader", password="******")
  txt <- paste("UPDATE adminvectors.county SET attributes= hstore('usco@TP-TotPop@2010'::TEXT,",new_data$TP.TotPop[new_data$FIPS == i],"::TEXT) where geoid ='",i,"'")
  dbSendQuery(con, txt)
  dbCommit(con)
  dbDisconnect(con)
}

Can anyone let me know if I have done something wrong? Any help is highly appreciated

[Neil Tiffin]

On Nov 2, 2015, at 10:43 AM, Ravi Varma Alluru <varma.a...@gmail.com> wrote:

Thank you Neil for your help.Casting both the arguments to TEXT has helped in resolving the error.

Right now i am trying to execute the modified query in a loop. Although the loop is getting executed successfully the update statements are not getting committed properly in the database. In other words I couldn't find any changes to the database happening although the query is running without errors. Below is the piece of code I am trying to run in R command prompt.

for(i in new_data$FIPS) {
  drv <- dbDriver("PostgreSQL")
  con <- dbConnect(drv, dbname="ip_platform", host="******", port="5432", user="data_loader", password="******")
  txt <- paste("UPDATE adminvectors.county SET attributes= hstore('usco@TP-TotPop@2010'::TEXT,",new_data$TP.TotPop[new_data$FIPS == i],"::TEXT) where geoid ='",i,"'")
  dbSendQuery(con, txt)
  dbCommit(con)
  dbDisconnect(con)
}

Keep in mind in mind that paste() puts a space in place of every comma by default.  That means if i = 5, that 

geoid =‘“,i,”’ 

is really

geode = ‘ 5 ‘

which is probably not what you intended. A space before the last ::TEXT is probably also causing a problem.  To keep this from happening put sep=“” as the last parameter of the paste().

The best way to check these issues is to print the variable txt before executing it. 

[Ravi Varma Alluru]

Thank you Neil for your prompt help. The issue is resolved now.