Re: [PATCHES] ANSI Compliant Inserts

I submitted a patch which would make Postgresql ANSI compliant in
regards to INSERT with a provided column list. As Tom states below,
this is not full compliance.

CREATE TABLE tab(col1 text, col2 text);

INSERT INTO tab (col1, col2) VALUES ('val1'); -- bad by spec (enforced
by patch)
INSERT INTO tab (col1, col2) VALUES ('val1', 'val2'); -- good

INSERT INTO tab VALUES ('val1'); -- bad by spec (not enforced)
INSERT INTO tab VALUES ('val1', 'val2'); -- good

Currently in postgres all of the above are valid. I'd like to rule
out the first case (as enforced by the patch) as it's obvious the user
had intended to have two values. Especially useful when the user
misses a value and inserts bad data into the table as a result.

For the latter one, it could be argued that the user understands the
table in question and has inserted the values they require. New
columns are added at the end, and probably don't affect the operation
in question so why should it be changed to suit new columns? But,
automated code should always be written with the columns explicitly
listed, so this may be a user who has simply forgotten to add the
value -- easy to do on wide tables.

Thoughts?
--
Rod Taylor

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Bruce Momjian" <pgman(at)candle(dot)pha(dot)pa(dot)us>
Cc: "Rod Taylor" <rbt(at)zort(dot)ca>; <pgsql-patches(at)postgresql(dot)org>
Sent: Sunday, April 14, 2002 11:49 PM
Subject: Re: [PATCHES] ANSI Compliant Inserts

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Do you want to argue we should continue allowing it?
>
> No; I'm objecting that there hasn't been adequate discussion about
> this change of behavior.
>
> BTW, if the rationale for the change is "ANSI compliance" then the
patch
> is still wrong. SQL92 says:
>
> 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.
>
> 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 patch enforces equality only for the case of an explicit <insert
> column list> --- which is the behavior I suggested in the original
> comment, but the spec clearly requires an exact match for an
implicit
> list too. How tight do we want to get?
>
> In any case this discussion should be taking place someplace more
public
> than -patches.
>
> regards, tom lane
>