pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
default 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