1 April 2005

The unbearable crapness of SQL

SQL is a dinosaur of a language, designed for the bad old days when computers enforced a fixed size and format for every kind of data, everything was upper case, and if you didn’t like it you went back to using paper. After all, disk space and CPU time were expensive, so you didn’t want people wasting them with pesky unaltered real-world information.

As such, SQL doesn’t have variable-length strings. Oh, sure, it has VARCHAR as well as CHAR, but VARCHAR only gives you a string that can be any size up to some fixed length. That’s as opposed to CHAR, which is even dumber, padding out all your strings to a fixed length. So all you gain from VARCHAR is the ability to get “John”, “Smith” rather than “John            ”, ”Smith           ” as output.

So, what happens if Mr Apu Nahasapeemapetilon suddenly joins the company, and your SURNAME VARCHAR(16) overflows? If you’re running MySQL, the name is silently truncated, and your database outputs garbage. If you’re running PostgreSQL, the database chokes and your system falls over with a run-time error, which is what the SQL standard apparently says should happen. So, would you like your leg amputated with a bread knife, or a rusty hacksaw blade?

Let’s suppose that you want to use PostgreSQL, so that you can have advanced features like, oh, the database actually remaining consistent if there’s a crash, and constraints that actually do something. Let’s also suppose that you don’t care too much about data being truncated, so long as you don’t personally have to deal with a support call every time some idiot decides to get creative with a product name.

Now you have a problem, because the program that’s feeding information into the database needs to know how much data it can put in each field without the database choking. The database knows this, of course; it’s part of the schema you used to set up the tables in the first place. But you don’t want the information in two places, because then if you change it in one place, you need to remember to change it in the other.

So, what’s the solution? Surely there has to be one?

© mathew 2017