How to add a length constraint to a text field

Question

It seems it is preferable to use the TEXT datatype when using PostgreSQL (or some other databases which support it as well) rather than character varying(NN) because there is no performance penalty, and the maximum possible length can be adjusted by dropping and re-applying constraints without effecting any views etc. which use the field.

But, how is this constraint applied (SQL code)?

Answer 1

When you create the table you can do something of this sort,

CREATE TABLE names (   name text CONSTRAINT namechk CHECK (char_length(name) <= 255) ) 

(namechk is just a name for the constraint)

Same goes for ALTER TABLE for example:

ALTER TABLE names   ADD CONSTRAINT namechk CHECK (char_length(name) <= 255); 

Answer 2

There are really three things here:

1. Is it better to use text + a check constraint, or varchar(N)?
2. How would you write an appropriate check constraint?
3. Should you name your constraints, or let an automatic name be assigned?

Answers:

1. A varchar(N) will be more obvious when inspecting the schema, and what developers coming from other DBs will expect to see. However, as you say, it is harder to change later. Bear in mind that applying a new/modified check constraint is not free - all existing rows must be checked against the constraint, so on a large table, a lot of reading is necessary.
2. The syntax for a check constraint is CONSTRAINT name CHECK (condition) (or just CHECK (condition) and Postgres itself will come up with a name) in a CREATE TABLE statement, and ALTER TABLE table_name ADD CONSTRAINT name CHECK (condition);. condition would be an expression using an appropriate string function, e.g. char_length(foo) <= 255.
3. Adding a name for a constraint is very useful if you want to manage the constraint later. In particular, since you're using this for flexibility, you may well want to write code to drop and recreate the constraint with a new length. If you only ever use graphical tools, this isn't a problem, but managing multiple servers (e.g. development, testing, and production copies) becomes much easier if you can script your changes. With a named constraint, this would like ALTER TABLE foo DROP CONSTRAINT ck_bar_length; ALTER TABLE foo ADD CONSTRAINT ck_bar_length CHECK ( char_length(bar) <= 100 ); I can't actually think of a disadvantage of naming your constraint.