Problem with too short column

[T***@limagrain.com]

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 ?

I'm using Postgresql 7.2.2.

Thanks in advance.

TOm



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[scott.marlowe]

Post by T***@limagrain.com
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.

Post by T***@limagrain.com
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.



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Sam Barnett-Cormack]

Truncate it in the applications? Create an SQL function with pg-perl or
something?

Post by T***@limagrain.com
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 ?
I'm using Postgresql 7.2.2.
Thanks in advance.
TOm
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[scott.marlowe]

Post by T***@limagrain.com
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 ?

Note that in my previous posting I think I used substring with a 0 start,
it should start at 1:

select substring('abcdefgh',1,4);
SELECT
substring
----
abcd


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly