Re: Question on INSERT statement

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