Postgresql 不截断超长字符串

Question

根据documentation , 比 character varying 或 VARCHAR 指定的字符串长的字符串应该被截断:

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.)

但我无法让它工作。现在文档确实说必须“明确地”将值转换为字符变化，所以也许我错过了。下面是一个简单的测试表:

create table test1(
tval character varying(20));

以下失败并显示错误:对于类型字符 varying(20) 值太长

insert into test1 values 
('this is a super long string that we want to see if it is really truncated');

我怎样才能让它工作？

Answer 1

这不会被截断，因为它只是一个赋值:

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');

但这会，因为它是一个显式转换:

insert into test1 values (CAST('this is a super long string that we want to see if it is really truncated' AS varchar(20)));

要获得截断行为，您必须使用显式转换，坦率地说，我希望 SQL 标准没有指定这一点。

处理这个问题的更好方法是明确说明您想要什么:

insert into test1 values (left('this is a super long string that we want to see if it is really truncated', 20));