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

Vadivel Subramaniam wrote:
> 
> Hi,
> 
> We have  a requirement wherein we have to store around 3 MB of data
> in Postgres database.
> 
> We had gone through the postgres website 
> http://www.postgresql.org/docs/7.4/interactive/datatype-character.html#DATATYPE-CHARACTER-SPECIAL-TABLE
> 
> The above link says "varchar" can store upto 1 GB of data. 

I don't think it does, it says the "longest possible character string" 
which covers type "text".
> But when
> i try to insert using ODBC API's the varchar field takes only 65484
> characters.    But i could insert more characters using INSERT SQL
> command.  Even in this case when we fetch the data it reads only
> 65540 characters. Remaining data is not read from the varchar field.

So, your problem is with the ODBC interface rather than actually 
inserting the data.

> We have tried in 2 ways
> 
> 1. table is created without any limit specified for varchar. create
> table details (name varchar, data varchar);
> 
> 2. create table utilntmlscripts (name character varying, data
> character varying(10485770)); ERROR:  length for type 'varchar'
> cannot exceed 10485760 It's not allowing more than 10 MB of size
> during table creation.
> 
> 
> Is there anything needs to be configured to make the varchar field to
>  support upto 1 GB. Even if it does not support 1 GB, can we store &
> retrieve atleast 10 MB of character data in Varchar field?

Check your ODBC settings and try type "text" instead of "varchar". I 
think the problem is with ODBC restrictions rather than anything else. 
You should be able to get type "text" to map to a "Memo" type or 
similar, which should hold more than 64k IIRC.

Failing that, try internal type "bytea" and large-object on the ODBC end.

--  Richard Huxton  Archonet Ltd