Use TEXT instead of VARCHAR with PostgreSQL

Posted on

In recent project I’m migrating from postgresql-simple to Selda. I’m happy with postgresql-simple, but really wanted some extra type-safety, and a much more readable query syntax. Along the way I was hit by an error:

BUG: result with unknown type oid: Oid 1043
CallStack (from HasCallStack):
  error, called at src/Database/Selda/PostgreSQL/Encoding.hs:72:24 in selda-postgresql-

Turn out the column type I was using is VARCHAR (Oid 1043) is not supported by Selda. I’m surprised because VARCHAR is the first thing I learned when I was studying SQL. I looked at the source code and the only textual data type supported is TEXT (Oid 25). Of course before submitting a bug report I searched around and, to my surprise TEXT is the right choice, from the PostgreSQL documentation:

There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead.

I’ve always thought VARCHAR is for small text and TEXT is for longer form which you usually treat as a blob and don’t want to index. I guess I will start using TEXT exclusively from now on.