Re: [PATCHES] ANSI Compliant Inserts

Tom Lane wrote:
> "Rod Taylor" <rbt(at)zort(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026