Re: Question on INSERT statement - Mailing list pgsql-general

Richard Huxton wrote:

> Geoffrey KRETZ wrote:
>
>>
>> I've a table temp_tab with 5 fields (f1,f2,f3,...),and I'm a
>> launching the following request :
>>
>> INSERT INTO temp_tab VALUES (1,2,3)
>>
>> It  will insert the values in the three first row whereas with
>> informix or db2 for exemple, it will return an error.
>>
>> So is that normal ?
>
>
> Well, it's normal in the sense that other installations of PG will do
> the same thing (and it's documented in the INSERT page of the
> manuals). Whether it is desirable or according to the SQL standards is
> another matter.
>
> Anyone with a copy of the specs know what they say?


I think it's that, isn't it :

        3) (...)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.

Here's the complete SQL92 specifications about the insert clause, I
don't know if sthing has change with SQL99 spec

"13.8 <insert statement>

         Function

         Create new rows in a table.

         Format

         <insert statement> ::=
              INSERT INTO <table name>
                <insert columns and source>

         <insert columns and source> ::=
                [ <left paren> <insert column list> <right paren> ]
              <query expression>
              | DEFAULT VALUES

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


         Syntax Rules

         1) The table T identified by the <table name> shall not be a read-
            only table.

         2) An <insert columns and source> that specifies DEFAULT VALUES is
            equivalent to an <insert columns and source> that specifies a
            <query expression> of the form

              VALUES (DEFAULT, . . . )

            where the number of "DEFAULT" entries is equal to the number of
            columns of T.

         3) No <column name> of T shall be identified more than once. 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.

         4) A column identified by the <insert column list> is an object
            column.

         5) Let QT be the table specified by the <query expression>. 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.

         6) The Syntax Rules of Subclause 9.2, "Store assignment", apply to
            corresponding columns of T and QT as TARGET and VALUE, respec-
            tively.

         Access Rules

         1) Case:

            a) If an <insert column list> is specified, then the applicable
              <privileges> shall include INSERT for each <column name> in
              the <insert column list>.

            b) Otherwise, the applicable privileges shall include INSERT for
              each <column name> in T.

            Note: The applicable privileges for a <table name> are defined
            in Subclause 10.3, "<privileges>".

         2) Each <column name> in the <insert column list> shall identify a
            column of T.

         General Rules

         1) If the access mode of the current SQL-transaction is read-only
            and T is not a temporary table, then an exception condition is
            raised: invalid transaction state.

         2) Let B be the leaf generally underlying table of T.

         3) The <query expression> is effectively evaluated before inserting
            any rows into B.

         4) Let Q be the result of that <query expression>.

            Case:

            a) If Q is empty, then no row is inserted and a completion con-
              dition is raised: no data.

            b) Otherwise, for each row R of Q:

              i) A candidate row of B is effectively created in which the
                 value of each column is its default value, as specified in
                 the General Rules of Subclause 11.5, "<default clause>".
                 The candidate row includes every column of B.

             ii) For every object column in the candidate row, the value of
                 the object column identified by the i-th <column name> in
                 the <insert column list> is replaced by the i-th value of
                 R.

            iii) Let C be a column that is represented in the candidate row
                 and let SV be its value in the candidate row. The General
                 Rules of Subclause 9.2, "Store assignment", are applied to
                 C and SV as TARGET and VALUE, respectively.

             iv) The candidate row is inserted into B.

                 Note: The data values allowable in the candidate row may be
                 constrained by a WITH CHECK OPTION constraint. The effect
                 of a WITH CHECK OPTION constraint is defined in the General
                 Rules of Subclause 11.19, "<view definition>".


         Leveling Rules

         1) The following restrictions apply for Intermediate SQL:

            a) The leaf generally underlying table of T shall not be gen-
              erally contained in the <query expression> immediately
              contained in the <insert columns and source> except as the
              <qualifier> of a <column reference>.

         2) The following restrictions apply for Entry SQL in addition to
            any Intermediate SQL restrictions:

            a) The <query expression> that is contained in an <insert state-
              ment> shall be a <query specification> or it shall be a <ta-
              ble value constructor> that contains exactly one <row value
              constructor> of the form "<left paren> <row value constructor
              list> <right paren>", and each <row value constructor ele-
              ment> of that <row value constructor list> shall be a <value
              specification>.

            b) If the data type of the target identified by the i-th <column
              name> is an exact numeric type, then the data type of the i-
              th item of the <insert statement> shall be an exact numeric
              type.

            c) If the data type of the target C identified by the i-th <col-
              umn name> is character string, then the length in characters
              of the i-th item of the <insert statement> shall be less than
              or equal to the length of C.

            d) The <insert columns and source> shall immediately contain a
              <query expression>."