postgresql - Postgres 文本数据类型截断问题

Question

我正在使用 Postgres 9.3。当我将一个大字符串插入数据类型为“文本”的列时，它总是被截断为 256 个字符。

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

请帮忙!

Answer 1

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

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 总是更好，无论如何，这不太可能在您不知情的情况下发生。