The SQL Language’s Most Missing Feature

SQL is also awesome in many ways. We can write out the most complex truths and facts and have the database tell us the answer in no time. But the SQL language is arguably the most beautiful programming language out there. It has so many caveats that people like me get ridiculously rich selling consulting services … Continue reading The SQL Language’s Most Missing Feature

Recursive SQL for Data Normalisation

Recursive SQL can be awesome, although a bit hard to read in its SQL standard beauty. Let's assume you have some aggregated data with dates and a number of events per date: | DATE | COUNT | |--------------------------------|-------| | October, 01 2013 00:00:00+0000 | 2 | | October, 02 2013 00:00:00+0000 | 1 | | … Continue reading Recursive SQL for Data Normalisation

PostgreSQL Syntax is a Mystery Only Exceeded by its Power

I just ran across this rather powerful PostgreSQL example statement from the manual. It reads Increment the sales count of the salesperson who manages the account for Acme Corporation, and record the whole updated row along with current time in a log table: WITH upd AS ( UPDATE employees SET sales_count = sales_count + 1 … Continue reading PostgreSQL Syntax is a Mystery Only Exceeded by its Power

Emulating the SQL standard derived column list

Derived column lists are a fine feature, if your database supports them. The SQL:2008 standard specifies 7.6 <table reference> <table reference> ::= <table factor> | <joined table> <table factor> ::= <table primary> [ <sample clause> ] <table primary> ::= <table or query name> [ [ AS ] <correlation name> [ <left paren> <derived column list> … Continue reading Emulating the SQL standard derived column list