Mikkel Grum
2010-Sep-18 21:56 UTC
[R] Saving long character variable to database saves timestamp instead
When saving a data frame with long character variable, approximately longer than 30000 characters, to a text field in PostgreSQL, using RODBC on Windows XP, I get a timestamp saved to the database instead. Is there any way to extend the number of characters that a text variable can receive? Here are the details of my case (simplified): I'm using PostgreSQL to create a table CREATE TABLE testdf ( numchar bigint NOT NULL, chars text, CONSTRAINT pktestdf PRIMARY KEY (numchar) ) WITH ( OIDS=FALSE ); ALTER TABLE testdf OWNER TO postgres; Then in R I create a table with long character variables:> testdf <- data.frame(matrix(NA, 2, 2)) > names(testdf) <- c("numchar", "chars") > testdf$numchar <- c(30000, 35000) > testdf$chars[1] <- paste(rep("1 ", 30000/2), collapse = "") > testdf$chars[2] <- paste(rep("1 ", 35000/2), collapse = "")I then save it to the database and pull it out again to a new database:> require(RODBC) > pg <- odbcConnect("DSN", uid="postgres", pwd="***", case="tolower") > sqlSave(pg, testdf, "testdf", rownames = FALSE, append = TRUE) > testdf2 <- sqlQuery(pg, "SELECT * FROM testdf", stringsAsFactors = FALSE)The longest variable wasn't saved correctly:> nchar(testdf$chars)[1] 30000 35000> nchar(testdf2$chars)[1] 30000 19 A timestamp was saved instead:> testdf2$chars[2][1] "2010-09-18 00:00:00" A simple look in the database, e.g. with pgAdmin, reveals that there is indeed a timestamp in the database rather than a variable of 35000 characters. PostgreSQL claims that there is no limit to the length of text in a text variable, so where does the time stamp come from? Any ideas on how to avoid it and get all 35000 characters? BTW. In my ODBC driver, I have set the Max LongVarChar to 65520 and doubling it doesn't do the trick. Have I left out any details?> sessionInfo()R version 2.10.1 (2009-12-14) i386-pc-intel32 locale: [1] LC_COLLATE=English_Ireland.1252 LC_CTYPE=English_Ireland.1252 [3] LC_MONETARY=English_Ireland.1252 LC_NUMERIC=C [5] LC_TIME=English_Ireland.1252 attached base packages: [1] stats graphics grDevices utils datasets methods base other attached packages: [1] RODBC_1.3-1 Revobase_3.2.0 All assistance appreciated. Best regards, Mikkel *************************** Mikkel Grum [[alternative HTML version deleted]]