Creating Tables Dum and Dee in PostgreSQL
If SQL is so powerful, why can't we represent Dum and Dee tables in SQL? Or can we? See what it takes to create them in PostgreSQL.
Join the DZone community and get the full member experience.Join For Free
I was nerd-sniped:
[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;
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:
Every database schema should have these. Much more powerful than Oracle’s
DUAL table. With this, have a nice week!
Published at DZone with permission of Lukas Eder. See the original article here.
Opinions expressed by DZone contributors are their own.