PostgreSQL: Tables without columns

PostgreSQL allows to create, read and write tables without columns. Let’s see how to work with them, and whether they can have practical uses.

Create a table without columns

To create a table without columns, just leave the column list empty, but don’t omit the parenthesis:

postgres=# CREATE TABLE t ();
CREATE TABLE

Insert rows without columns

According to relational algebra, a relation without attributes can contain zero or one row without attributes.

PostgreSQL allows to insert any number of rows. Apparently, only the DEFAULT VALUE syntax is accepted:

postgres=# INSERT INTO t DEFAULT VALUES;
INSERT 0 1
postgres=# INSERT INTO t DEFAULT VALUES;
INSERT 0 1

Deleting rows with no columns

EDITED on 23th October 2021: Thanks Andreas Kretschmer for his contribute! The initial version of this paragraph said that there is no way to delete a single row, but his comment shown us that it is possible.

DELETEing single rows is possible thanks to the ctid column. To DELETE the row with the lowest ctid:

postgres=# DELETE FROM t WHERE ctid IN (SELECT MIN(ctid) FROM t);
DELETE 1

To DELETE the row with the highest ctid:

postgres=# DELETE FROM t WHERE ctid IN (SELECT MAX(ctid) FROM t);
DELETE 1

Reading a table with no rows

We can SELECT from a table without columns. We can use * or omit the column list at all. But each column has no attributes, so all we can get is the number of rows – unless we explicitly SELECT their ctid.

postgres=# SELECT * FROM t;
--
(2 rows)
postgres=# SELECT FROM t;
--
(2 rows)
postgres=# SELECT *, ctid FROM t;
 ctid  
-------
 (0,2)
 (0,3)
(2 rows)

Use cases

Use cases for columnless tables are probably more theoretical than practical. And they are a rare, obscure practice. But sometimes you might have a reason to use them.

Columnless tables as boolean values

A table without columns can be seen as an indicator with only two possible values:

It is empty (FALSE);
It has one or more identical rows (TRUE).

To set it to TRUE, add one row.

To set it to FALSE, run:

TRUNCATE TABLE t;

To read it:

SELECT COUNT(*) FROM (SELECT FROM t LIMIT 1) v;

Columnless tables as counters

The only data a table without columns contains is the number of its rows. So a table with no columns can be seen as an increment-only counter. To increment the counter, add one row. To reset it, truncate the table. To read it, run a SELECT COUNT(*).

INSERTs will not be blocked by other INSERTs, or by SELECTs. This is a potential benefit over using tables where each row is a counter: if two UPDATEs on the same row are run at the same time, one will have to wait until the other has finished. A potential disadvantage is that counting the rows is slower than reading a single value, especially if a proper index exists.

Placeholder

A table without columns can be created as a placeholder. Seeing it will remind us that we need to replace it with a proper table.