Re: pg and number of parameters by insert

On Wed, 4 Dec 2002, Jean-Christian Imbeault wrote:

> create table_1 (
> a text,
> b int,
> c int
> );
>
> Why should insert into table_1 values ('f1_value',10) be an error?

Because there is only two values in the tuple and if you want the last to
be NULL why not write that.

> Explain to me why the above SQL query is wrong and maybe I can help you.
> But I can't see why a DB should give an error.

It's the same resom that if you have a C function taking two arguments and
you try to call it with one you get an error. Of course the C-compiler
could call the function anyway and supply 0 for the extra argument. I for
one would like to get at least a warning in cases like this. When I create
queries I make sure I create them typecorrect and with the correct number
of arguments to everything. If I make a mistake I want the system to help
me find it. It's the same reson why we don't allow atoi('') in 7.3 even
though we did it before.

> If you want to force the user to give values for *every* column when
> doing an insert then all you need to do is create a table like this:
>
> create table_1 (
> a text NOT NULL,
> b int NOT NULL,
> c int NOT NULL
> );

NULL is also a value and there is a difference to write ('f1_value',10)
and ('f1_value',10,NULL). The first can be a bug since I forgot to change
it when I added the third column to the table. The second is clearly
correct since it contains 3 elements in the tuple.

> Maybe I am missing your point?

Yes.

However, this is how it have worked for some time so it might be difficult
to change this into an error. But if there was a flag for this I would
very much like to get an error in cases like above. I'm pleased to se that
some other database systems treat it like an error. I didn't know that
before it was pointed out in this thread.

--
/Dennis