5

This question is an extension to the question already posted earlier in this forum. I need to append/update a row from a data frame to a table in PostgreSQL db table that has the same columns using RPostgreSQL.I am bale to copy the whole table or use insert command as shown below: 

insert into mytable (FName, LName, Age) values (Rosy, Rees, 54)

But, I want to copy row(s) (or a subset from a data frame) directly to RPostgreSQL database table. Any suggestion please?

Example:

Data Frame in R

FName   LName   Age
Rosy    Rees    54

Table in PostgreSQL database before copying the row from data frame

FName   LName   Age
John    Doe     35
Jane    Sanders 32
Robert  Muller  45

Table in PostgreSQL database after copying the row from data frame

FName   LName   Age
John    Doe     35
Jane    Sanders 32
Robert  Muller  45
Rosy    Rees    54
RanonKahn
  • 853
  • 10
  • 34

1 Answers1

7

If you made the following table like this in db called 'mydb':

DROP TABLE IF EXISTS mytable;

CREATE TABLE mytable (
  fname text,
  lname text,
  age integer);

INSERT INTO mytable(fname, lname, age) VALUES
  ('John', 'D.', 35),
  ('Jane', 'S.', 32),
  ('Robert', 'M.', 45);

Then use dbWriteTable in something like this in R to append records form a data frame:

library(RPostgreSQL)

#load PostgreSQL driver
drv <- dbDriver("PostgreSQL")

#make connection to the postgres database
con <- dbConnect(drv, dbname = "mydb",
             host = "localhost", port = 5432, 
             user = 'postgres', password = 'postgres')

#insert data into mytable from data frame
df <- data.frame(fname = "Rosy", lname = "R.", age = 54)
dbWriteTable(con, "mytable", df, append = TRUE, row.names = FALSE)

dbDisconnect(con)
Chris Holbrook
  • 2,531
  • 1
  • 17
  • 30
  • 1
    When I try to overwrite/update a value in an existing table in the database, the whole table gets recreated instead of one row getting updated. Can you please me on how to handle updating of data? – RanonKahn Dec 12 '17 at 04:05
  • If using dbWriteTable and the table exists in your db, then `append = TRUE` should insert new rows into that table. – Chris Holbrook Dec 12 '17 at 13:25
  • If you are trying to update an existing record in a table, then write your sql commands in a text string and pass that to postgresql using dbSendQuery. e.g., `sql <- "UPDATE mytable SET age = 99 WHERE fname = 'John' AND lname = 'D.';"` `dbSendQuery(con, sql)` – Chris Holbrook Dec 12 '17 at 13:38
  • Trying to run this it says: Error: Failed to fetch row: ERROR: relation "mytable" already exists – Fernando C. Feb 23 '22 at 16:16
  • 1
    @FernandoC. The code above (in the answer and comments) all runs without error for me in PostgreSQL 13; R 4.1.2; RPostgreSQL 0.7.3; Win 10. – Chris Holbrook Feb 24 '22 at 16:19
  • Thanks! I found out that the issue was that I was trying to write to a materialized view, which is not possible by this way I think. – Fernando C. Feb 28 '22 at 11:58