How to append/update a row from a data frame to a table in PostgreSQL db table that has the same columns using RPostgreSQL?

Question

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

Chris Holbrook · Accepted Answer · 2017-11-29 02:34:03Z

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)

answered Nov 29, 2017 at 2:34

Chris Holbrook

2,6461 gold badge21 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

RanonKahn Over a year ago

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?

Chris Holbrook Over a year ago

If using dbWriteTable and the table exists in your db, then append = TRUE should insert new rows into that table.

Chris Holbrook Over a year ago

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)

Fernando C. Over a year ago

Trying to run this it says: Error: Failed to fetch row: ERROR: relation "mytable" already exists

Chris Holbrook Over a year ago

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

|

Collectives™ on Stack Overflow

How to append/update a row from a data frame to a table in PostgreSQL db table that has the same columns using RPostgreSQL?

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related