insert statement using default values
Esteban asked how to insert a row using all default values in Informix, as MySQL (INSERT INTO t VALUES ()) and Sybase (VALUES (DEFAULT)) allow; Informix gives a syntax error. Replies explained Informix columns default to NULL unless a DEFAULT clause is given, but Jonathan Leffler confirmed Informix has no 'all defaults'/DEFAULT keyword support in INSERT (noting it as a wish-list item). Workarounds offered: name at least one column, e.g. INSERT INTO test3 (col1) VALUES (NULL), or insert into a SERIAL column with 0 so other columns take their defaults. Esteban planned to modify his Hibernate dialect instead.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
the following syntax works for mysql:
insert into table_name values () ;
and the following works for sybase
insert into table_name values ( DEFAULT );
under informix I received a syntax error.
I wonder if there is a way to do it.
What I 've got is:
create table "informix".test3
(
col1 integer,
col2 char(10)
);
revoke all on "informix".test3 from "public";
insert into test3 ( col1 ) values ( 1 );will insert 1 for col1 and null for col2
but
insert into test3 values ( );
returns syntax error.
Any help would be great,
thanks
esteban.-
Esteban Casuscelli wrote:
> the following syntax works for mysql:
> insert into table_name values () ;>
> and the following works for sybase
> insert into table_name values ( DEFAULT );>
> under informix I received a syntax error.
> I wonder if there is a way to do it.
> What I 've got is:
>
> create table "informix".test3
> (
> col1 integer,
> col2 char(10)
> );
> revoke all on "informix".test3 from "public";
You will want to define what the default values are for each column where
you will allow a default. For example:
CREATE TABLE test3
(col1 INTEGER DEFAULT 10,
col2 CHAR(10) DEFAULT "UNASSIGNED");
> insert into test3 ( col1 ) values ( 1 );> will insert 1 for col1 and null for col2
The INSERT that you've shown would now insert a 1 for col1 and "UNASSIGNED"
for col2.
You could also issue a statement such as:
INSERT INTO test3 (col2) VALUES ("ABC");
and col1 would contain a 10 and col2 would contain "ABC".
> but
> insert into test3 values ( );>
> returns syntax error.
> Any help would be great,
Until just now, I've never tried to insert a row with all default values.
In a few simple tests, I haven't discovered how to do it - assuming it is
even possible, and the documentation that I've read does not give any
indication that it is possible. I'm curious, though, why you would want to
do such a thing; you could end up with multiple identical rows. Of course,
without a unique index, you could still end up there, but....
--
June Hunt
June C. Hunt wrote:
> Esteban Casuscelli wrote:
>
>>the following syntax works for mysql:
>>insert into table_name values () ;
Well, that should only work if there are zero columns in the table.
>>and the following works for sybase
>>insert into table_name values ( DEFAULT );
And that should only work if there's one column.
>>under informix I received a syntax error.
>>I wonder if there is a way to do it.
>>What I 've got is:
>>
>>create table "informix".test3
>> (
>> col1 integer,
>> col2 char(10)
>> );
>>revoke all on "informix".test3 from "public";>
>
> You will want to define what the default values are for each column where
> you will allow a default. For example:
>
> CREATE TABLE test3
> (col1 INTEGER DEFAULT 10,
> col2 CHAR(10) DEFAULT "UNASSIGNED");>
>
>>insert into test3 ( col1 ) values ( 1 );>>will insert 1 for col1 and null for col2
>
>
> The INSERT that you've shown would now insert a 1 for col1 and "UNASSIGNED"
> for col2.
>
> You could also issue a statement such as:
>
> INSERT INTO test3 (col2) VALUES ("ABC");>
> and col1 would contain a 10 and col2 would contain "ABC".
>
>
>>but
>>insert into test3 values ( );>>
>>returns syntax error.
>>Any help would be great,
>
>
> Until just now, I've never tried to insert a row with all default values.
> In a few simple tests, I haven't discovered how to do it - assuming it is
> even possible, and the documentation that I've read does not give any
> indication that it is possible. I'm curious, though, why you would want to
> do such a thing; you could end up with multiple identical rows. Of course,
> without a unique index, you could still end up there, but....
There isn't a way to do it in Informix yet. Sybase has it - that's
useful to know. DB2 has it. More ammunition - it was on my list of
nice to have's.
INSERT INTO SomeTable(Col1, Col2, ..., ColN)
VALUES(Val1, DEFAULT, ..., DEFAULT);
Note that no rational database design would permit every column to be
defaulted. Note that there are implications for loaders like
DB-Import (and, to a lesser extent, unloaders).
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
Thanks June,
Just to let you know if you don't use the DEFAULT clause the default
will be a
null value. So if you use the default clause or not you will have
default values for each column.
The problem is that I couldn't find a way to insert all default values
for a table. Same thing works for mysql and sybase....
Under informix at least you need to specify one column to obtain
default values with the other columns.
Yes you are right, you could still end up with more than one row with
all NULL values and doesn't make sense.
I 'm running some hibernate testings and one of them insert all
default values to a table. That 's why I m doing this.
thank you for your help
esteban.-
"June C. Hunt" <june_c_hunt@hotmail.com> wrote in message news:<2m2ilhFhvpg3U1@uni-berlin.de>...
> Esteban Casuscelli wrote:
> > the following syntax works for mysql:
> > insert into table_name values () ;> >
> > and the following works for sybase
> > insert into table_name values ( DEFAULT );> >
> > under informix I received a syntax error.
> > I wonder if there is a way to do it.
> > What I 've got is:
> >
> > create table "informix".test3
> > (
> > col1 integer,
> > col2 char(10)
> > );
> > revoke all on "informix".test3 from "public";>
> You will want to define what the default values are for each column where
> you will allow a default. For example:
>
> CREATE TABLE test3
> (col1 INTEGER DEFAULT 10,
> col2 CHAR(10) DEFAULT "UNASSIGNED");>
> > insert into test3 ( col1 ) values ( 1 );> > will insert 1 for col1 and null for col2
>
> The INSERT that you've shown would now insert a 1 for col1 and "UNASSIGNED"
> for col2.
>
> You could also issue a statement such as:
>
> INSERT INTO test3 (col2) VALUES ("ABC");>
> and col1 would contain a 10 and col2 would contain "ABC".
>
> > but
> > insert into test3 values ( );> >
> > returns syntax error.
> > Any help would be great,
>
> Until just now, I've never tried to insert a row with all default values.
> In a few simple tests, I haven't discovered how to do it - assuming it is
> even possible, and the documentation that I've read does not give any
> indication that it is possible. I'm curious, though, why you would want to
> do such a thing; you could end up with multiple identical rows. Of course,
> without a unique index, you could still end up there, but....
Jonathan Leffler <jleffler@earthlink.net> wrote in message news:<Y60Lc.8842$Qu5.3572@newsread2.news.pas.earthlink.net>...
> June C. Hunt wrote:
>
> > Esteban Casuscelli wrote:
> >
> >>the following syntax works for mysql:
> >>insert into table_name values () ;>
> Well, that should only work if there are zero columns in the table.
No, the table has 4 columns ( serail , varchar, integer, integer ) and
it works.
>
> >>and the following works for sybase
> >>insert into table_name values ( DEFAULT );>
> And that should only work if there's one column.
No, the table has 4 colums :-)
>
> >>under informix I received a syntax error.
> >>I wonder if there is a way to do it.
> >>What I 've got is:
> >>
> >>create table "informix".test3
> >> (
> >> col1 integer,
> >> col2 char(10)
> >> );
> >>revoke all on "informix".test3 from "public";> >
> >
> > You will want to define what the default values are for each column where
> > you will allow a default. For example:
> >
> > CREATE TABLE test3
> > (col1 INTEGER DEFAULT 10,
> > col2 CHAR(10) DEFAULT "UNASSIGNED");> >
> >
> >>insert into test3 ( col1 ) values ( 1 );> >>will insert 1 for col1 and null for col2
> >
> >
> > The INSERT that you've shown would now insert a 1 for col1 and "UNASSIGNED"
> > for col2.
> >
> > You could also issue a statement such as:
> >
> > INSERT INTO test3 (col2) VALUES ("ABC");> >
> > and col1 would contain a 10 and col2 would contain "ABC".
> >
> >
> >>but
> >>insert into test3 values ( );> >>
> >>returns syntax error.
> >>Any help would be great,
> >
> >
> > Until just now, I've never tried to insert a row with all default values.
> > In a few simple tests, I haven't discovered how to do it - assuming it is
> > even possible, and the documentation that I've read does not give any
> > indication that it is possible. I'm curious, though, why you would want to
> > do such a thing; you could end up with multiple identical rows. Of course,
> > without a unique index, you could still end up there, but....
>
> There isn't a way to do it in Informix yet. Sybase has it - that's
> useful to know. DB2 has it. More ammunition - it was on my list of
> nice to have's.
>
> INSERT INTO SomeTable(Col1, Col2, ..., ColN)
> VALUES(Val1, DEFAULT, ..., DEFAULT);>
> Note that no rational database design would permit every column to be
> defaulted. Note that there are implications for loaders like
> DB-Import (and, to a lesser extent, unloaders).
Ok, I think there isn't a way to do it and I understand that this
doesn't make sense from a relational database point of view.
I think i will need to change the hibernate dialect to fix this.
thank you for you help,
esteban.-
Esteban, what does :
"the following syntax works for mysql
insert into table_name values ()"
insert into the table? How is "table_name" defined?What result are you looking for? One row with a default value for
every column?
if so the closest I think I can get is :
CREATE TABLE test3
(col1 INTEGER DEFAULT 10,
col2 CHAR(10) DEFAULT "UNASSIGNED"
col3 SERIAL);
INSERT INTO test3(col3) VALUES (0);
This will insert 1 row, generate a unique serial number and then
default the other columns.
Thanks
johnd
Jonathan Leffler <jleffler@earthlink.net> wrote in message news:<Y60Lc.8842$Qu5.3572@newsread2.news.pas.earthlink.net>...
> June C. Hunt wrote:
>
> > Esteban Casuscelli wrote:
> >
> >>the following syntax works for mysql:
> >>insert into table_name values () ;>
> Well, that should only work if there are zero columns in the table.
>
> >>and the following works for sybase
> >>insert into table_name values ( DEFAULT );>
> And that should only work if there's one column.
>
> >>under informix I received a syntax error.
> >>I wonder if there is a way to do it.
> >>What I 've got is:
> >>
> >>create table "informix".test3
> >> (
> >> col1 integer,
> >> col2 char(10)
> >> );
> >>revoke all on "informix".test3 from "public";> >
> >
> > You will want to define what the default values are for each column where
> > you will allow a default. For example:
> >
> > CREATE TABLE test3
> > (col1 INTEGER DEFAULT 10,
> > col2 CHAR(10) DEFAULT "UNASSIGNED");> >
> >
> >>insert into test3 ( col1 ) values ( 1 );> >>will insert 1 for col1 and null for col2
> >
> >
> > The INSERT that you've shown would now insert a 1 for col1 and "UNASSIGNED"
> > for col2.
> >
> > You could also issue a statement such as:
> >
> > INSERT INTO test3 (col2) VALUES ("ABC");> >
> > and col1 would contain a 10 and col2 would contain "ABC".
> >
> >
> >>but
> >>insert into test3 values ( );> >>
> >>returns syntax error.
> >>Any help would be great,
> >
> >
> > Until just now, I've never tried to insert a row with all default values.
> > In a few simple tests, I haven't discovered how to do it - assuming it is
> > even possible, and the documentation that I've read does not give any
> > indication that it is possible. I'm curious, though, why you would want to
> > do such a thing; you could end up with multiple identical rows. Of course,
> > without a unique index, you could still end up there, but....
>
> There isn't a way to do it in Informix yet. Sybase has it - that's
> useful to know. DB2 has it. More ammunition - it was on my list of
> nice to have's.
>
> INSERT INTO SomeTable(Col1, Col2, ..., ColN)
> VALUES(Val1, DEFAULT, ..., DEFAULT);>
> Note that no rational database design would permit every column to be
> defaulted. Note that there are implications for loaders like
> DB-Import (and, to a lesser extent, unloaders).
Esteban Casuscelli wrote:
> Just to let you know if you don't use the DEFAULT clause the default
> will be a null value. So if you use the default clause or not you will
have
> default values for each column.
>
> The problem is that I couldn't find a way to insert all default values
> for a table. Same thing works for mysql and sybase....
> Under informix at least you need to specify one column to obtain
> default values with the other columns.
>
> Yes you are right, you could still end up with more than one row with
> all NULL values and doesn't make sense. [remainder snipped]
Oh - you *wanted* a row of all NULL values? Sorry, I misunderstood what you
were trying to accomplish. So, with the following original example:
insert into test3 ( col1 ) values ( 1 );will insert 1 for col1 and null for col2
It is col1 that is the problem, not col2? As you have found, you must
specify at least one column... How about:
insert into test3 (col1) values (NULL);
Not the syntax you are used to, but it seems that it would give you the
results you seek... Unless, of course, the major point of your original
question goes to syntax rather than results.
--
June Hunt