My PostgreSQL database is empty!

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:

1
2
3
4
5
6
7

CREATE ROLE "‎" WITH login;
CREATE DATABASE "‎" WITH OWNER "‎";
\c "‎"

CREATE SCHEMA "‎";
SET search_path TO "‎";
CREATE TYPE "‎"."‎" AS ("‎" TEXT);

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.

1
2
3
4

CREATE TABLE "‎"."‎‎" ("‎" "‎"."‎" UNIQUE);
ALTER TABLE "‎"."‎‎" OWNER TO "‎";
\dt
\d+ "‎"."‎‎"

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

=# \dt
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
       |    | table |
(1 row)

=# \d+ "".""
                                      Table "."
 Column | Type | Collation | Nullable | Default | Storage  | Stats target | Description
--------+------+-----------+----------+---------+----------+--------------+-------------
       | ""  |           |          |         | extended |              |
Indexes:
    "__key" UNIQUE CONSTRAINT, btree ("")
Access method: heap

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 UNIQUE constraint.

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.

1
2
3
4
5

INSERT INTO "‎"."‎‎" VALUES (ROW('‎'));
INSERT INTO "‎"."‎‎" VALUES (ROW('‎‎'));
INSERT INTO "‎"."‎‎" VALUES (ROW('‎‎‎'));
INSERT INTO "‎"."‎‎" VALUES (ROW('‎‎‎‎'));
INSERT INTO "‎"."‎‎" VALUES (ROW('‎‎‎‎‎'));

When I 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.

1
2
3
4
5
6
7
8
9

=# SELECT "", LENGTH(("")."") FROM ""."";
        | length
---------+--------
 ()     |      1
 ()    |      2
 ()   |      3
 ()  |      4
 () |      5
(5 rows)

Summary

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).

Full example

Here is the full source code for this example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

\set ON_ERROR_STOP on
\c postgres
-- DROP DATABASE "‎";
-- DROP ROLE "‎";

CREATE ROLE "‎" WITH login;
CREATE DATABASE "‎" WITH OWNER "‎";
\c "‎"

CREATE SCHEMA "‎";
SET search_path TO "‎";
CREATE TYPE "‎"."‎" AS ("‎" TEXT);
CREATE TABLE "‎"."‎‎" ("‎" "‎"."‎" UNIQUE);
ALTER TABLE "‎"."‎‎" OWNER TO "‎";
\dt
\d+ "‎"."‎‎"

-- INSERT INTO "‎"."‎‎" VALUES (ROW(''));
INSERT INTO "‎"."‎‎" VALUES (ROW('‎'));
INSERT INTO "‎"."‎‎" VALUES (ROW('‎‎'));
INSERT INTO "‎"."‎‎" VALUES (ROW('‎‎‎'));
INSERT INTO "‎"."‎‎" VALUES (ROW('‎‎‎‎'));
INSERT INTO "‎"."‎‎" VALUES (ROW('‎‎‎‎‎'));

SELECT "‎", LENGTH(("‎")."‎") FROM "‎"."‎‎";