Re: pg and number of parameters by insert - Mailing list pgsql-general

From Dennis Björklund
Subject Re: pg and number of parameters by insert
Date
Msg-id Pine.LNX.4.44.0212041207380.9578-100000@zigo.dhs.org
Whole thread Raw
In response to Re: pg and number of parameters by insert  (Jean-Christian Imbeault <jc@mega-bucks.co.jp>)
List pgsql-general
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


pgsql-general by date:

Previous
From: suresh s
Date:
Subject: Re: problem startting postgresql
Next
From: Martijn van Oosterhout
Date:
Subject: Re: Starting postmaster in rc.local/during bootup