0

I want to limit numeric column type to 10 symbols before decimal separator and 4 symbols after decimal separator. I executed the following command:

ALTER TABLE scustdisc ADD COLUMN spec_price numeric(10,4)

The command executed without errors but when I try to insert value in spec_price 10.123456 I am able to do it. It should give error and the value not to be inserted. Am I wrong in my alter command?

ProfileForStack4
  • 47
  • 7
  • Possible duplicate of [How to use SQLite decimal precision notation](https://stackoverflow.com/questions/21757722/how-to-use-sqlite-decimal-precision-notation) – pritaeas Apr 11 '18 at 08:39

1 Answers1

1

SQLite has a dynamic type system and the column types have a limited impact, but can be virtually any name. They are resolved to one of TEXT, NUMERIC, INTEGER, REAL or BLOB.

  • numeric(0,0) - numeric(99999999,99999999) and more resolve to NUMERIC.

As such 10,4 4,10 etc means nothing and makes no difference to SQLite.

With one exception bar constraints a column may hold any type of value. The column type only comes into play in determining the way the data is stored.

A must read is Datatypes In SQLite Version 3

You may also find How flexible/restricive are SQLite column types?

You may be able to resolve this by using a CHECK constraint CREATE TABLE or by using a TRIGGER or multiple TRIGGERs.

You could format the number(s) appropriately when they are displayed.

You could utilise the round(x,y) function Core Functions

MikeT
  • 51,415
  • 16
  • 49
  • 68