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

On Wed, 24 Jun 2020 at 08:18, Fabien COELHO <coelho(at)cri(dot)ensmp(dot)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.