Thread: pg and number of parameters by insert
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.
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
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
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
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
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
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
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
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)
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.
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
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.