[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