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!
| +
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 |
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 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.
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
DISTINCTnorUNIONwork 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