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.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
==========================================================================
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 1We'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
A compromise position would be to allow dropping trailing columns only
when the column name list is omitted.Comments?
Sounds reasonable to me.
Andreas
Import Notes
Resolved by subject fallback
Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:
A compromise position would be to allow dropping trailing columns only
when the column name list is omitted.
Sounds reasonable to me.
IIRC, there's some interaction between this behavior and the way that
INSERT ... DEFAULT VALUES is implemented; I think DEFAULT VALUES is
parsed as an empty values list and then the trailing-column-omission
code is what actually makes the right things happen. So changing it
without breaking DEFAULT VALUES is not entirely trivial; it'll take
some changes in the raw-parsetree representation. There are other
missing SQL features hereabouts also, such as being able to write
a DEFAULTed column explicitly:
INSERT ... VALUES('foo', DEFAULT, 42);
and being able to insert multiple explicit rows:
INSERT ... VALUES('foo', DEFAULT, 42), VALUES('bar', 99, 44);
I think we should deal with all of these issues at once, which means
it's not something to try to fix for 7.1. Bruce, would you add a
TODO item?
* Bring INSERT ... VALUES up to full SQL92 spec.
regards, tom lane
missing SQL features hereabouts also, such as being able to write
a DEFAULTed column explicitly:
INSERT ... VALUES('foo', DEFAULT, 42);
and being able to insert multiple explicit rows:
INSERT ... VALUES('foo', DEFAULT, 42), VALUES('bar', 99, 44);
I think we should deal with all of these issues at once, which means
it's not something to try to fix for 7.1. Bruce, would you add a
TODO item?* Bring INSERT ... VALUES up to full SQL92 spec.
Added to TODO:
* Bring INSERT ... VALUES up to full SQL92 spec, disallow missing columns
--
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 19026a DEFAULTed column explicitly:
INSERT ... VALUES('foo', DEFAULT, 42);
and being able to insert multiple explicit rows:
INSERT ... VALUES('foo', DEFAULT, 42), VALUES('bar', 99, 44);
I think we should deal with all of these issues at once, which means
it's not something to try to fix for 7.1. Bruce, would you add a
TODO item?* Bring INSERT ... VALUES up to full SQL92 spec.
Oops, I have now added:
* Bring INSERT ... VALUES up to full SQL92 spec, disallow missing
columns, allow DEFAULT
--
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