Thread: Re: [PATCHES] ANSI Compliant Inserts

Tom Lane wrote:
> "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.)

Yes, I understand the tempation to put the columns needing default at
the end and skipping them on INSERT.  However, our new DEFAULT insert
value seems to handle that nicely, certainly better than the old code
did, and I think the added robustness of now requiring full columns on
INSERT is worth it.

I realize this could break some apps, but with the new DEFAULT value, it
seems like a good time to reign in this error-prone capability.

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

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> 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.

> Yes, I understand the tempation to put the columns needing default at
> the end and skipping them on INSERT.  However, our new DEFAULT insert
> value seems to handle that nicely, certainly better than the old code
> did, and I think the added robustness of now requiring full columns on
> INSERT is worth it.

If I have two or three defaultable columns (say, a SERIAL primary key
and an insertion timestamp), it's going to be a pain in the neck to
have to write DEFAULT, DEFAULT, ... at the end of every insert.

I feel that people who want error cross-checking on this will have used
an explicit column list anyway.  Therefore, Rod's patch tightens the
case that should be tight, while still being appropriately loose for
casual manual inserts.

BTW, I do *not* agree with equating this case with COPY.  COPY is mostly
used for loading dumped data, and so it's reasonable to make different
tradeoffs between error checking and friendliness for COPY and INSERT.
        regards, tom lane