5

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

  • 1
    The behaviour of PostgreSQL is detailed in the official documentation: "...is a PostgreSQL extension. It fills the columns from the left with as many values as are given, and the rest will be defaulted." – Marco Borchert Jan 19 '18 at 13:39
  • 1
    Everytime you think you have all of the docs memorized.. nope, it's still in the docs. – Evan Carroll Jan 19 '18 at 16:36
  • PostgreSQL docs usually state their adherence or difference to ANSI SQL standards. Although in this case it is not clear. – gbn Feb 8 '18 at 7:26
11

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
2

I think PostgreSQL is breaking the spec, Lennart is right on the part he quotes, it's also there in the SQL:2016 version

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.

But, going down a bit further,

13) Let QT be the table specified by the <query expression> or <contextually typed table value constructor>. The degree of QT shall be equal to the number of <column name>s in the <insert column list>. The column of table T identified by the i-th <column name> in the <insert column list> corresponds with the i-th column of QT.

So the <insert column list> can be omitted but if it is omitted it's assumed to be the entirety of the table. And the <insert column list> must be equal in degree to the <contextually typed table value constructor> (VALUES).

As Marco Borchert points out too in the comments, this is actually explicitly in the docs as an "extension"/violation of the standard,

The second form is a PostgreSQL extension. It fills the columns from the left with as many values as are given, and the rest will be defaulted.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.