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

[Sean Davis]

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
> 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