Default value in DECIMAL field

Databases
1

I see that the default value in a field of type DECIMAL is ‘0.00’. I would like to have an empty field as the default value, how can I do this?

2

specify NULL instead of NOT NULL, with no DEFAULT

3

I tried doing that, but it nothing changes, not even if I erase the fields manually. Everytime the default value goes back to zero.

4

then you’re doing something else wrong

please do a SHOW CREATE TABLE for your table, and show us how you insert a row

then compare to this –

CREATE TABLE foobar
( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
, foo DECIMAL(7,2) NOT NULL DEFAULT 0.00
, bar DECIMAL(7,2) NULL
);

INSERT INTO foobar (foo) VALUES ( 9.37 );

INSERT INTO foobar (bar) VALUES ( 9.37 );

SELECT * FROM foobar;

any guess as to what this code produces?

5

Here’s the output of SHOW CREATE TABLE (just the part of interest):


CREATE TABLE `appuntamenti` (
  `nome` varchar(255) NOT NULL,
  `data` date NOT NULL,
  `affarilordo` decimal(10,2) NOT NULL,
  `imponibile` decimal(10,2) default NULL,
  `note` text NOT NULL,
  PRIMARY KEY  (`nome`,`data`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

About your code, I’m probably wrong, but it should insert the value 9.37 in both the foo and the bar fields. Right? No XD

6

everything here works exactly as expected –


INSERT INTO appuntamenti VALUES
 ( 'nome1' , '2009-09-09' , 9.37 , NULL , 'whoa' )
,( 'nome2' , CURRENT_DATE , 0.00 , 9.37 , 'whoa' )
;
INSERT INTO appuntamenti
 ( nome , data , affarilordo , note )
VALUES
 ( 'nome3' , '2009-09-09' , 9.37 , 'whoa' )
;
INSERT INTO appuntamenti
 ( nome , data , imponibile , note )
VALUES
 ( 'nome4' , '2009-09-09' , 9.37 , 'whoa' )
;
SELECT * FROM appuntamenti
;
7

Uhm, yes, you are right. It’s probably some Javascript code that makes me see it as 0 instead than as blank…

Thank you :slight_smile: