CLOB VS VARCHAR

[Garth Keesler <ga...@gdcjk.com>]

I'm converting from MS SQL Server to PostgreSQL, switching from VARCHAR 
in SQL Server to TEXT (or CITEXT depending) in PG. I'm converting the 
software from MS AccessVB to Java/Cayenne and am wondering if there is a 
preference for CLOB over VARCHAR(n) when hand generating the DBEntity 
attributes. I'm not reverse engineering them from the schema for a 
couple of reasons.

Thanx,
Garth

Re: CLOB VS VARCHAR

[Andrus Adamchik <an...@objectstyle.org>]

Cayenne DbAttribute types are all taken from JDBC spec. Nominally PG 'text' maps to JDBC 'CLOB', and 'varchar(n)' to VARCHAR. However PG promises no performance differences between 'text' and 'varchar(n)'. So certainly you can map 'text' as either.

On the other hand, (and this was on MySQL, not PG), I observed performance slowdown when mapping text columns as CLOBs. This had something to do with the extra processing the driver and Cayenne adapter had to perform to convert a CLOB to String. So on MySQL projects my current preference is VARCHAR. YMMV with PG.

Andrus 


On Oct 31, 2012, at 3:27 PM, Garth Keesler <ga...@gdcjk.com> wrote:
> I'm converting from MS SQL Server to PostgreSQL, switching from VARCHAR in SQL Server to TEXT (or CITEXT depending) in PG. I'm converting the software from MS AccessVB to Java/Cayenne and am wondering if there is a preference for CLOB over VARCHAR(n) when hand generating the DBEntity attributes. I'm not reverse engineering them from the schema for a couple of reasons.
> 
> Thanx,
> Garth
>