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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Fabien COELHO-3

Hello devs,

I would like to create an "all defaults" row, i.e. a row composed of the
default values for all attributes, so I wrote:

   INSERT INTO t() VALUES ();

This is forbidden by postgres, and also sqlite.

Is there any good reason why this should be the case?

--
Fabien.


Reply | Threaded
Open this post in threaded view
|

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

Thomas Kellerer-4
Fabien COELHO schrieb am 24.06.2020 um 14:18:
> I would like to create an "all defaults" row, i.e. a row composed of the default values for all attributes, so I wrote:
>
>   INSERT INTO t() VALUES ();
>
> This is forbidden by postgres, and also sqlite.
>
> Is there any good reason why this should be the case?
>

Maybe because

   insert into t default values;

exists (and is standard SQL if I'm not mistaken)

Thomas



Reply | Threaded
Open this post in threaded view
|

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

Fabien COELHO-3

Hallo Thomas,

>>   INSERT INTO t() VALUES ();
>>
>> This is forbidden by postgres, and also sqlite.
>>
>> Is there any good reason why this should be the case?
>
> Maybe because
>
>   insert into t default values;
>
> exists (and is standard SQL if I'm not mistaken)
That's a nice alternative I did not notice. Well, not an alternative as
the other one does not work.

I'm still unclear why it would be forbidden though, it seems logical to
try that, whereas the working one is quite away from the usual syntax.

--
Fabien.
Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
Fabien COELHO <[hidden email]> writes:
>>>   INSERT INTO t() VALUES ();

> I'm still unclear why it would be forbidden though, it seems logical to
> try that, whereas the working one is quite away from the usual syntax.

It's forbidden because the SQL standard forbids it.

We allow zero-column syntaxes in some other places where SQL forbids
them, but that's only because there is no reasonable alternative.
In this case, there's a perfectly good, standards-compliant alternative.
So why encourage people to write unportable code?

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

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

Dagfinn Ilmari Mannsåker
Tom Lane <[hidden email]> writes:

> Fabien COELHO <[hidden email]> writes:
>>>>   INSERT INTO t() VALUES ();
>
>> I'm still unclear why it would be forbidden though, it seems logical to
>> try that, whereas the working one is quite away from the usual syntax.
>
> It's forbidden because the SQL standard forbids it.
>
> We allow zero-column syntaxes in some other places where SQL forbids
> them, but that's only because there is no reasonable alternative.
> In this case, there's a perfectly good, standards-compliant alternative.
> So why encourage people to write unportable code?

FWIW, MySQL (and MariaDB) only support INSERT INTO t () VALUES (), not
DEFAULT VALUES.  We have added syntax for MySQL compatibility in the
past, e.g. the CONCAT() function.

- ilmari
--
"A disappointingly low fraction of the human race is,
 at any given time, on fire." - Stig Sandbeck Mathisen


Reply | Threaded
Open this post in threaded view
|

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

David G Johnston
On Wed, Jun 24, 2020 at 3:31 PM Dagfinn Ilmari Mannsåker <[hidden email]> wrote:
FWIW, MySQL (and MariaDB) only support INSERT INTO t () VALUES (), not
DEFAULT VALUES.

We have added syntax for MySQL compatibility in the
past, e.g. the CONCAT() function.

I don't see the similarities.  IIUC there isn't a standard mandated function that provides the behavior that the concat function does.  There is an operator but the treatment of NULL is different.  So for concat we decided to add a custom function modelled on another DB's custom function.  Adding custom syntax here when an identically behaving standard syntax already exists has considerably less going for it.  I would say that accepting the compatibility hit while being the ones that are standard-compliant is in line with project values.

David J.

Reply | Threaded
Open this post in threaded view
|

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

Fabien COELHO-3
In reply to this post by Tom Lane-2

Hello Tom,

>>>>   INSERT INTO t() VALUES ();
>
>> I'm still unclear why it would be forbidden though, it seems logical to
>> try that, whereas the working one is quite away from the usual syntax.
>
> It's forbidden because the SQL standard forbids it.

Ok, that is definitely a reason. I'm not sure it is a good reason, though.

Why would the standard forbid it? From the language design point of view,
it is basically having a syntax for lists which would not work for empty
lists, or a syntax for strings which would not work for empty strings.

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.

> We allow zero-column syntaxes in some other places where SQL forbids
> them,

Then forbidding there it just adds awkwardness: the same thing works in
one place but not in another. That does not help users.

> but that's only because there is no reasonable alternative. In this
> case, there's a perfectly good, standards-compliant alternative. So why
> encourage people to write unportable code?

I doubt that people look at the (costly) standard when writing corner case
queries, they just try something logical as I did.

As some other databases accepts it, and if it is already allowed elsewhere
in pg, encouraging portability is not the main issue here. I'd rather have
logic and uniformity accross commands.

If I'm annoyed enough to send a patch some day, would you veto it because
it departs from the standard?

Anyway, thanks for the answer!

--
Fabien.


Reply | Threaded
Open this post in threaded view
|

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

Vik Fearing-6
On 6/25/20 6:56 AM, Fabien COELHO wrote:

>
> Hello Tom,
>
>>>>>   INSERT INTO t() VALUES ();
>>
>>> I'm still unclear why it would be forbidden though, it seems logical to
>>> try that, whereas the working one is quite away from the usual syntax.
>>
>> It's forbidden because the SQL standard forbids it.
>
> Ok, that is definitely a reason. I'm not sure it is a good reason, though.


It's a very good reason.  It might not be good *enough*, but it is a
good reason.


> Why would the standard forbid it? From the language design point of
> view[...]


Don't go there.  There is nothing but pain there.

--
Vik Fearing


Reply | Threaded
Open this post in threaded view
|

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

David Rowley
In reply to this post by Fabien COELHO-3
On Thu, 25 Jun 2020 at 16:56, Fabien COELHO <[hidden email]> 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.

This is not a vote in either direction but just wanted to say that
during 7e413a0f8 where multi-row inserts were added to pg_dump, a
special case had to be added to support tables with no columns.  We
cannot do multi-inserts for that so are forced to fall back on
one-row-per-INSERT.

However, even if we had this syntax I imagine it would be unlikely
we'd change pg_dump to use it since we want to be as standard
compliant as possible when dumping INSERTs since it appears the only
genuine use-case for that is for importing the data into some other
relational database.

David


Reply | Threaded
Open this post in threaded view
|

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

Fabien COELHO-3
In reply to this post by Vik Fearing-6

Bonjour Vik,

>>> It's forbidden because the SQL standard forbids it.
>>
>> Ok, that is definitely a reason. I'm not sure it is a good reason, though.

> It's a very good reason.  It might not be good *enough*, but it is a
> good reason.

Ok for good, although paradoxically not "good enough":-)

>> Why would the standard forbid it? From the language design point of
>> view[...]
>
> Don't go there.  There is nothing but pain there.

Hmmm. I like to understand. Basically it is my job.

Otherwise, yes and no. Postgres could decide (has sometimes decided) to
extend the syntax or semantics wrt the standard if it makes sense, so that
when a syntax is allowed by the standard it does what the standard says,
which I would call positive compliance and I would support that, but keep
some freedom elsewhere.

--
Fabien.


Reply | Threaded
Open this post in threaded view
|

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

Robert Haas
In reply to this post by Fabien COELHO-3
On Thu, Jun 25, 2020 at 12:56 AM Fabien COELHO <[hidden email]> 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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
Robert Haas <[hidden email]> writes:
> On Thu, Jun 25, 2020 at 12:56 AM Fabien COELHO <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

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

Isaac Morland
In reply to this post by Fabien COELHO-3
On Wed, 24 Jun 2020 at 08:18, Fabien COELHO <[hidden email]> wrote:
I would like to create an "all defaults" row, i.e. a row composed of the 
default values for all attributes, so I wrote:

   INSERT INTO t() VALUES ();

This is forbidden by postgres, and also sqlite.

This is not the only area where empty tuples are not supported. Consider:

PRIMARY KEY ()

This should mean the table may only contain a single row, but is not supported.

Also, GROUP BY supports grouping by no columns, but not in a systematic way: Using aggregate functions with no explicit GROUP BY clause will result in grouping by no columns (i.e., entire result set is one group); I also found that I could GROUP BY NULL::integer, abusing the column number syntax. But things like GROUP BY ROLLUP () are not supported.

On the plus side, empty rows are supported, although the explicit ROW keyword is required.

Reply | Threaded
Open this post in threaded view
|

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

Robert Haas
In reply to this post by Tom Lane-2
On Thu, Jun 25, 2020 at 12:07 PM Tom Lane <[hidden email]> wrote:
> 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:

Sure, but it means if you're writing code to generate queries
programmatically, then you have to handle the 0-column case completely
differently from all the others. Seems like unnecessary pain for no
real reason.

I mean, I generally agree that if the standard says that syntax X
means Y, we should either make X mean Y, or not support X. But if the
standard says that X has no meaning at all, I don't think it's a
problem for us to make it mean something logical. If we thought
otherwise, we'd have to rip out support for indexes, which would
probably not be a winning move. Now, various people, including you and
I, have made the point that it's bad to give a meaning to some piece
of syntax that is not current part of the standard but might become
part of the standard in the future, because then we might end up with
the standard saying that X means one thing and PostgreSQL thinking
that it means something else. However, that can quickly turn into an
argument against doing anything that we happen not to like, even if
the reason we don't like it has more to do with needing a Snickers bar
than any underlying engineering reality. In a case like this, it's
hard to imagine that () can reasonably mean anything other than a
0-column tuple. It's not impossible that someone could invent another
interpretation, and there's been much discussion on this list about
how the SQL standards committee is more likely than you'd think to
come up with unusual ideas, but I still don't think it's a bad gamble,
especially given the MySQL/MariaDB precedent.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Reply | Threaded
Open this post in threaded view
|

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

Peter Eisentraut-6
In reply to this post by Tom Lane-2
On 2020-06-25 18:07, Tom Lane wrote:
> 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.

Conformance to the standard means that the syntax described in the
standard behaves as specified in the standard.  It doesn't mean you
can't have additional syntax that is not in the standard.

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Reply | Threaded
Open this post in threaded view
|

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

Fabien COELHO-3
In reply to this post by Isaac Morland

Hello Isaac,

> This is not the only area where empty tuples are not supported. Consider:
>
> PRIMARY KEY ()
>
> This should mean the table may only contain a single row, but is not
> supported.

Yep. This is exactly the kind of case about which I was trying the
command, after reading Bruce Momjian blog
(https://momjian.us/main/blogs/pgblog/2020.html#June_22_2020) about
one-row tables and thinking about how to improve it and allow enforcing a
singleton simply, which is a thing I needed several times in the past.

> On the plus side, empty rows are supported, although the explicit ROW
> keyword is required.

Yet another weirdness.

Thanks for the comments.

--
Fabien.


Reply | Threaded
Open this post in threaded view
|

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

Stephen Frost
In reply to this post by Peter Eisentraut-6
Greetings,

* Peter Eisentraut ([hidden email]) wrote:
> On 2020-06-25 18:07, Tom Lane wrote:
> >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.
>
> Conformance to the standard means that the syntax described in the standard
> behaves as specified in the standard.  It doesn't mean you can't have
> additional syntax that is not in the standard.

Agreed in general with the caveat that we don't want to support syntax
that the standard might decide later means something else.

For this case, however, I tend to agree with the other folks on this
thread who feel that we should add it- since it seems quite unlikely
that the standard folks would define this syntax to somehow mean
something else.

Thanks,

Stephen

signature.asc (836 bytes) Download Attachment