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]]

