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
postgres=# DELETE FROM t WHERE ctid IN (SELECT MIN(ctid) FROM t); DELETE 1
DELETE the row with the highest
postgres=# DELETE FROM t WHERE ctid IN (SELECT MAX(ctid) FROM t); DELETE 1
Reading a table with no rows
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
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 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 (
- It has one or more identical rows (
To set it to
TRUE, add one row.
To set it to
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
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.
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.