From: Richard Huxton <dev@archonet.com>
To: Vadivel Subramaniam <vadivel.subramaniam@flextronicssoftware.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: how to store more than 3 MB of character data in Postgres
Date: Mon, 06 Jun 2005 11:48:16 +0100
Message-ID: <42A429F0.3040208@archonet.com> (raw)
In-Reply-To: <OFD54B33E3.AEC7F197-ON65257018.00386389-65257018.00388347@flextronicssoftware.com>
References: <OFD54B33E3.AEC7F197-ON65257018.00386389-65257018.00388347@flextronicssoftware.com>
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-TA...
>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, vadivel.subramaniam@flextronicssoftware.com
Subject: Re: how to store more than 3 MB of character data in Postgres
In-Reply-To: <42A429F0.3040208@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox