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>."
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org 
