I have a table TABLE1 with 400,000 row (by now) that has 20 columns.

I have a column DETAIL that usually stores 100 to 300 characters, that's why its size is 1024, but I have an special case. I need to store 1900 chars for one row only.

The solution that I found is creating another column, DETAIL2 to store remaining characters, but I'm afraid that this will cause my table size to increase a lot (just one row would have DETAIL2, others will be NULL).

Is this the right solution or is there a better way to store the special case?

  • Just increase the size of the column or change it to text Aug 26 '20 at 12:38

Change the datatype of Detail to VARCHAR(2000) or TEXT*. This will be enough to handle your 1900 length value plus a little padding for future exceptions. This will add no additional overhead beyond the additional storage needed for that particular row.

If you add another column you will add 1 byte to all of the rows (which isn't a big deal) but now you have to contend with two columns with different names but the same purpose.

Postgres documentation explains storage requirements in more detail: https://www.postgresql.org/docs/current/datatype-character.html

*The TEXT datatype is not ANSI compliant, if you do use it understand the implications.

  • I'll upvote if you change your recommendation to text. It makes no sense to set an arbitrary limit: next week, someone will want to store 2000 characters. Aug 26 '20 at 6:40
  • I will update my answer to "or TEXT". VARCHAR is compliant with SQL standards, TEXT is not.
    – bbaird
    Aug 26 '20 at 12:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.