How to append a new column to a postgresql table from a dataframe from R

Question

I am trying to append or cbind a new column from R to a postgresql table. So, I have created a new column in the postgresql table where I want to put my dataframe column, i.e

ALTER TABLE schema.table
    ADD COLUMN newcolumn "char";

library(RPostgres)
library(tidyverse)
library(rpostgis)

# SQL CONNECTION

fun_connect<-function(){dbConnect(RPostgres::Postgres(),dbname = 'mydb', 
                                  host = 'localhost', # i.e. 'ec2-54-83-201-96.compute-1.amazonaws.com'
                                  port = 5432, # or any other port specified by your DBA
                                  user = 'postgres',
                                  password = 'secretpass'}

conn <- fun_connect()

mytable<-tbl(conn, "mydb")

# MY data frame

a<-data.frame(a= c("123","231543","1232","45389","4398543"))


# Trying to append or cbind my data frame column 

   #First try:
   
copy_to(conn,a,"newcolumn")

   #Second try:

RPostgreSQL::dbWriteTable(conn, "table", a,append=T)

So i Have the next error: 

Error: COPY returned error: ERROR:  el valor nulo en la columna «FIRSTcolumn» de la relación «table» viola la restricción de no nulo
DETAIL:  La fila que falla contiene (null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 1).
CONTEXT:  COPY table, línea 1: «10208011005»

Third try:

pgInsert(conn, name=c("schema","table"), a)

But I get:
1 out of 1 columns of the data frame match database table columns and will be formatted for database insert.
Error : Failed to fetch row: ERROR:  el valor nulo en la columna «FIRSTcolumn» de la relación «table» viola la restricción de no nulo
DETAIL:  La fila que falla contiene (null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 1).

Insert failed. No changes made to database.

So I dont know how to append a new column from R to Postgresql in a simple way. I ask me if exist something similar to cbind(df, df2) (lol) with df1 and df2 having the same nrow, but neither I don't have a proxy of how it's can be possible

Thanks for help me. Regards!

score 2 · Answer 1 · answered Jan 27 '21 at 22:55

To begin with you have two different types of data objects. One is a connection to a remote (not in R memory) sql table, and the other is a local (in R memory) dataframe. These two types of objects can not be immediately combined. This means there is no simple cbind equivalent for these data types.

I would recommend first copying the local dataframe into the sql database. Then you will have two of the same type of data objects - both sql tables - that you can combine.

Your attempts using copy_to and dbWriteTable are a good start. But these functions write an entire R dataframe to an entire table, not as part.

For copying a dataframe into sql I use:

DBI::dbWriteTable(
      db_connection,
      DBI::Id(
        catalog = db,
        schema = schema,
        table = sql_table_name
      ),
      r_table_name
)

The sql syntax you are looking for to insert values is most likely:

INSERT INTO first_table(names_of_columns1) SELECT names_of_columns2 FROM second_table;

I would make this in R something like the following:

insert_into_column <- function(connection, tbl1, col1, tbl2, col2){
    prep = tbl2 %>% select(col2)

    query <- glue::glue("INSERT INTO {tbl1} ({col1})\n",
                        dbplyr::sql_render(prep))

    result <- DBI::dbExecute(connection, as.character(query))
}

Note that inserting values to a column of an sql table may not work the same as inserting values into the column of an R dataframe. Tables in sql are not sorted by default, so your insert may not guarantee the correct order.

Thanks for you answer! Just that I need to process the data from postgresql because I have the data in a .csv that is very weight and in R is very slow work with it. — Bryan Castillo, Jan 28 '21 at 02:10
Sorry, I am not sure from your comment whether my answer is sufficient, or there is some other problem that needs to be addressed. Please would you clarify? — Simon.S.A., Jan 28 '21 at 07:44
Simon.S.A. Sorry for my late answer, finally I resolved this question in this way: https://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match I used UPDATE from SQL and this work very well, thanks for you time! — Bryan Castillo, Feb 02 '21 at 17:04

How to append a new column to a postgresql table from a dataframe from R

1 Answers1