pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddefault value syntax - pg compared to?
4+ messages / 4 participants
[nested] [flat]
* default value syntax - pg compared to?
@ 2001-03-07 10:01 Emils Klotins <emils@mail.grafton.lv>
2001-03-12 20:18 ` Re: default value syntax - pg compared to? Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 4+ messages in thread
From: Emils Klotins @ 2001-03-07 10:01 UTC (permalink / raw)
To: pgsql-sql
Hello,
do you happen to know, if there is any sqlserver that WON'T handle
inserting default value if it is not specified in the INSERT field list?
I am particularly interested in ORACLE, INFORMIX and MYSQL .
Eg.
CREATE TABLE test
(
id integer default 5,
fld1 text
);
INSERT INTO test (fld1) VALUES ('blahblah');
So, is there any sqlserver that under these conditions will NOT
make id = 5 for the inserted row?
Background:
There is a package called phprojekt where the developer has
created some workarounds for different handlings of auto-
incrementing fields (sequences) for the above databases.
The only place he uses them, however, is in the INSERT
stataments (and he does not need to know the new id value for the
inserted row).
So I suggested he rewrite INSERTs explicitly specifying field
names and excluding the id field, rather than putting a server-
specific "default value" token (which postgres does not support
anyway). Will it work for the above other dbs?
TIA for answers!
Emils
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: default value syntax - pg compared to?
2001-03-07 10:01 default value syntax - pg compared to? Emils Klotins <emils@mail.grafton.lv>
@ 2001-03-12 20:18 ` Josh Berkus <josh@agliodbs.com>
2001-03-12 23:34 ` Re: default value syntax - pg compared to? Carl van Tast <vanTast.no@spam.netway.at>
0 siblings, 1 reply; 4+ messages in thread
From: Josh Berkus @ 2001-03-12 20:18 UTC (permalink / raw)
To: Emils Klotins <emils@mail.grafton.lv>; pgsql-sql
Emils,
> do you happen to know, if there is any sqlserver that WON'T handle
> inserting default value if it is not specified in the INSERT field
> list?
> I am particularly interested in ORACLE, INFORMIX and MYSQL .
MS SQL 6.5, 7.0 and 2000 all support default values on incremental
fields ... with some caveats:
1. Unlike PostgreSQL, MSSQL server will not permit you to override an
"Identity Value" auto-incrementing field;
2. MSSQL 6.5 and 7.0 do not support user-defined functions as
default-values (or, for that matter, user-defined functions at all).
Also, you should be aware that the pgODBC driver for Win32 clients does
not recognize PostgreSQL sequences correctly when called as default
vaues for other than a SERIAL field (i.e. if you use the DEFAULT
NEXTVAL('sequence') construction rather than the SERIAL type, pgODBC
will error out on INSERTS).
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: default value syntax - pg compared to?
2001-03-07 10:01 default value syntax - pg compared to? Emils Klotins <emils@mail.grafton.lv>
2001-03-12 20:18 ` Re: default value syntax - pg compared to? Josh Berkus <josh@agliodbs.com>
@ 2001-03-12 23:34 ` Carl van Tast <vanTast.no@spam.netway.at>
0 siblings, 0 replies; 4+ messages in thread
From: Carl van Tast @ 2001-03-12 23:34 UTC (permalink / raw)
To: pgsql-sql
On Mon, 12 Mar 2001 20:52:02 +0000 (UTC), josh@agliodbs.com ("Josh
Berkus") wrote:
>1. Unlike PostgreSQL, MSSQL server will not permit you to override an
>"Identity Value" auto-incrementing field;
That's almost correct. You cannot *update* an identity column, but you
can override it on insert if you use
SET IDENTITY_INSERT tablename ON
create table foo (id integer identity, txt varchar(10))
go
insert into foo (txt) values ('a')
insert into foo (txt) values ('b')
set identity_insert foo on
insert into foo (id, txt) values (10, 'c')
set identity_insert foo off
insert into foo (txt) values ('b')
select * from foo
id txt
----------- ----------
1 a
2 b
10 c
11 d
(4 row(s) affected)
.02 by
Carl van Tast
^ permalink raw reply [nested|flat] 4+ messages in thread
* default value syntax - pg compared to?
@ 2001-03-12 17:46 emils@mail.grafton.lv
0 siblings, 0 replies; 4+ messages in thread
From: emils@mail.grafton.lv @ 2001-03-12 17:46 UTC (permalink / raw)
Hello,
do you happen to know, if there is any sqlserver that WON'T handle
inserting default value if it is not specified in the INSERT field list? I
am particularly interested in ORACLE, INFORMIX and MYSQL .
Eg.
CREATE TABLE test
(
id integer default 5,
fld1 text
);
INSERT INTO test (fld1) VALUES ('blahblah');
So, is there any sqlserver that under these conditions will NOT
make id = 5 for the inserted row?
Background:
There is a package called phprojekt where the developer has
created some workarounds for different handlings of auto-
incrementing fields (sequences) for the above databases.
The only place he uses them, however, is in the INSERT
stataments (and he does not need to know the new id value for the
inserted row).
So I suggested he rewrite INSERTs explicitly specifying field
names and excluding the id field, rather than putting a server-
specific "default value" token (which postgres does not support
anyway). Will it work for the above other dbs?
TIA for answers!
Emils
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-03-12 23:34 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-07 10:01 default value syntax - pg compared to? Emils Klotins <emils@mail.grafton.lv>
2001-03-12 20:18 ` Josh Berkus <josh@agliodbs.com>
2001-03-12 23:34 ` Carl van Tast <vanTast.no@spam.netway.at>
2001-03-12 17:46 default value syntax - pg compared to? emils@mail.grafton.lv
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox