How to read data frame in R into existing SQL table?

Question

Here is my code:

dbSendQuery(con, "create table origdata (day1 numeric, day2 numeric, day3 numeric, day4 numeric, day5 numeric, day6 numeric, day7 numeric, day8 numeric, day9 numeric, day10 numeric);")

#3.4
data=data.frame(origData)
data[is.na(data)]=0
dbWriteTable(con, "origdata", value=origData, row.names =F,append = T)

But I am getting the error : Error in postgresqlpqExec(new.con, sql4) : RS-DBI driver: (could not Retrieve the result : ERROR: column "X1" of relation "origdata" does not exist )

and part of my data frame for reference.

How can I get it to read the values from my data frame in to the table origdata? I need the columns to have those labels.

Column names of the DB table and your data.frame must match. — Roman Luštrik
– Roman Luštrik, Commented Dec 12, 2020 at 20:25
Reconsider saving data in wide format inside an enterprise RDBMS like Postgres. Number-suffixed columns even tables are not optimal database design. Use one column for day indicator and another for its value in long, normalized format. — Parfait
– Parfait, Commented Dec 12, 2020 at 22:33

Roman Luštrik · Accepted Answer · 2020-12-13 21:06:38Z

1

To be able to import your data.frame into an existing database table, column names will have to match the SQL schema.

answered Dec 13, 2020 at 21:06

Roman Luštrik

70.9k25 gold badges160 silver badges203 bronze badges

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

Collectives™ on Stack Overflow

How to read data frame in R into existing SQL table?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related