Re: ANSI Compliant Inserts

Tom Lane wrote:
> Rod Taylor <rbt@zort.ca> writes:
> >       /*
> > !      * XXX It is possible that the targetlist has fewer entries than were
> > !      * in the columns list.  We do not consider this an error.    Perhaps we
> > !      * should, if the columns list was explicitly given?
> >        */
> > =20=20
> >       /* done building the range table and jointree */
> >       qry->rtable =3D pstate->p_rtable;
> > --- 547,558 ----
> >       }
> > =20=20
> >       /*
> > !      * Ensure that the targetlist has the same number of  entries
> > !      * that were present in the columns list.  Don't do the check
> > !      * for select statements.
> >        */
> > +     if (stmt->cols !=3D NIL && (icolumns !=3D NIL || attnos !=3D NIL))
> > +         elog(ERROR, "INSERT has more target columns than expressions");
>
>
> What's the rationale for changing this exactly?
>
> The code might or might not need changing (I believe the XXX comment
> questioning it is mine, in fact) but changing behavior without any
> pghackers discussion is not the way to approach this.
>
> In general I'm suspicious of rejecting cases we used to accept for
> no good reason other than that it's not in the spec.  There is a LOT
> of Postgres behavior that's not in the spec.

TODO has:

    o Disallow missing columns in INSERT ... VALUES, per ANSI

I think it should be done because it is very easy to miss columns on
INSERT without knowing it.  I think our current behavior is too
error-prone.  Now, if we want to just throw a NOTICE is such cases, that
would work too.

Clearly he didn't need discussion because it was already on the TODO
list.  I guess the question is whether it should have had a question
mark.  I certainly didn't think so.

Also, I thought we were going to fix COPY to reject missing columns too.
I just can't see a valid reason for allowing missing columns in either
case, except to hide errors.

--
  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, Pennsylvania 19026