Thursday, 1 April 2010

The Empty Set?

April 1st, so time to goof around a little.

I discovered recently that you can have a table called " ". That's one space character or ascii #32. Just make sure you enclose it in quotes. You can also have a table with no columns. So try

CREATE TABLE " " ();

which works fine. What does that look like?

postgres=# select * from " ";
--
(0 rows)

Can you have rows in this almost non-existent table?

INSERT INTO " " VALUES ();

fails, but let's try

INSERT INTO " " DEFAULT VALUES;

Works! And in fact you can insert multiple rows this way.

How about an index? Surely not?? Yep, we can build an index on it, even if it has no columns, as long as it references an immutable function with constant input

CREATE INDEX ON " " (exp(1));

The name of the index is " _exp_idx"! And for my coup de grace, yes, it can be a unique index

CREATE UNIQUE INDEX ON " " (exp(1));

which then enforces that there can only be a single row in our table (called " "). I've tried and failed to create an index that would enforce that our empty table called " " with no columns should have zero rows. Any takers?

So how about a Schema? Surely not? Yep.

CREATE SCHEMA " ";

and a table called " " in the " " schema? Oh yes.

CREATE TABLE " "." " ();

I also tried VACUUM VERBOSE " "." ";
INFO: vacuuming " . "
INFO: " ": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

Nothing practical comes out of that at all, just some fun. Needless to say, we only cover practical and useful things on 2ndQuadrant's courses...serious stuff.

4 comments:

  1. David Fetter1 April 2010 at 09:20

    Here's a table that must be empty:

    CREATE table " "(check(false));
    INSERT INTO " " DEFAULT VALUES;
    ERROR: new row for relation " " violates check constraint " _check"

    ReplyDelete
  2. Bill Karwin1 April 2010 at 10:25

    C. J. Date calls these relations TABLE_DUM and TABLE_DEE. TABLE_DUM has zero tuples of degree zero, TABLE_DEE has one tuple of degree zero.

    The reason these are important is to provide some identity relations with respect to join. Joining any table to TABLE_DEE returns the original table. Joining any table to TABLE_DUM returns TABLE_DUM. You can think of this as analogous to multiplying by 1 or 0.

    Unfortunately, standard SQL doesn't account for tuples of degree zero. So while it makes sense that relational theory includes these relations for completeness, SQL as written doesn't.

    ReplyDelete
  3. Simon Riggs1 April 2010 at 11:14

    Great comments guys, thanks.

    ReplyDelete
  4. Darren Duncan1 April 2010 at 19:49

    This may be April 1st, but supporting empty sets in the general case is a good feature to have, both for supporting tuples and relations with zero attributes, and for supporting keys ranging over zero attributes, and supporting selecting zero attributes and grouping by zero attributes, etc; placing a key constraint of zero attributes says that a relation may have either zero or one tuple.

    While I like the support for nullary tuples and relations, I find that the monikers TABLE_DEE and TABLE_DUM to be quite bad and never use them; and I can never remember which is which; rather I prefer the monikers D0C1 and D0C0, which are descriptive (the D and C mean degree and cardinality); plain D0 I use to mean the nullary tuple.

    Responding to Bill Karwin, you made a mistake regarding D0C0; joining a relation R with D0C0 results in a relation with the same attributes as R but no tuples, not in D0C0 as you said; your observation about D0C1 is correct, the result being just R, so D0C1 is the identity value for relational join.

    ReplyDelete

Subscribe to: Post Comments (Atom)