The one thing I like to do is challenge PostgreSQL - and readers - to the extreme. A while ago I posted about the Beer o’clock. Building Zoé was another interesting example. And at some point I blog about Dancing with the Stars as well. Today it’s all about nothing. Empty. Where is all the data gone?
A while ago I posted this screenshot on Twitter, and as far as I can tell no one found the correct answer.
The screenshot looks like there’s nothing. Empty names, schema name, role name, database name, even no data. And yet this all comes together and somehow works. Let’s dig in.
It all comes down to having something, compared to having nothing. Even if you can’t see it. My goal was to have as many "" as possible.
In computers, one can have an empty whitespace character, also called zero-width space. For the computer, by all means, that is a regular character. Just that when printed out there is nothing. The rest of the exercise is finding ways to cleverly use this for names and data.
How to get a zero-width space?
When building this example in my editor, before pasting it into a
psql shell, I was using the emptycharacter.com website. It has a button which copies one empty whitespace into the clipboard. There are other ways, and websites, it’s just that this one worked well for me.
How to build the DDL?
Most objects names in my example are exactly one zero-with space wide. This includes the database name, the role name, the schema name, and the type name:
However the table name is two zero-width whitespaces wide: otherwise it conflicts with the previously created type, which is already one zero-width space wide. Both objects are in the zero-width whitespaces schema, because the search_path is set to that schema.
So far, so empty.
Get me more emptyness
It’s not enough to have nothing as structure, I also want nothing as data. In my example I create 5
INSERT statements which apparently all insert nothing into the table, and yet don’t violate the
At this point you might have guessed it: the 5
INSERT statements insert 5 different amounts of zero-width whitespaces. The
ROW() constructor is necessary because I previously created a new type based on a basic
TEXT type. Details about such types I explain in my “Advanced Datatypes” talk.
SELECT the data it looks like there is nothing in there. However the
LENGTH() function gives it away and shows that there are up to 5 characters in it.
All in all this was rather easy to build, once I figured out the part with the non-printable zero-width whitespace. The only problem was the tablename which could not be the same as the type name (which I wanted in there because otherwise the \d will show
TEXT and not a seemingly empty type).
Here is the full source code for this example: