CODEWITHSUNDEEP
postgresqlsql-insertcreate-tablepostgresql-9.3
WraithWireless
WraithWireless

Reputation: 674

Postgresql not truncating overlength strings

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?

Upvotes: 4

Views: 6326

Answers (2)

Constantine Ch
Constantine Ch

Reputation: 170

There is another solution, not to specify the n when creating the column: If you desire to store long strings with no specific upper limit, use text or character varying without a length specifier, rather than making up an arbitrary length limit.)

Upvotes: 0

Craig Ringer
Craig Ringer

Reputation: 324731

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

Upvotes: 7

Related Questions