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)?

Solution 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);

Solution 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.

Comments

[jarlh]

But is a text column with length check constraint really more efficient than nvarchar?

[Vivek S.]

@jarlh Postgres doesn't have nvarchar

[IMSoP]

@jarlh See postgresql.org/docs/current/interactive/datatype-character.h‌​tml "Tip: 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."

[Admin]

thank you for asking this question Dr. Tobias

[Tobias]

Yes, of course the constraint should have a name; that's why I said "when adding with a tool like pgAdmin" (which builds a name following a schema which is likely useful to follow).

[Tobias]

Does it matter whether I use char_length or length? I meanwhile applied constraints using the latter ...

[IMSoP]

@Tobias Any tool you use will create it with some name, generated according to some algorithm. However, it's not guaranteed to use the same name when you run in different places at different times. I honestly can't think of a reason not to use your own name.

[Tobias]

I don't need such guarantee. I'd use the interactive tool once, and then I'd use the SQL code it generated to apply the same changes in other places, if needed, or store it in files.

[IMSoP]

@Tobias That's a reason why it doesn't matter not choosing a name, but it's not on its own a reason not to choose a name. Literally the only reason I can think of not to is to save 10 seconds of typing, and that's a pretty lame reason.

[deFreitas]

ERROR: syntax error at or near "namechk" Looks like you can't specify constraint name when specify it at table creation

[gmaliar]

@deFreitas which version of PostgreSQL are you referring to?

[deFreitas]

At version PostgreSQL 10.3 CREATE TABLE names ( name text CHECK namechk (char_length(name) <= 255)) don't work but CREATE TABLE names ( name text CHECK (char_length(name) <= 255)) do

[gerardw]

If you consistently use the same tool and let it name things, you don't have to worry about duplicating an existing name.

[Sampson Crowley]

@deFreitas you're syntax is incorrect. should be CONSTRAINT namechk CHECK, not CHECK namechk (...)

[vaer-k]

@Tobias char_length will measure the length in characters; length will measure in bytes stackoverflow.com/questions/1734334/mysql-length-vs-char-len‌​gth