By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 983 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Question on INSERT statement

P: n/a
Hello,

I'm wondering if the following behaviour is the correct one for
PostGreSQL (7.4 on UNIX).

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 ?

Geoffrey Kretz - Four J's Development Tools

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Nov 23 '05 #2

P: n/a
Geoffrey KRETZ <gk@4js.com> writes:
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.


This is an extension to the spec - per SQL92 it would be right to throw
an error. However Postgres has always behaved this way and we're not
likely to change it. (I think it's a hangover from PostQUEL.) Many
people find it to be a convenient behavior.

If you want an error then specify an explicit column list:

INSERT INTO temp_tab (f1,f2,f3,f4,f5) VALUES (1,2,3)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.