Hacker News Reader

Comment by pilif | Hacker News Reader

> VARCHAR(16) will enforce a maximum size (through truncation)

Thankfully, real databases won't do that. They will blow up and leave the application to do the truncation.

If there is one feature I want a database to have, it's that I'm guaranteed to read the data back that I stored there.

That also means that if I store "foobar" in a varchar(3) field that I don't want to read back "foo" with no way to ever find out that I didn't actually ever store foo there to begin with.

Storing a 6 character string into a field dimensioned for three characters is an error and not something that would warrant silent data corruption.

View on HN

If you read the PostgreSQL docs you'll see that it only truncates spaces and throws an error if any non-space data is present. Apparently, this behavior is required by the SQL specifications.

So, in your example of storing "foobar" in a varchar(3) field, PostgreSQL will throw an error and not store the data. You could however store "foo " in that same field without any problems and you would get back "foo" in return.

I know that Postgres does this the right way. I was referring to the initial comment that was suggesting that truncation happens, but of all the databases I know, only MySQL does that and it's totally wrong IMHO

MySQL will silently truncate it without throwing an error however. And even when you know about it, you'll still get bitten occasionally and SERIOUSLY kick yourself when you realise the error.

Postgres is better for the little things, but when it goes wrong it tends to be harder to find the reason why. At least in my experience. That said, I'll still reach for Postgres over MySQL these days.

MySQL will silently truncate it without throwing an error however.

It will indeed, for folks that prefer that or haven't looked into enabling alternate behavior. Set STRICT_TRANS_TABLES (for InnoDB) or STRICT_ALL_TABLES (for any storage engine) in MySQL's config and it'll throw the following error instead of silently truncating data:

[Err] 1406 - Data too long for column '[some column]' at row [XYZ]

Does that help?

Reply on news.ycombinator.com