[R] Not able to write to PostgreSQL database using "dbWriteTable"

Mark Dalphin mark.dalphin at pacificedge.co.nz
Thu Jun 14 01:21:34 CEST 2012


I still don't have any idea about your schema [eg CREATE TABLE (blah, 
blah, ...);], but I guess you don't have the right database type for 
"id" when you are storing a date.

In PostgreSQL:
CREATE TABLE myTable (
    id INTEGER PRIMARY KEY,
    aDate TIMESTAMP
);

In R:
dbGetQuery(conn, paste("INSERT INTO myTable (id, aDate)\n",
                                        "VALUES (1, '2012-06-14 
11:18:36');\n",
                                        sep=''))

All untested. If you want/need to use the sprintf() form, then just wrap 
the time variable in single quotes:

sql <- sprintf("INSERT INTO myTable (id, aDate)\nVALUES (%d, '%s');\n", 
1, '2011-06-14 11:18:36');
dbGetQuery(conn, sql)

Mark

Prakash Thomas wrote:
> Dear R User's,
>
>     Thank you, Mark. The following code suggested by you worked for me.
>          dbGetQuery(connAE1, sprintf("INSERT INTO test1 (id) VALUES ( %d
> );", i))
>     But I have a issue in passing  "date and time data" as variable.If I
> hard code the value like bellow it workings.
>           dbGetQuery(connAE1, sprintf("INSERT INTO test1 (id) VALUES ( %s
> );", ,'\'2012-06-10 16:36:00+05:30\''))
>            Can some body please help me with the code where I need to read
> from a variable(i) which has  data & time (2012-06-10 16:36:00+05:30). R is
> throwing error for space as shown in output bellow
>
> **********************************Console code &
> output*******************************
>     > if(dbExistsTable(connAE1, "test1")){
> +   dbGetQuery(connAE1, sprintf("INSERT INTO test1 (id) VALUES ( %s );", i))
> + }
> Error in postgresqlExecStatement(conn, statement, ...) :
>   RS-DBI driver: (could not Retrieve the result : ERROR:  syntax error at
> or near "16"
> LINE 1: INSERT INTO test1 (id) VALUES ( 2012-06-10 16:36:00+05:30 );
>                                                    ^
> )
> NULL
> *********************************************************************************************
>
>
> Thanks & Regards,
> Thomas
>
>
> On Wed, Jun 13, 2012 at 2:25 AM, Mark Dalphin <
> mark.dalphin at pacificedge.co.nz> wrote:
>
>   
>> I just tested your code and I _think_ you have a misconception about
>> dbWriteTable().
>>
>> Your code has some oddities so I am only guessing; for example, what is
>> "zz" and why is it in this snippet?
>>
>> In the absence of information on the database TABLE, it is even harder to
>> guess what you are doing, but I guess you are trying to use dbWriteTable to
>> add a small amount of data to an existing table since previously you select
>> from a similarly named table, "test1". The dbWriteTable function is writing
>> to the table called "test1.id" not to "test1, column id". If you check
>> your PostgreSQL schema, you will see that you have created a new table
>> called "test1.id" (which you will be required to quote to remove as the
>> DOT is an operator: DROP TABLE "test1.id";).
>>
>> I think you are trying to add a new row to the existing database table.
>> Try using (untested):
>> dbGetQuery(connAE1, sprintf("INSERT INTO test1 (id) VALUES ( %d );", i))
>>
>> and you will find things go better, assuming I grasped the problem you are
>> having correctly.
>>
>> Regards,
>> Mark Dalphin
>>
>>
>> Prakash Thomas wrote:
>>
>>     
>>> Dear R User's
>>>    Please help me to debug this issue. I am trying to write some data ( i=
>>> 6) to PostgreSQL database, but it not writing.
>>> Is there any issue in the way I use "dbWriteTable"?
>>>
>>> ++++++++++++++++++ Source Code ++++++++++++++++++++++++
>>>
>>> library("DBI")
>>> library("RPostgreSQL")
>>> drv1 <- dbDriver("PostgreSQL")
>>> i=6
>>> connAE1 <- dbConnect(drv1,host = "xx.xxx.xxx.xxx", port = "6443",
>>> dbname="DB",user = "xxxxx",password = "xxx")
>>> as.data.frame(zz[1])
>>> dbGetQuery(connAE1,'SELECT id FROM \"test1\"')
>>> if(dbExistsTable(connAE1, "test1")){
>>>    dbWriteTable(con=connAE1,name=**'test1.id',value=as.data.**
>>> frame(i),row.names=T
>>> ,overwrite=F ,append=T)
>>> }
>>> dbDisconnect(connAE1)
>>> dbUnloadDriver(drv1)
>>> ++++++++++++++++++++++++++++++**++++++++++++++++++++++++
>>>
>>> Following is the past of the console Log for your Reference
>>>
>>> ++++++++++++++++++ console log ++++++++++++++++++++++++
>>>
>>>
>>>       
>>>> dbGetQuery(connAE1,'SELECT id FROM \"test1\"')
>>>>
>>>>
>>>>         
>>>  id
>>> 1  1
>>> 2  2
>>>
>>>
>>>
>>>       
>>>> if(dbExistsTable(connAE1, "test1")){
>>>>
>>>>
>>>>         
>>> +     dbWriteTable(con=connAE1,name=**'test1.id',value=as.data.**
>>> frame(i),row.names=T
>>> ,overwrite=F ,append=T)
>>> +    .... [TRUNCATED]
>>>  id
>>> 1  1
>>> 2  2
>>> ++++++++++++++++++++++++++++++**+++++++++++++++++++++
>>>
>>> Thanks & Regards,
>>> Thomas
>>>
>>>        [[alternative HTML version deleted]]
>>>
>>> ______________________________**________________
>>> R-help at r-project.org mailing list
>>> https://stat.ethz.ch/mailman/**listinfo/r-help<https://stat.ethz.ch/mailman/listinfo/r-help>
>>> PLEASE do read the posting guide http://www.R-project.org/**
>>> posting-guide.html <http://www.R-project.org/posting-guide.html>
>>> and provide commented, minimal, self-contained, reproducible code.
>>>
>>>
>>>       
>> --
>>
>>
>>
>>   Mark Dalphin Ph.D.
>>
>> Director of Bioinformatics
>>
>> mark.dalphin at pacificedge.co.nz <mailto:mark.dalphin@**pacificedge.co.nz<mark.dalphin at pacificedge.co.nz>
>>     
>> *Ph:* +64-3-479-5805
>> *Cell:* +64-21-156-7625
>> *Skype:* mdalphin
>> <http://www.facebook.com/**pages/Pacific-Edge/**111356775582456<http://www.facebook.com/pages/Pacific-Edge/111356775582456>>
>> <http://twitter.com/#%21/**pacificEdgeLtd<http://twitter.com/#%21/pacificEdgeLtd>>
>> <http://www.youtube.com/**PacificEdgeLtd<http://www.youtube.com/PacificEdgeLtd>
>>     
>> 87 St David St, PO Box 56, Dunedin, New Zealand 9016www.pacificedge.co.nz
>>
>>
>>     
>
> 	[[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>   

-- 
	


    Mark Dalphin Ph.D.

Director of Bioinformatics

mark.dalphin at pacificedge.co.nz <mailto:mark.dalphin at pacificedge.co.nz>
*Ph:* +64-3-479-5805
*Cell:* +64-21-156-7625
*Skype:* mdalphin
<http://www.facebook.com/pages/Pacific-Edge/111356775582456> 
<http://twitter.com/#%21/pacificEdgeLtd> 
<http://www.youtube.com/PacificEdgeLtd>

87 St David St, PO Box 56, Dunedin, New Zealand 9016www.pacificedge.co.nz



More information about the R-help mailing list