Postgresql not truncating overlength strings
create-table, postgresql, postgresql-9.3, sql-insert
Solution
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));
Problem
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?