odbc varchar/longvarchar default setting

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@postgresql.org so that your
message can get through to the mailing list cleanly

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 wrote:

> From: Tom Hart
> Subject: [ODBC] odbc varchar/longvarchar default setting
> To: pgsql-odbc@postgresql.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@postgresql.org
> so that your
> message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster