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