Error when inserting record into mysql table

Question

I have created the following table:

CREATE TABLE IF NOT EXISTS `prices_1d` (
  `symbol` char(50) NOT NULL,
  `open_time` datetime DEFAULT NULL,
  `open` decimal(15,8) unsigned DEFAULT NULL,
  `high` decimal(15,8) unsigned DEFAULT NULL,
  `low` decimal(15,8) unsigned DEFAULT NULL,
  `close` decimal(15,8) unsigned DEFAULT NULL,
  `volume` decimal(15,8) DEFAULT NULL,
  `close_time` datetime DEFAULT NULL,
  `quote_av` decimal(15,8) DEFAULT NULL,
  `trades` bigint DEFAULT NULL,
  `tb_base_av` decimal(15,8) DEFAULT NULL,
  `tb_quote_av` decimal(15,8) DEFAULT NULL,
  PRIMARY KEY (`symbol`),
  KEY `symbol` (`symbol`),
  CONSTRAINT `FK__symbols` FOREIGN KEY (`symbol`) REFERENCES `symbols` (`symbol`) ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

which is keyed based on another symbols column.

When I want to insert a single record into this prices_1d table using the following query:

INSERT INTO prices_1d (symbol,
                                open, 
                                high, 
                                low, 
                                close, 
                                volume, 
                                close_time, 
                                quote_av,
                                trades, 
                                tb_base_av, 
                                tb_quote_av, 
                                open_time) 
                    VALUES 
                          ('AAPL', 
                          19695.87000000, 
                          19888.00000000, 
                          18001.12000000, 
                          18764.96000000, 
                          127698.76265200, 
                          '2020-12-01 23:59:59.999000', 
                          2446070334.82879867, 
                          2023802, 
                          63805.39289800, 
                          1223282816.31921670, 
                          '2020-12-01 00:00:00')
        ON DUPLICATE KEY UPDATE open=19695.87000000, 
                                high=19888.00000000, 
                                low=18001.12000000, 
                                close=18764.96000000, 
                                volume=127698.76265200, 
                                close_time='2020-12-01 23:59:59.999000', 
                                quote_av=2446070334.82879867,
                                trades=2023802, 
                                tb_base_av=63805.39289800, 
                                tb_quote_av=1223282816.31921670, 
                                open_time='2020-12-01 00:00:00'

I get the following error:

SQL Error (1264): Out of range value for column 'quote_av' at row 1

I don't understand while it fails for 'quote_av' as even changing the structure of the column from decimal(15,8) to decimal(30,10) doesn't change anything.

I thought it would be a matter of column order but I read on other posts that the order of the values inserted should not matter.

Ken Lee · Accepted Answer · 2020-12-22 04:06:08Z

2

You quote_av is decimal(15,8)

So the following value will be out of range (more than 15 in total digits)

2446070334.82879867

For this case, you will need the quote_av structure to be

decimal(18,8)

but if you insert other "longer" values (higher precision), you will need to further increase the x and y in decima(x,y)

answered Dec 22, 2020 at 4:06

Ken Lee

8,4153 gold badges14 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JejeBelfort Over a year ago

Thanks a lot! Indeed I recreated the table with the new decimal format and it worked :)

Collectives™ on Stack Overflow

Error when inserting record into mysql table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related