I agree with everything said by a_horse_with_no_name, and I generally agree with Erwin's comment advice:
No, char is inferior (and outdated). text and varchar perform (almost) the same.
With one minor exception, the only time I use
char() is when I want the meta-data to say this MUST have have x-characters. Though I know that
char() only complains if the input is over the limit, I'll frequently protect against underruns in a
CHECK constraint. For example,
CREATE TABLE foo (
x char(10) CHECK ( length(x) = 10 )
INSERT INTO foo VALUES (repeat('x', 9));
I do this for a few reasons,
char(x) is sometimes inferred with schema-loaders as being a fixed-width column. This may make a difference in a language that is optimized for fixed-width strings.
- It establishes a convention that makes sense and is easily enforced. I can write a schema-loader in a language to generate code from this convention.
Need an example of where I may do this,
- Two-letter state abbreviations, though because this list can be enumerated, I'll typically do it with an
- Vehicle Identification Numbers
- Model Numbers (of fixed size)
Notice some people may be uncomfortable with the incongruity of error messages on both sides of the limit, but it doesn't bother me
test=# INSERT INTO foo VALUES (repeat('x', 9));
ERROR: new row for relation "foo" violates check constraint "foo_x_check"
DETAIL: Failing row contains (xxxxxxxxx ).
test=# INSERT INTO foo VALUES (repeat('x', 11));
ERROR: value too long for type character(10)
Moreover, I think the above suggestion fits really well with a convention of almost always use
text. You ask about
varchar(n) too. I never use that. At least, I can't remember the last time I used
- If a spec has a static-width field that I trust, I use
- Otherwise, I use
text which is effectively
varchar (no limit)
If I found a spec that had variable-length text-keys that were meaningful and that I trusted to have a constant max-length, I would use
varchar(n) too. However, I can't think of anything that fits that criteria.
Related Q & A: