Thread: 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@sss.pgh.pa.us>
To: "Bruce Momjian" <pgman@candle.pha.pa.us>
Cc: "Rod Taylor" <rbt@zort.ca>; <pgsql-patches@postgresql.org>
Sent: Sunday, April 14, 2002 11:49 PM
Subject: Re: [PATCHES] ANSI Compliant Inserts


> Bruce Momjian <pgman@candle.pha.pa.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
>

Rod Taylor wrote:
> 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.

I think our new DEFAULT for insert allows people to properly match all
columns, and I think it is too error prone to allow missing columns in
any INSERT.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

"Rod Taylor" <rbt@zort.ca> writes:
> 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.

Seems reasonable.

> For the latter one, it could be argued that the user understands the
> table in question and has inserted the values they require.

Ruling out this case would break a technique that I've used a lot in the
past, which is to put defaultable columns (eg, SERIAL columns) at the
end, so that they can simply be left out of quick manual inserts.
So I agree with this part too.  (I wouldn't necessarily write
application code that way, but then I believe in the theory that robust
application code should always specify an explicit column list.)

For the record --- I actually am in favor of this patch; but I wanted
to see the change discussed and defended in a more widely-read mailing
list than -patches.  If there are no objections from the assembled
hackers, apply away ...
        regards, tom lane

Rod Taylor writes:

> 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

I recall that this was the behavior we agreed we wanted.  IMHO, it would
be conditional on the INSERT ... VALUES (DEFAULT) capability being
provided.  I'm not sure if that is there yet.

-- 
Peter Eisentraut   peter_e@gmx.net