I was nerd-sniped:
[Dee] is the relation that has no attributes and a single tuple. It plays the role of True.
[Dum] is the relation that has no attributes and no tuples. It plays the role of False.
Quite academic? Sure. But the awesome PostgreSQL database can model these beasts! Check this out:
-- Creating the tables: CREATE TABLE dum(); CREATE TABLE dee(); INSERT INTO dee DEFAULT VALUES; -- Making sure the tables stay this way: CREATE FUNCTION dum_trg () RETURNS trigger AS $$ BEGIN RAISE EXCEPTION 'Dum must be empty'; END $$ LANGUAGE plpgsql; CREATE TRIGGER dum_trg BEFORE INSERT OR UPDATE OR DELETE OR TRUNCATE ON dum FOR EACH STATEMENT EXECUTE PROCEDURE dum_trg(); CREATE FUNCTION dee_trg () RETURNS trigger AS $$ BEGIN RAISE EXCEPTION 'Dee must keep one tuple'; END $$ LANGUAGE plpgsql; CREATE TRIGGER dee_trg BEFORE INSERT OR UPDATE OR DELETE OR TRUNCATE ON dee FOR EACH STATEMENT EXECUTE PROCEDURE dee_trg();
And we’re done!
Check this out:
SELECT * FROM dum;
It’s hard to display this, of course. Imagine zero rows with zero columns. Or this:
SELECT * FROM dee;
One row with no columns!
| + |
Just to be sure:
SELECT 'dum' AS t, count(*) FROM dum UNION ALL SELECT 'dee' AS t, count(*) FROM dee;
And we’ll get, nicely:
|t |count| |---|-----| |dum|0 | |dee|1 |
Note, it is worth mentioning that there are some flaws / “bugs” (in my opinion). This query:
SELECT DISTINCT * FROM dee;
… yields an error:
ERROR: SELECT DISTINCT must have at least one column SQL state: 42601
I suspect the author(s) of the
DISTINCT operation have overlooked a nice feature here.
UNION on the other hand doesn’t work correctly either. It doesn’t remove duplicates (but also doesn’t complain):
SELECT * FROM dee UNION SELECT * FROM dee
This yields two rows with zero columns:
| + | |
which is surprising, because when we nest the record with the following useful PostgreSQL specific syntax…
SELECT dee FROM dee UNION SELECT dee FROM dee
We’ll get a single empty row (it used to produce a nested empty row in previous PostgreSQL versions, which was probably a bug)
|dee| |---| | |
In any case: Every database schema should have these.