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!

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!

5 thoughts on “Creating Tables Dum and Dee in PostgreSQL

    • (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 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.

    • 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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s