Postgresql does not trim overlay lines
According to the documentation, strings longer than specified with a character or VARCHAR should be truncated:
If one explicitly specifies a value for a character changing (n) or character (n), then the excess length value will be truncated to n characters without raising the error. (This is also required by the SQL standard.)
but I cannot get it to work. Now the documentation says to "explicitly" distinguish the meaning for a character, so maybe I'm missing that. Below is a simple test table:
create table test1(
tval character varying(20));
where the following is in error: ERROR: value too long for a character like (20)
insert into test1 values
('this is a super long string that we want to see if it is really truncated');
How can I get this to work?
source to share
This will not truncate because this is just an assignment:
create table test1(tval character varying(20));
insert into test1 values ('this is a super long string that we want to see if it is really truncated');
but it will because it is an explicit expression:
insert into test1 values (CAST('this is a super long string that we want to see if it is really truncated' AS varchar(20)));
To get the truncation behavior, you have to use an explicit cast, and honestly, I would like the SQL standard not to specify this.
The best way to deal with this is to clearly state what you want:
insert into test1 values (left('this is a super long string that we want to see if it is really truncated', 20));
source to share