Re: ANSI Compliant Inserts

Michael Loftis <mloftis(at)wgops(dot)com> writes:
> I'm on the fence in that situation. Though I'd lean towards a patch
> thats a sort of compromise. IIF the 'remaining' columns (IE columns
> unspecified) have some sort of default or auto-generated value (forgive
> me I'm just getting back into workign with postgresql) like a SERIAL or
> TIMESTAMP allow it, IFF any of them do not have a default value then
> fail. This will make it 'do the right thing'

I think the apparent security is illusory. Given the presence of ALTER
TABLE ADD/DROP DEFAULT, the parser might easily accept a statement for
which an end-column default has been dropped by the time the statement
comes to be executed. (Think about an INSERT in a rule.)

Another reason for wanting it to work as proposed is ADD COLUMN.
Consider

CREATE TABLE foo (a, b, c);

create rule including INSERT INTO foo(a,b,c) VALUES(..., ..., ...);

ALTER TABLE foo ADD COLUMN d;

The rule still works, and will be interpreted as inserting the default
value (NULL if unspecified) into column d.

Now consider same scenario except I write the rule's INSERT without
an explicit column list. If we follow the letter of the spec, the
rule will now fail. How is this sensible or consistent behavior?
The case that should be laxer/easier is being treated *more* rigidly.

In any case, the above comparison shows that it's not very consistent
to require explicit defaults to be available for the omitted column(s).
INSERT with an explicit column list does not have any such requirement.

regards, tom lane