Alex Rivera | Logout

Change varchar to boolean in PostgreSQL

Asked 2012-10-10T16:41:55.960
12

I've started working on a project where there is a fairly large table (about 82,000,000 rows) that I think is very bloated. One of the fields is defined as:

consistency character varying NOT NULL DEFAULT 'Y'::character varying

It's used as a boolean, the values should always either be ('Y'|'N').

Note: there is no check constraint, etc.

I'm trying to come up with reasons to justify changing this field. Here is what I have:

  • It's being used as a boolean, so make it that. Explicit is better than implicit.
  • It will protect against coding errors because right now there anything that can be converted to text will go blindly in there.

Here are my question(s).

  • What about size/storage? The db is UTF-8. So, I think there really isn't much of a savings in that regard. It should be 1 byte for a boolean, but also 1 byte for a 'Y' in UTF-8 (at least that's what I get when I check the length in Python). Is there any other storage overhead here that would be saved?
  • Query performance? Will Postgres get any performance gains for a where cause of "=TRUE" vs. "='Y'"?
postgresql database-design query-optimization storage postgresql-9.1
Edit
Report

1 Answer

3

Neither storage size nor query performance will be significantly better switching from a single VARCHAR to a BOOLEAN. Although you are right that it's technically cleaner to use boolean when you are talking about a binary value the cost to change is probably significantly higher than the benefit. If you're worried about correctness then you could put a check on the column, for example

ALTER TABLE tablename ADD CONSTRAINT consistency CHECK (consistency IN ('Y', 'N'));
answered 2012-10-12T21:26:01.570

Your Answer