Hacker News Reader

Comment by billpg | Hacker News Reader

Dear databases, please don't get hung up about string lengths when dealing with UTF8.

If I ask for a UTF8 string with a max-length of 100, please don't apply the worse case scenario and allocate space for 100 emojis. Please give me a box of 100 bytes and allow me to write any UTF-8 string that can fit into 100 bytes in there.

100 ASCII characters. 20 emojis. Any mixture of the two.

If I ask for UTF-8, it'll be because I'd like to make advantage of UTF-8 and I accept the costs. If that means I can't quickly jump to the character at index 84 in a string, no problem, I've accepted the trade-off.

View on HN

PostgreSQL recommends you use the text type everywhere, and use CHECK constraints if you really really really need to limit a field’s length on the database level. (VAR)CHAR is considered a code smell. So many fields are artificially limited to 255 bytes because that used to make a performance difference on MySQL 15 years ago.

I feel PostgreSQL should push that as a benefit of their system. I read your comment and said "Where have you been all my life?"

"Still using strings with fixed limits like you're a C programmer in the 80s? Use PostgreSQL."

Yes! This and other charset oddities almost make me want to store all strings as varbinary and handle any conversions to/from a string type to utf8 bytes client side. It makes ad-hoc query access from a shell much more annoying though.

I wonder if you can do LIKE with a VARBINARY value.

"SELECT * FROM Users WHERE Name LIKE @namePattern" @namePattern = UTF8.GetBytes("%billpg%")

More like "100 ASCII characters. Unknown number of emojis."

You'll get codepoints truncated if you use bytes.

I would hope a database engine would throw an error instead of truncating bytes.

Why would the database "allocate space" unless it was truly necessary, in any case? While char(n) works that way, most applications don't actually use it, no matter the underlying DB system.

You're literally asking them to break the SQL standard. The meaning of N in CHAR(N) is the number of characters.

Reply on news.ycombinator.com