11

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.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Barry Brown
  • 20,233
  • 15
  • 69
  • 105
  • I did. It just deletes the rows and returns nothing special. – Barry Brown Sep 16 '12 at 07:57
  • 4
    Looks like you missed `RETURNING` which is a non optional part of the grammar preceding `output_expression` I presume that `*` is just treated as an alias (as for `wheeeeeee`) without that. – Martin Smith Sep 16 '12 at 08:04
  • I've deleted my answer along those lines as I'm not actually sure that `*` is treated as an alias. It isn't possible to then use it in a `WHERE` clause and with the optional `AS` keyword, i.e. `DELETE FROM customer AS *` fails with a syntax error. – Martin Smith Sep 16 '12 at 08:41
  • @MartinSmith, after reading your analysis (now deleted), I tend to think it's a bug in Postgres. The * is being treated as an alias, although an illegal one. The grammar for the RETURNING clause appears to be incorrect -- at least in the documentation. I'm going to report it as a bug and see what the devs say. – Barry Brown Sep 16 '12 at 08:44
  • 2
    @BarryBrown - From experimentation I think the token is maybe being accepted as though it were an alias in that specific query but it doesn't actually get treated as an alias in any way. `DELETE FROM customer * WHERE customer.id=1` works. If it was treated as an alias at all then referencing `customer` should fail as it would be hidden. Seems to be behaviour specific to `*` as well. Trying `+` for example fails. Without looking at the source code not sure exactly what is going on. – Martin Smith Sep 16 '12 at 09:15
  • I took a look at parse.y and scan.l in the source and can't figure out exactly which rule is matching the `*`. If it's a table alias, it must be matched by `ColID`, but `ColID` just reduces to `IDENT` and that's where the trail runs cold. – Barry Brown Sep 16 '12 at 09:29
  • 1
    Tom Lane explains this on the PostgreSQL mailing list in reply to the repost of this question there. See http://archives.postgresql.org/message-id/5536.1347854286@sss.pgh.pa.us . I added the link to @MichalPolitowski's answer to make sure nobody misses it. – Craig Ringer Sep 17 '12 at 08:05

1 Answers1

5

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.

Michał Politowski
  • 4,288
  • 3
  • 30
  • 41