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]]
______________________________________________
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
