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