
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.net
 128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================




Mon, 29 Jan 2001 11:21:38 -0500 (EST)
Vince Vielhaber <vev@michvhf.com>
Shouldn't this be an error?
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

Mon, 29 Jan 2001 11:44:39 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Shouldn't this be an error? 
> 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, Pennsylvania 19026

Mon, 29 Jan 2001 11:58:15 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Shouldn't this be an error?