[R-sig-DB] R and PostgreSQL - Writing data?

[James David Smith]

Hi Sean,

Thanks for the reply. I'm familiar with UPDATE queries when working in
PostgreSQL, but not from within R. Would it look something like this?

dbWriteTable(con, " UPDATE table SET ucam_no2 = 'ucam_no2$interpolated_data' ")

My problem is how to get the R data 'within' my SQL statement I think.

Thanks

James



On 28 September 2012 15:19, Sean Davis <sdavis2 using mail.nih.gov> wrote:
> On Fri, Sep 28, 2012 at 10:14 AM, James David Smith
> <james.david.smith using gmail.com> wrote:
>> Dear all,
>>
>> Sorry if this isn't quite the right place, but it's the first time	
SendSave NowDiscardDraft autosaved at 15:36 (0 minutes ago)
33% full
Using 3.4 GB of your 10.1 GB
©2012 Google - Terms & Privacy
Last account activity: 50 minutes ago
Details
People (2)
Sean Davis	
Add to circles

Show details
Ads – Why these ads?
Big Data Too Slow?
Real-Time Analytics for Big Data. Visual Drag & Drop UI. Quick & Easy
PentahoBigData.com
Talend Open Source ESB
Open Source ESB Based on Apache CXF and Apache Camel. Free Download!
www.talend.com/Free_ESB_Software
Warp I/O for SQL Server
Speed SQL Server performance 3x Faster I/O, reduced storage
www.confio.com/warp-io
Storage Container Sussex
Ex-Shipping Containers Sale & Hire Storage Container 0800 043 6311
www.CsShippingContainers.co.uk
More about...
MS Access Database SQL »
Database »
Excel Database Query »
Oracle Database Problems »

>> I've posted here. My issue is to do with writing to a PostgreSQL
>> database from within R. My situation is best explained by some R code
>> to start:
>>
>> #Connect to the database
>> con <- dbConnect(PostgreSQL(), user="postgres", password="password",
>> dbname="database")
>>
>> #Get some data out of the database.
>> ucam_no2$original_data <- dbGetQuery(con, "select ucam_no2 FROM table")
>>
>> This returns say 10000 rows of data, but there is only data in about
>> half of those rows. What I want to do is interpolate the missing data
>> so I do this:
>>
>> #Generate some data
>> ucam_no2$interpolated_data <- na.approx(ucam_data$ucam_no2, na.rm = FALSE)
>>
>> This works well and I now have 10000 rows of data with no empty cells.
>> I now want to write this back into my PostgresSQL database. Into the
>> same row that I took the data from in the first place. But I don't
>> know how. I can write to a new table with something like the below,
>> but what I'd really like to do is put the data back into the table I
>> got it from.
>>
>> # Try to write the data back
>> dbWriteTable(con, "new_data", ucam_no2$interpolated_data)
>
> Hi, James.
>
> You'll need to look into doing a SQL UPDATE.  That is the standard way
> to "put data back into the table I got it from".
>
> Sean