0

Found a strange behaviour. I am trying to append a number of rows to an existing postgresql table with R. The new rows are to be appended to the table.

dbWriteTable(con,name = "public.MyTab",value = data,row.names=F,overwrite=F, append=T)  

It returns TRUE and no errors and thus I assume that the copying has worked. However when I execute in my opened Terminal (psql) the following command:

SELECT count(*) from MyTab;

It returns:

 count 
-------
     0

When I then go back to R and test out

dbReadTable(con, "public.MyTab")

It shows the data correctly.

So what happened? Is the data properly in the Postgres database or not? If I insert the rows manually (INSERT INTO ...), then the immediate Count test works as intended.

Curlew
  • 1,022
  • 18
  • 39
  • If you write the table and quit R, *then* check in a *fresh* psql console is it there? I suspect R is operating in a transaction so changes don't happen until the transaction is closed. There's probably a function to do this. – Spacedman Oct 31 '14 at 13:42
  • Sadly not... Yes, you can call `dbCommit(con)`, but it tells me that no transactions are open and returns TRUE – Curlew Oct 31 '14 at 13:48

2 Answers2

2

I've had a similar problem -- the issue is that postgres doesn't handle capital letters in table and column names but a table can be created with a capital using DBI::dbWriteTable(). There is actually a table in one of my production databases that shows up with \dt in psql but I can't remove it with a drop table statement from the CLI (maybe I should try to get rid of it in R)! Here is a thread with a brief discussion on this.

Solution: stick to using lowercase plus underscores instead of camel case and you will be fine.

Taylor White
  • 644
  • 5
  • 11
0

Try name=c("public", "MyTab"). Also watch the case of the table names (and quote if necessary). More code below:

df <- data.frame(name=c("Alice", "Bob"), score=c(95L, 90L))

library(RPostgreSQL)

con <- dbConnect(PostgreSQL())

# Write table using c(schema, table)
dbWriteTable(con, name = c("public", "MyTab"), value = df,
             row.names=FALSE, overwrite=TRUE)

# Works
dbGetQuery(con, "SELECT * FROM \"MyTab\"")

# Doesn't work
dbGetQuery(con, "SELECT * FROM MyTab")
dbDisconnect(con)
Ian Gow
  • 3,098
  • 1
  • 25
  • 31
  • I just tried this and it did not work. I got this error `> RPostgreSQL::dbWriteTable(pano,name = c("public","jacky_test"),value = test,row.names=F,overwrite=TRUE) Error in postgresqlpqExec(new.con, sql4) : RS-DBI driver: (could not Retrieve the result : ERROR: syntax error at or near "STDIN" LINE 1: COPY "public"."jacky_test" ( "foo","bar" ) FROM STDIN ^ )` It does output a 0 row, 0b table. – Jacky Feb 14 '20 at 17:21
  • @Jacky If interpret "It does output a 0 row, 0b table" to mean that `test` is a data frame with no rows, I infer that `RPostgreSQL` does not check that there are rows and this causes the error message you see (nothing comes after `STDIN`). Checking that yourself (e.g., `if(nrows(test) > 0)`) should prevent this error. – Ian Gow Feb 15 '20 at 18:25
  • I meant that my data frame `test` has 2 columns and 10 observations but it outputs a 0 row and 0 byte table on the Redshift server. The redshift server does allow RPostgreSQL to read in data btw – Jacky Feb 17 '20 at 21:09