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>."
