Thread: Shouldn't this be an error?

Shouldn't this insert fail?   This is 7.0.3.

template1=# create table foo(x int,y varchar(10),z datetime);
CREATE
template1=# insert into foo(x,y,z) values(1,'asdf');
INSERT 19222 1
template1=#

If not, why not?  z's missing from the values().  Sybase fails it:

1> create table foo(x int,y varchar(10),z datetime)
2> go
1> insert into foo(x,y,z) values(1,'asdf')
2> go
Msg 109, Level 15, State 1
, Line 1
There are more columns in the INSERT statement than values specified in
the VALUES clause.  The number of values in the VALUES clause must match
the number of columns specified in the INSERT statement.
1>


Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net128K ISDN from $22.00/mo - 56K Dialup from
$16.00/moat Pop4 Networking       Online Campground Directory    http://www.camping-usa.com      Online Giftshop
Superstore   http://www.cloudninegifts.com
 
==========================================================================

Vince Vielhaber <vev@michvhf.com> writes:
> Shouldn't this insert fail?   This is 7.0.3.

> template1=# create table foo(x int,y varchar(10),z datetime);
> CREATE
> template1=# insert into foo(x,y,z) values(1,'asdf');
> INSERT 19222 1

We've always allowed trailing columns to be omitted, whether a column
name list is specified or not.  This is not per spec --- SQL92 and SQL99
both say that all the columns must be provided --- but I'm rather
hesitant to enforce the spec's stricter rule at this point.  Seems like
it'd probably break some existing apps.

A compromise position would be to allow dropping trailing columns only
when the column name list is omitted.

Comments?
        regards, tom lane

> Vince Vielhaber <vev@michvhf.com> writes:
> > Shouldn't this insert fail?   This is 7.0.3.
> 
> > template1=# create table foo(x int,y varchar(10),z datetime);
> > CREATE
> > template1=# insert into foo(x,y,z) values(1,'asdf');
> > INSERT 19222 1
> 
> We've always allowed trailing columns to be omitted, whether a column
> name list is specified or not.  This is not per spec --- SQL92 and SQL99
> both say that all the columns must be provided --- but I'm rather
> hesitant to enforce the spec's stricter rule at this point.  Seems like
> it'd probably break some existing apps.
> 
> A compromise position would be to allow dropping trailing columns only
> when the column name list is omitted.

Yikes, we allow the column to be missing even if they specify it in
INSERT.  That seems very wrong.

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