As a SQL Server DBA I was fortunate enough to encounter the idea of a numbers table — principally due to the work of Itzik Ben Gan (e.g. in books like ‘Inside Microsoft SQL Server 2005: T-SQL Querying’)… but I’ve not found many internet resources for a numbers tables in PostgreSQL. I’m working with Postgres at the moment, so I thought it would be good to provide a little introduction.
What is a Numbers Table?
Quite simply; a table with sequential integer numbers in; typically starting from 0 or 1 (or perhaps, both, as we shall see)… we can then use this table to join to other tables to provide useful functionality.
I was reminded earlier today of the famous Donald Rumsfeld press conference in which he said:
“Reports that say that something hasn’t happened are always interesting to me, because as we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns – the ones we don’t know we don’t know. And if one looks throughout the history of our country and other free countries, it is the latter category that tend to be the difficult ones.”
There was a considerable uproar in the press at the time, which mostly seemed to focus on these words as if they were a radical new idea… but I think I just considered his words to represent a broad concept that seemed very familiar… probably so familiar in fact that no-one ever says them!
Some of the earlier posts on this blog use ‘we’ and ‘us’ to hint or suggest that 42 IT Solutions has a team of like-minded people working for a better future in IT. I’m sure there were any number of clues that this was ‘enterprise-speak’; but as things stand 42 IT Solutions is effectively only one person: me, Nigel Rheam.
Received an email from Amazon today, this email explaining that delivery of my parcel was delayed demonstrates the dangers of duplicate data, I think:
29 September 2016 is actually a Thursday!
The duplication I am thinking of is that in addition to providing the date; 29/09/2016, they have also chosen to provide a refinement; ‘Friday’.
The client had a stalled project with many changes to an existing system; there were several areas of new functionality, but they were not completely trusted. There was considerable concern regarding the reliability and performance of the database, and in some senses the database was seen as the whole problem. Not only had this set of changes stalled, but minor fixes had also stacked up waiting to be released and all were dependent upon the main project.