What does the output_expression for "DELETE FROM table" do?

Question

I recently ran across an oddity. The following is valid SQL:

DELETE FROM customer *;

The documentation for PostgreSQL DELETE says the star is a possible value for the output_expression:

An expression to be computed and returned by the DELETE command after each row is deleted. The expression can use any column names of the table or table(s) listed in USING. Write * to return all columns.

I tried it with and without the star and can't see a difference. In fact, I can put just about anything single word after the table name and it is accepted. It doesn't even have to be an actual column name. Nothing extra is returned.

db=> DELETE FROM customer wheeeeeee;
DELETE 19

So what does it do and what could I use it for?

Question also posted on the PostgreSQL mailing list.

Answer 1

The asterisk is not output_expression, for this you would have to use the RETURNING keyword. It is instead an old, obsolete syntax for including child tables in queries. (The last version for which it is documented seems to be PostgreSQL 8.1. Since the syntax is still valid it is a documentation bug, as Tom Lane points out in the post linked below.)

Since PostgreSQL 7.1 this is the default (unless sql_inheritance is set to off) and the ONLY keyword is used for the opposite, so the * is not very useful.

See this explanatory post from Tom Lane on the PostgreSQL mailing list.