I was nerd-sniped:
So tables dee and dum are two theoretical tables in SQL, and they can be characterised as such:
[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.
5 thoughts on “Creating Tables Dum and Dee in PostgreSQL”
Nice but you can’t add a primary key in them. So nothing stops you from adding another (or many more) row in dee!
(I obviously missed the triggers in the first read).
Still being able to have this would be nice:
CREATE TABLE dee( CONSTRAINT dee_pk PRIMARY KEY () );
Yes, that would be great, indeed! And, a foreign key from a dee_backup table to dee would be interesting as well.
Cool! I didn’t expect that any SQL product would allow us to create tables DEE and DUM. You refer to them as “tables… IN SQL”; are they, in fact, defined in the SQL Standard? I know them from relational theory, but I wasn’t aware that they “exist” in SQL.
I don’t “do” PostgreSQL or I could have checked for myself… the fundamental property of DEE is that the Cartesian join of any other table with DEE equals that other table. Is that something you could test and state whether it works as expected? I believe that would be more important than correct behavior with regard to UNION.
I’m not sure what you mean by being “defined in the SQL standard”. As I mentioned in the article, I’ve managed to emulate them in PostgreSQL, to some extent (they don’t behave entirely correctly).
Why don’t you just do it yourself? :) If you don’t want to install PostgreSQL, you can use this public installation here: http://sqlfiddle.com