Creating Tables Dum and Dee in PostgreSQL

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.

5 thoughts on “Creating Tables Dum and Dee in PostgreSQL

    1. (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 () );

      1. 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 nor UNION work correctly:

        -- Exception
        SELECT DISTINCT dee.* FROM dee
        
        -- Too many rows
        SELECT * FROM dee
        UNION
        SELECT * FROM dee
        
        -- This is fine, though:
        SELECT dee FROM dee
        UNION
        SELECT dee FROM dee
        
  1. 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.

    1. 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).

      Is that something you could test and state whether it works as expected?

      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

Leave a Reply