0

I've used RPostgreSQL to connect R and postgresQL, and I'd like to insert a column into another table according to the "pid", please advise how is can be achieved using R command:

>library(RPostgreSQL)
>drv<-dbDriver("PostgreSQL")
>itemlist<- dbGetQuery(con, "SELECT * from project_budget_itemlist")
>View(itemlist)
 pid item cost
  1   ABC  9
  2   ACB  8
  3   BAC  7
  3   ZZZ  6

and another tables is as follow:

  >name<- dbGetQuery(con, "SELECT * from namelist")
  >View(name)

  pid name 
   1  Sally
   2  Joy
   3  Susan

I want to the result to be:

   pid item cost name 
    1   ABC  9   Sally
    2   ACB  8   Joy
    3   BAC  7   Susan
    3   ZZZ  6   Susan
Samoth
  • 716
  • 15
  • 34
  • Maybe `?merge` ? (You want a dataset with the expected result or to write the expected result back in postgres ?) – Tensibai Sep 17 '15 at 08:19
  • @Tensibai: Hi, no need to return the result in postgres. I just want the combination and do some plot. – Samoth Sep 17 '15 at 08:21
  • In this case `merge(itemlist,name)` should do ... – Tensibai Sep 17 '15 at 08:22
  • Thanks, but it seems in vail and the merged table is empty. – Samoth Sep 17 '15 at 08:32
  • I've no idea, this works fine with data frames created with your question example... maybe you can `dput(itemlist)` and `dput(name)` so I can try on the same type of data – Tensibai Sep 17 '15 at 08:55
  • The data type is data frames as well, and the error message is :Error in fix.by(by.x, x) : 'by' must specify uniquely valid columns. – Samoth Sep 17 '15 at 09:18

1 Answers1

0

If there are no matching pids in both outputs, the merge will return an empty data frame. If there are, then this should work:

merge(itemList, name)
Serenthia
  • 1,222
  • 4
  • 22
  • 40