Hi,

I was wondering if anybody knows how to get around the following error:

[JDBCExceptionReporter] ERROR: value too long for type character varying(50)

I am using Postgres 8.1 and Hibernate 3.0.5 with JBoss AS 4.0.2.

This happens as a result of trying to insert 51 characters (or more) into a varchar(50) field. MySQL and other databases truncate the data for you but Postgres will not.

I have tried setting the length attribute on the property mapping but it makes no difference. Here is the property mapping:

<property name="dropOffAddress2"
type="string"
update="true"
insert="true"
column="DropAdditionalAddress"
length="50"
not-null="false" />

The postgres doco says that you can explicitly truncate data by using the following syntax:

INSERT INTO test VALUES ('too long'::varchar(5));

Can anyone help me with a mapping to do this? I wish to avoid having to explicitly write code to trim the data to the correct length before insert or update.

Cheers!

Hello,

You can probably write something like this:
<sql-insert>INSERT INTO test (x) VALUES (?::varchar(5))</sql-insert>

Look the Hibernate documentation for more details:
http://www.hibernate.org/hib_docs/v3/re ... erysql-cud

_________________
François J.
Don't forget to rate

Yeah, Thanks, I thought of that. I was hoping for something a little more generic like some option to tell Hibernate to trim the data for you. Otherwise I'll have to write custom inserts for every mapping and then if a column definition changes I would have to go back and update the mappings. Yuck!

Hello,

In last resort, you can use the Interceptors to trim the data before saving them. You would have to loop trough all the attributes of the entity and trim all the strings to the value you put the attribute's property. This way you will have something generic.

Documentation about the Interceptors:
http://www.hibernate.org/hib_docs/v3/re ... terceptors

Good Luck,

After searching for something totally different I stumbled across the answer... Use a UserType.

It is actually a FAQ (oops) and there is a topic here.