News for hackers. Stuff that matters.
Re:Common mistakes in PostgreSQL by shawnz on Friday May 03, 2019 @10:27AM (#19818739)
I don't think the problem is so much about predicting the encoding that will be used. Let's say you KNOW that non-ascii characters will be used in the field, but only very occasionally. That means the field has to be 4x as wide as it would have been otherwise even if non-ascii characters will almost never be used.
Re:Common mistakes in PostgreSQL by Retra on Friday May 03, 2019 @11:16AM (#19819152)
If you knew the characters were ASCII, you wouldn't want unicode support in the first place. It's pure overhead.
Re:Common mistakes in PostgreSQL by kbenson on Friday May 03, 2019 @11:30AM (#19819281)
But you also wouldn't want char(n), because as the article notes:

char(n) doesn't reject values that are too short, it just silently pads them with spaces. So there's no actual benefit over using text with a constraint that checks for the exact length.

So even if you're using pure ASCII, you're supposedly better off just setting the character encoding appropriately and using a text or non-limited varchar field.

That said, I thought the main benefit of a char field (and its padding) was that you could use it in select instances where is was useful to have specific length records. E.g. if all the other fields of the table are fixed length, every record can be fixed length if you use a char(n). I'm not sure if that's a benefit or not to modern databases anymore though.

Re:Common mistakes in PostgreSQL by bunderbunder on Friday May 03, 2019 @01:19PM (#19820268)
Well, it depends.

Some RDBMS's don't store the string's length with each value for fixed-width character fields. So a fixed-length field may still be preferable if the space lost to padding amounts to less than the space lost to overhead.

That distinction doesn't apply to PostgreSQL, because it stores a length along with the value even for fixed-with columns.