According to the documentation, strings longer than that specified by character varying or VARCHAR should be truncated:
If one explicitly casts a value to character varying(n) or character(n), then an over-length value will be truncated to n characters without raising an error. (This too is required by the SQL standard.)
but I can not get it to work. Now the documentation does say one has to "explicitly" cast a value to character varying so maybe I am missing that. Below is a simple test table:
create table test1(
tval character varying(20));
where the following fails with ERROR: value too long for type character varying(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?
This won't truncate, because it's 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 this will, because it's an explicit cast:
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 truncation behaviour you must use an explicit cast, and frankly I wish the SQL standard didn't specify that.
The better way to handle this is to be explicit about 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));