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;
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:
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
SELECT dee FROM dee UNION SELECT dee FROM dee
When we use
INTERSECT, however, clearly, we’re running in a bug. Both of these queries return the same result:
SELECT * FROM dee EXCEPT SELECT * FROM dee SELECT * FROM dee INTERSECT SELECT * FROM dee
The same result as the
In any case: Every database schema should have these. Much more powerful than Oracle’s
DUAL table. With this, have a nice weekend!