3 ms·

I learned a few things, despite having used Postgres for many years: (1) the filter clause with aggregates to more succinctly pivot tables, (2) the not-valid cl

I learned a few things, despite having used Postgres for many years: (1) the filter clause with aggregates to more succinctly pivot tables, (2) the not-valid clause with check constraints to apply only to new data while leaving old rows alone, and (3) something about inverse-distribution functions --- I actually didn't learn these yet but bookmarked it for later, because it's too hard to read on a Saturday ;) It really is amazing how much you can prepare your data before it leaves your database, or how raw you can leave your data before sending it there, letting Postgres take care of it. I wonder how much middle code would be saved (PHP, Python, Perl, etc.) if more programmers knew more SQL, and especially if they chose Postgres more often. I also wonder why SQL seems to be the language that developers know least, even though it seems like it should be easiest, with its declarative, natural-sounding syntax. (I admit that the naturalness is deceptive. Just because "select color, count(1) from favorites where age < 13 having count(1) > 3 order by 1" sounds like English doesn't mean you can tell exactly what it will do before reading the manual.)

barrkel 9y ago
The language isn't particularly compositional (why both HAVING and WHERE? why must we repeat SELECT column expressions in HAVING, ORDER BY etc?), and the "natural" grammar means it usually isn't very discoverable in an editor. The grammar puts operations out of order (SELECT should be last! And usable multiple times, to simplify expressions!), and multi-word clauses with contextual meaning are harder to complete effectively in a syntax-aware editor. SQL is also a bit of a two-edged sword. You can do very powerful things with it that are infeasible in normal code, because they'd require round-tripping enormous quantities of data. OTOH it's easy to write code that performs and scales dreadfully, which will normally have knock-on effects across the whole production system, which is pretty scary. There are usually multiple ways of writing what is semantically the same query, that perform quite differently, depending on how smart your target planner is. Despite the language being nominally declarative, you still need to keep in mind potential execution plans as you write the code, and verify on reasonably sized data sets that the plan you expected, or one better than it, was selected. It's been my experience that most developers don't have cost model estimation baked into their brains, so the more advanced cost estimation you need to do with a declarative language is even rarer.

barrkel 9y ago

The language isn't particularly compositional (why both HAVING and WHERE? why must we repeat SELECT column expressions in HAVING, ORDER BY etc?), and the "natural" grammar means it usually isn't very discoverable in an editor. The grammar puts operations out of order (SELECT should be last! And usable multiple times, to simplify expressions!), and multi-word clauses with contextual meaning are harder to complete effectively in a syntax-aware editor. SQL is also a bit of a two-edged sword. You can do very powerful things with it that are infeasible in normal code, because they'd require round-tripping enormous quantities of data. OTOH it's easy to write code that performs and scales dreadfully, which will normally have knock-on effects across the whole production system, which is pretty scary. There are usually multiple ways of writing what is semantically the same query, that perform quite differently, depending on how smart your target planner is. Despite the language being nominally declarative, you still need to keep in mind potential execution plans as you write the code, and verify on reasonably sized data sets that the plan you expected, or one better than it, was selected. It's been my experience that most developers don't have cost model estimation baked into their brains, so the more advanced cost estimation you need to do with a declarative language is even rarer.