Thread: Why forbid "INSERT INTO t () VALUES ();"
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.
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
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.
Fabien COELHO <coelho@cri.ensmp.fr> 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
Re: Why forbid "INSERT INTO t () VALUES ();"
From
ilmari@ilmari.org (Dagfinn Ilmari Mannsåker)
Date:
Tom Lane <tgl@sss.pgh.pa.us> writes: > Fabien COELHO <coelho@cri.ensmp.fr> 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
On Wed, Jun 24, 2020 at 3:31 PM Dagfinn Ilmari Mannsåker <ilmari@ilmari.org> 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.
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.
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
On Thu, 25 Jun 2020 at 16:56, Fabien COELHO <coelho@cri.ensmp.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. 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
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.
On Thu, Jun 25, 2020 at 12:56 AM Fabien COELHO <coelho@cri.ensmp.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. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, Jun 25, 2020 at 12:56 AM Fabien COELHO <coelho@cri.ensmp.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
On Wed, 24 Jun 2020 at 08:18, Fabien COELHO <coelho@cri.ensmp.fr> 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.
On Thu, Jun 25, 2020 at 12:07 PM Tom Lane <tgl@sss.pgh.pa.us> 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
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
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.