Thread: pg and number of parameters by insert

pg and number of parameters by insert

From
pginfo
Date:
Hi ,

I am very confused with pg insert manipulation.

As I see pg don not check number of insert parameters and do not comapre
it to table column numbers.

For example if :

create table table_1 ( filed _1 name, field_2  int, field_3  name);

And if I try to execute:

insert into table_1 values ('f1_value',10);

I do not get any error from pg !!!

The problem is that I send two parameter and in table declaration I have
3 !

Is it bug by pg or I can setup my server to prevent my from this
mistake.

I make a litle test with oracle, MS SQL and mySQL and all this reports
error.

regards,
ivan.


Re: pg and number of parameters by insert

From
Jean-Christian Imbeault
Date:
pginfo wrote:
>
> For example if :
>
> create table table_1 ( filed _1 name, field_2  int, field_3  name);
>
> And if I try to execute:
>
> insert into table_1 values ('f1_value',10);
>
> I do not get any error from pg !!!

The reason is that you did not declare the fields to be "NOT NULL". The
default is to allow null values into the fields unless you specifically
create the columns with the constraint "NOT NULL".

If you want an error to be thrown you need to create your table liek this:

create table table_1 (
   filed _1 text NOT NULL,
   field_2  int  NOT NULL,
   field_3  text NOT NULL
);

Jc



Re: pg and number of parameters by insert

From
pginfo
Date:
Hi,

I do not think so.

Realy I will to have NULL value in my table.
The problem is that it can be syntax error (for example I forgot to rewrite
all my functions after
adding new filed to my table).

In this case I have syntax error in my code!
And I think the pg need to report it.

If I write
 insert into table_1 (field_1,field_2)  values ('f1_value',10);
and field_3 is not declared as NOT NULL it will be correct for pg to execute
it without any massage.

Is it so?

regards,
ivan.

Jean-Christian Imbeault wrote:

> pginfo wrote:
> >
> > For example if :
> >
> > create table table_1 ( filed _1 name, field_2  int, field_3  name);
> >
> > And if I try to execute:
> >
> > insert into table_1 values ('f1_value',10);
> >
> > I do not get any error from pg !!!
>
> The reason is that you did not declare the fields to be "NOT NULL". The
> default is to allow null values into the fields unless you specifically
> create the columns with the constraint "NOT NULL".
>
> If you want an error to be thrown you need to create your table liek this:
>
> create table table_1 (
>    filed _1 text NOT NULL,
>    field_2  int  NOT NULL,
>    field_3  text NOT NULL
> );
>
> Jc




Re: pg and number of parameters by insert

From
Jean-Christian Imbeault
Date:
pginfo wrote:
>
> If I write
>  insert into table_1 (field_1,field_2)  values ('f1_value',10);
> and field_3 is not declared as NOT NULL it will be correct for pg to execute
> it without any massage.
>
> Is it so?

That's right. If you do not declare the column with a constraint of NOT
NULL, then it is not an error to do:

insert into table_1 (field_1,field_2)  values ('f1_value',10);

Why should this query throw an error? You are asking the DB to insert
values into a row. There are no constraints on any of the columns so the
DB is quite happy to do what you ask.

If you want to make it mandatory for an insert to specify a value for a
column then you MUST have a constraint on that column of NOT NULL.

Jc


Re: pg and number of parameters by insert

From
pginfo
Date:
Hi,
Hmm, Ok I try to explain:

If I write: insert into table_1  values ('f1_value',10);

It is error because I try to insert 2 fields in 3-fields table. The reason is that
I can make write error.

If I write : insert into table_1 (field_1,field_2)  values ('f1_value',10); It is
no problem because I have specified exact the
inserted fields and I have one missing and it is not NOT NULL.

The problem is that pg do not report the first error.
And as I wrote oracle, MS SQL and mySQL reports correct the error.

I think this bug is potencial ground for many errrs by developing.

regards,
ivan.




Jean-Christian Imbeault wrote:

> pginfo wrote:
> >
> > If I write
> >  insert into table_1 (field_1,field_2)  values ('f1_value',10);
> > and field_3 is not declared as NOT NULL it will be correct for pg to execute
> > it without any massage.
> >
> > Is it so?
>
> That's right. If you do not declare the column with a constraint of NOT
> NULL, then it is not an error to do:
>
> insert into table_1 (field_1,field_2)  values ('f1_value',10);
>
> Why should this query throw an error? You are asking the DB to insert
> values into a row. There are no constraints on any of the columns so the
> DB is quite happy to do what you ask.
>
> If you want to make it mandatory for an insert to specify a value for a
> column then you MUST have a constraint on that column of NOT NULL.
>
> Jc




Re: pg and number of parameters by insert

From
Jean-Christian Imbeault
Date:
pginfo wrote:
 >
 > Hmm, Ok I try to explain:

Try again. I can't quite get what you mean.

 >If I write: insert into table_1  values ('f1_value',10);
 >
 >It is error because I try to insert 2 fields in 3-fields table. The
 >reason is that I can make write error.

If your table is:

create table_1 (
   a  text,
   b  int,
   c  int
);

Why should insert into table_1  values ('f1_value',10) be an error?

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. 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
);

Maybe I am missing your point?

Jc


Re: pg and number of parameters by insert

From
Dennis Björklund
Date:
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


Re: pg and number of parameters by insert

From
Jean-Christian Imbeault
Date:
Dennis Björklund wrote:
 >
 > 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.

Aaaahhhh, I see the light.

I completely agree with you that it would be nice to get a warning.

