Re: odbc varchar/longvarchar default setting

here is what the ODBC faq has to say:

6.5) With MS Access, why can't I index on text fields -OR- why do I get "Invalid field definition 'field'" in definition of index or relationship?
Text fields are mapped to SQL_LONGVARCHAR by default. As a result MS Access treats these colomns as "Memo" types. The good news is that you can store up to the PostgreSQL block size limit in a text column. PostgreSQL has a tuple limit of just under 8k prior to version 7.1 which includes a new feature called TOAST which allows the storage of much larger strings.

You can change the mapping of Text fields to SQL_VARCHAR by unchecking the Advanced driver option "Text as LongVarchar" under Data Type Options. This should allow text fields to be used but you will be limited to the maximum size of a varchar.

--- On Fri, 11/23/07, Tom Hart <tomhart(at)coopfed(dot)org> wrote:

> From: Tom Hart <tomhart(at)coopfed(dot)org>
> Subject: [ODBC] odbc varchar/longvarchar default setting
> To: pgsql-odbc(at)postgresql(dot)org
> Date: Friday, November 23, 2007, 2:27 PM
> Hey everybody, and hey again everybody I know from the
> General list.
>
> I wasn't sure how to report this, as I don't see it
> as a bug, but I
> wanted to make you guys aware of it.
>
> The default setting in the odbc driver is to pass pg
> 'text' datatypes as
> LongVarChar (with a default length of 8190). However in
> Access (and as
> I'm told Crystal Reports as well) it converts this into
> a 'memo'
> datatype instead of a 'text' datatype (in access
> text = varchar(255)).
> This can be fixed by deselecting that option, and making
> sure the
> varchar length is 255, but I'm curious as to why this
> isn't the default.
> Granted postgresql isn't developed for or targeted
> towards Windows
> users, but I believe that a large number of people using
> this odbc
> driver are using it to connect to things like access and
> crystal
> reports, and it seems to me that varchar(255) would make
> more sense as a
> default. Of course I understand that there's the
> possibility of lost
> data (if your text fields are past 255 characters), but
> there's the
> possibility of that with the longvarchar as well, and
> having
> varchar(255) as the default would foster a higher level of
> interoperability, especially with access users.
>
> I'm sure you guys have heard this before, but why
> isn't this the
> default, and are there any plans or issues with making this
> the default?
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an
> appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org
> so that your
> message can get through to the mailing list cleanly