Re: Problem with too short column

On Wed, 4 Feb 2004 Thomas(dot)AUBRY(at)limagrain(dot)com wrote:

> Hi,
>
> I actually try to insert varchar which length is higher than 32 in a column
> which type is varchar(32). I can't change the type of the column, and I
> want to trunc the data. I know it's possible ! I have seen this !
>
> Example : "j'aime les saucisses avec du beurre dedans" will be "j'aime les
> saucisses avec du beu". For the moment it produces a SQL Error !
>
> Is it an option in the config files ?

No, it's your job to truncate it. You could use substring in your insert
statement like this:

insert into tablename (fieldname) values
(substring('inputdatathatstoolonghere',0 for x);

where x is the length you want it chopped off at.

Further, you could create an insert (i.e. before) trigger to do the same
thing.

Note that the SQL spec is very clear, truncating automatically is an
error, and if you want things truncated, it's not the databases job to do
it by any kind of "default" setting. Luckily, it's pretty easy to do it
either "by hand" in SQL with substring, or by trigger.

> I'm using Postgresql 7.2.2.

Upgrade. There are bugs in the early 7.2 series you don't want to have to
deal with. I'd recommend going to 7.4.1 if you can, but if not, at least
upgrade to the latest version of 7.2. As long as you compile it the same
as your earlier 7.2 install, it's an in place (i.e. no dump restore
required) upgrade.