Thread: default value syntax - pg compared to?

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

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
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 

Folks,
I'm trying to delcare an array (of type Numeric) within a PL/pgSQL
function.  No matter what syntax I try, however, I get a compile error. 
I've tried:

DECLARE
level_array NUMERIC(8,2)[];
level_array NUMERIC[](8,2);
level_array NUMERIC[];
level_array[] NUMERIC(8,2);

and even:
level_array MONEY[100];
Please help!

-Josh Berkus



-- 
______AGLIO DATABASE SOLUTIONS___________________________                                       Josh Berkus  Complete
informationtechnology      josh@agliodbs.com   and data management solutions       (415) 565-7293  for law firms, small
businesses      fax  621-2533   and non-profit organizations.       San Francisco
 

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 byCarl van Tast