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;
Nothing!
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:
Caveat
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:
, which is surprising, because when we nest the record with the following useful PostgreSQL specific syntax, we’ll get a single nested empty row:
SELECT dee FROM dee UNION SELECT dee FROM dee
When we use EXCEPT
or 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 UNION
:
In any case: Every database schema should have these. Much more powerful than Oracle’s DUAL
table. With this, have a nice weekend!
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.
Also, neither
DISTINCT
norUNION
work correctly: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