Sql-server – Does the spec permit column-count mismatch on INSERT

insertmariadbpostgresqlsql serversql-standard

I'm just wondering if the spec permits excluding the column-list clause in an INSERT statement if you're inserting in fewer columns than the table provides. I see PostgreSQL does, and MySQL and SQL Server do not.

With PostgreSQL, (works)

# CREATE TABLE t (a int, b int);
CREATE TABLE
# INSERT INTO t VALUES (42);  -- (b is set to DEFAULT)
INSERT 0 1

With SQL Server, I get

Msg 213 Level 16 State 1 Line 1
Column name or number of supplied values does not match table definition.

With MariaDB, I get

Column count doesn't match value count at row 1

What's the right behavior if there is a column-count mismatch?

Question inspired by the response and comments to this question here

Best Answer

Draft documents can be found at:

http://www.wiscorp.com/sql20nn.zip

The part that is of interest for this question is:

7IWD2-02-Foundation-2011-12.pdf

At first I could not find any support for leaving out the column specification at all. However, in 14.11 the BNF looks like:

<insert statement> ::=
INSERT INTO <insertion target> <insert columns and source>
[...]
<insert columns and source> ::=
    <from subquery>
  | <from constructor>
  | <from default>

<from subquery> ::=
  [ <left paren> <insert column list> <right paren> ]
    [ <override clause> ]
    <query expression>

<from constructor> ::=
  [ <left paren> <insert column list> <right paren> ]
    [ <override clause> ]
    <contextually typed table value constructor>

<insert column list> ::=
    <column name list>

I.e. the "column name list" is contained within square-brackets so there may or may not be a column specification. However, in the text under Syntax Rules it mentions:

7) If the <insert column list> is omitted, then an 
   <insert column list> that identifies all columns of T in the
   ascending sequence of their ordinal positions within T is implicit.

So, it seems that if the column list is omitted, all columns are assumed and that they should be interpreted from left to right. My guess is that PostgreSQL is too generous in this regard.

For clarity I think it is a good idea to always declare the column-list (except for ad-hoc situations). I can add Db2 (10.5) to the products that require a column-list for a partial tuple:

db2 "INSERT INTO t VALUES (42)"
DB21034E  The command was processed as an SQL statement because it was not a  
valid Command Line Processor command.  During SQL processing it returned:
SQL0117N  The number of values assigned is not the same as the number of specified or implied columns or variables.  SQLSTATE=42802
Related Question