Postgres Text数据类型截断问题

Question

我使用的是Postgres 9.3。当我在数据类型为"text"的列中插入一个大字符串时，它会被截断为256个字符。

我很困惑。Postgres的文档说，"文本"数据类型是可变的，长度不受限制。

请帮忙！

Answer 1

您的应用程序框架正在截断这些值。PostgreSQL从不截断text或varchar的值。它将截断传统character空白填充类型的值，但仅限于显式强制转换。

CREATE TABLE testtruncation(
    text_unlimited text,
    text_limit255 text check (length(text_limit255) <= 255),
    varchar_unlimited varchar,  
    varchar_255 varchar(255), 
    char_nosize character,
    char_255 character(255)
);
regress=> insert into testtruncation(text_unlimited) values ('abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789');
INSERT 0 1
regress=> insert into testtruncation(text_limit255) values ('abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789');
ERROR:  new row for relation "testtruncation" violates check constraint "testtruncation_text_limit255_check"
DETAIL:  Failing row contains (null, abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEF..., null, null, null, null).
regress=> insert into testtruncation(varchar_unlimited) values ('abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789');
INSERT 0 1
regress=> insert into testtruncation(varchar_255) values ('abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789');
ERROR:  value too long for type character varying(255)
regress=> insert into testtruncation(char_nosize) values ('abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789');
ERROR:  value too long for type character(1)
regress=> insert into testtruncation(char_255) values ('abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789');
ERROR:  value too long for type character(255)

唯一会被截断的时间值是显式转换为character(n):

regress=> SELECT CAST('abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789abcdefghijklmnopABCDEFGHIJKLMNOP0123456789' AS character(20));
        bpchar        
----------------------
 abcdefghijklmnopABCD
(1 row)

但最好使用substring，而不是这样，在任何情况下，在你不知情的情况下，这都不太可能发生。