Re: Why forbid "INSERT INTO t () VALUES ();"

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Thu, Jun 25, 2020 at 12:56 AM Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> wrote:
>> It also means that if for some reason someone wants to insert several such
>> all-default rows, they have to repeat the insert, as "VALUES (), ();"
>> would not work, so it is also losing a corner-corner case capability
>> without obvious reason.

> That, and a desire to make things work in PostgreSQL that work in
> MySQL, seems like a good-enough reason to me, but YMMV.

Yeah, the multi-insert case is a plausible reason that hadn't been
mentioned before. On the other hand, you can already do that pretty
painlessly:

regression=# create table foo(x float8 default random());
CREATE TABLE
regression=# insert into foo select from generate_series(1,10);
INSERT 0 10
regression=# table foo;
x
---------------------
0.08414037203059621
0.2921176461398325
0.8760821189460586
0.6266325419285828
0.9946880079739273
0.4547070342142696
0.09683985675118834
0.3172576600666268
0.5122428845812195
0.8823697407826394
(10 rows)

So I'm still not convinced we should do this. "MySQL is incapable
of conforming to the standard" is a really lousy reason for us to do
something.

Anyway, to answer Fabien's question about why things are like this:
the standard doesn't allow zero-column tables, so most of these
syntactic edge cases are forbidden on that ground. We decided we
didn't like that restriction (because, for example, it creates a
painful special case for DROP COLUMN). So we've adjusted a minimal
set of syntactic edge cases to go along with that semantic change.
There's room to argue that INSERT's edge case should be included,
but there's also room to argue that it doesn't need to be.

regards, tom lane