Use TEXT instead of VARCHAR with PostgreSQL
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-0.1.7.2-GpYhYBkrTMb6s8gal7hgyK:Database.Selda.PostgreSQL.Encoding
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.