What do the SQL specs have to say about this? Maybe it's not an error
because the specs require it not to be an error? (I have no idea, just
asking)

If the specs don't say either way, then a warning would be nice, and an
error better. But I bet that throwing an error would break a lot of apps ;)

Jc


Re: pg and number of parameters by insert

From
pginfo
Date:
Hi Jean,

Jean-Christian Imbeault wrote:

> pginfo wrote:
>  >
>  > Hmm, Ok I try to explain:
>
> Try again. I can't quite get what you mean.
>
>  >If I write: insert into table_1  values ('f1_value',10);
>  >
>  >It is error because I try to insert 2 fields in 3-fields table. The
>  >reason is that I can make write error.
>
> If your table is:
>
> create table_1 (
>    a  text,
>    b  int,
>    c  int
> );
>
> Why should insert into table_1  values ('f1_value',10) be an error?
>

Because I send not 3 parameters.

> 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. 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
> );
>
> Maybe I am missing your point?
>

I will give you a example:

I use java + pg.
I have table t1 (a name, b int ).
I have 2 methods add1 and add2:

public int add1(String parameter1, int parameter2)
{
   ...
   ...
       stmt.executeUpdate("insert into t1 values('" + parameter1 + "'," +
parameter2 + ")" ); // it make insert
  ...
 }

public int add2(String parameter1, int parameter2)
{
   ...
   ...
       stmt.executeUpdate("insert into t1 values('" + parameter1 + "'," +
parameter2 + ")" ); // it make insert
  ...
 }


And if I add new field to my table. The field is varchar(100) and can be
empty . The empty string is NULL in pg.

After it I need to werwite my methods add1 and add2. But what will happen if
I forgot to make changes in add2:

public int add1(String parameter1, int parameter2,String parameter3)
{
   ...
   ...
       stmt.executeUpdate("insert into t1 values('" + parameter1 + "'," +
parameter2 + "'" + parameter3 + "')" ); // it make insert
  ...
 }

public int add2(String parameter1, int parameter2,String parameter3 )
{
   ...
   ...
       stmt.executeUpdate("insert into t1 values('" + parameter1 + "'," +
parameter2 + ")" ); // it make insert
  ...
 }

All two methods will not report error.
Actualy I can not easy detect my mistake, because the DB do not report the
error.

It is very important, because we write big projects and it is possible to
forgot this method.

And also I can not  set the field to NOT NULL, because the user will not be
able to send empty parameter3.

I hoppe it is clear.

Exist any other way to  to force the
pg to check if all fields are in insert statement?

regards,
ivan.



> Jc
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)




Re: pg and number of parameters by insert

From
pginfo
Date:
Hi,

Jean-Christian Imbeault wrote:

> Dennis Björklund wrote:
>  >
>  > 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.
>
> Aaaahhhh, I see the light.
>

Sarry my english is bad and I can to explain the problem ( I am speaking good
German ).

> I completely agree with you that it would be nice to get a warning.

No, it shold be error, it is not warning.

>
>
> What do the SQL specs have to say about this? Maybe it's not an error
> because the specs require it not to be an error? (I have no idea, just
> asking)
>
> If the specs don't say either way, then a warning would be nice, and an
> error better. But I bet that throwing an error would break a lot of apps ;)
>

No, it will help to correct many apps not to break.(Sorry).

> Jc

  ivan.


Re: pg and number of parameters by insert

From
Jean-Christian Imbeault
Date:
pginfo wrote:
 >
 > No, it will help to correct many apps not to break.(Sorry).

I mean it will break current applications that expect no error to be
thrown if the wrong number of values are passed into an insert statement.

Either way, I wonder what the SQL specs say about this.

What do you say thos in the know, do the SQL specs have anything to say
about what happens when an insert inserts less values than the total
number of columns?

I'm guessing it says that this is fine ... I would be very surprised if
it said otherwise and Postgres wasn't up to spec on such a simple thing.

So Ivan, if the specs specifically say that this condition is acceptable
then there is nothing to be done I would think ...

But maybe one of the developers can or someone who knows the specs can
shed some light on this if you really want more info.

Sorry I can't be of more help.

Jc


Re: pg and number of parameters by insert

From
"scott.marlowe"
Date:
On Wed, 4 Dec 2002, Jean-Christian Imbeault wrote:

> pginfo wrote:
>  >
>  > No, it will help to correct many apps not to break.(Sorry).
>
> I mean it will break current applications that expect no error to be
> thrown if the wrong number of values are passed into an insert statement.
>
> Either way, I wonder what the SQL specs say about this.
>
> What do you say thos in the know, do the SQL specs have anything to say
> about what happens when an insert inserts less values than the total
> number of columns?
>
> I'm guessing it says that this is fine ... I would be very surprised if
> it said otherwise and Postgres wasn't up to spec on such a simple thing.
>
> So Ivan, if the specs specifically say that this condition is acceptable
> then there is nothing to be done I would think ...
>
> But maybe one of the developers can or someone who knows the specs can
> shed some light on this if you really want more info.
>
> Sorry I can't be of more help.

According to the strictest interpretation of the SQL92 standard, if the
<insert column list> is absent, it is assumed to be an implicit list of
all columns.  This subject came up a while ago on hackers.  It is my
understanding that most other databases behave the way postgresql does,
i.e. they do not assume ALL the columns are implicit, just enough to
correspond to the number of args in the values() part of the query.

If this were to be changed, it would likely be something that you'd have
to turn on explicitly with a GUC in the postgresql.conf file i.e.
strict_ansi_inserts = true or something like that.