Re: A simple question about text fields

On 17 Jun 2021, at 1:08, Tom Lane wrote:

> Martin Mueller <martinmueller(at)northwestern(dot)edu> writes:
>
>> Are there performance issues with the choice of 'text' vs. varchar and some character limit? For instance, if I have a table with ten million records and text fields that may range in length from 15 to 150, can I expect a measurable improvement in response time for using varchar(150) or will text do just or nearly as well.
>
> There is no situation where varchar outperforms text in Postgres.
> If you need to apply a length constraint for application semantic
> reasons, do so ... otherwise, text is the native type. It's
> useful to think of varchar as being a domain over text, though
> for various reasons it's not implemented quite that way.
>
This reminds of my days converting from MySQL to PostgreSQL. MySQL, along with other databases, seemed to have a strong preference for setting a length on character strings. And all this from before the advent of UTF encoding which has made the concept of string ‘length’ very messy.

Database guru and SQL author Joe Celko asserts in his ’SQL for Smarties’ that if he finds a text field without a length limit he will input the Heart Sutra (presumably in ASCII :) to demonstrate the design error. (Of course he is ignoring the potential for this input to help the database achieve inner consistency. :) . But taking Joe’s central point there do seem to be grounds for restricting user input text fields to a reasonable length according to the business need… if only to limit the damage of a cat sitting on the keyboard.

My approach is to define such fields as ‘text’ and set a constraint using char_length(). This allows PG to do the business with the text in native form, and only imposes the cost of any length check when the field is updated… best of both worlds.

Gavan Schneider
——
Gavan Schneider, Sodwalls, NSW, Australia
Explanations exist; they have existed for all time; there is always a well-known solution to every human problem — neat, plausible, and wrong.
— H. L. Mencken, 1920