Today I Learned

A Hashrocket project

Postgres, varchar, and silent white space trimming

Given this table:

# create table initials (monogram varchar(2) primary key);

Insert a three character monogram.

# insert into initials(monogram) values ('BPD');
ERROR:  value too long for type character varying(2)

Ok, cool. Thanks Postgres. But what if my string ends in a space?

# insert into initials(monogram) values ('BP ');
INSERT 0 1

What? This caused me great pain when trying to do an insert. Here in my example I’ve used a CTE for brevity, but imagine you can’t see the input table, and it contains thousands of records. My insert went something like this:

with input as (
        select 'BD '::text as data
)
insert into initials(monogram)
select data from input where not exists (select 1 from initials where monogram = input.data)
;
ERROR:  duplicate key value violates unique constraint "initials_pkey"
DETAIL:  Key (monogram)=(BD) already exists.

A spot check based on the error message turns up no results:

select count(*) from input where data = 'BD';
count
-------
     0
(1 row)

But of course 'BD ' = 'BD', if you keep in mind that Postgres will silently truncate trailing whitespace when casting to a varchar.

Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, a series of free weekly PostgreSQL screencasts.