Re: how to store more than 3 MB of character data in Postgres

Vadivel Subramaniam wrote:
>
> I assume, it could not be a problem with ODBC. I am able to store 2.5 MB of
> data into Oracle using the same ODBC APIs.

Well, it certainly isn't to do with PG itself:

$ cat bigtest.pl
#!/usr/bin/perl
print "CREATE TABLE foo (a int, b varchar);\n";
print "INSERT INTO foo VALUES (1, '".('a' x 5000000)."');\n";

$ perl bigtest.pl | psql -Urichardh richardh
CREATE TABLE
INSERT 3634376 1
$ psql -Urichardh richardh

richardh=> SELECT a,length(b) FROM foo;
a | length
---+---------
1 | 5000000
(1 row)

> Only difference is that Oracle supports CLOB datatype, that is mapped to
> LONG VARCHAR in ODBC.

> The input parameter during insert is bound using the below ODBC API
> retStatus = SQLBindParameter(dStmtHandle, 1, SQL_PARAM_INPUT,
> SQL_C_CHAR, SQL_LONGVARCHAR, dLen, 1, pData, 0, &dLen);
>
> For PostGres also the same API is used. Here varchar/text at the Postgres
> schema is mapped to LONGVARCHAR at ODBC.
> We can not go for bytea etc, since the data is in character format.
>
> I tried using "text" also at the postgres schema, still the impact is same
> (only stores around 64K).
> Could you please provide some example, how the large character data ( more
> than 1 MB ) can be inserted to text/varchar (in PostGres) using ODBC?

Hmm - looking back at your original email, I see you're using the
UnixODBC driver. Might be worth trying the PG version:
http://gborg.postgresql.org/project/psqlodbc/genpage.php?downloads

First step though, should be to check whether there are any
configuration settings that apply to longvarchar, because it sounds like
the odbc driver should be mapping to that.

I can't help you with finding those settings, since I have only ever
used the "official" PG driver on Windows.

--
Richard Huxton
Archonet Ltd