CakePHP Migrations - How to specify scale and precision

Question

I'm Running CakePhp 2.7 with Migrations Plugin and a Postgresql DB. Creating a field of type 'number' and specifying length 15,4 (scale 15, precision 4 - or any length) does not actually create the field with that precision and/or scale.

          ...
 'license_fee' => array(
   'type' => 'number',
   'null' => true,
   'length' => '15,6',
   'default' => 0
  ),
        ...

The field is created with the correct type (numeric) but with no scale/precision here is the Postgres description of the created field.

license_fee               | numeric | default 0

What I was expecting to see is this

license_fee               | numeric(15,6) | default 0

I also tried using 'type' => 'decimal' but same happened. This might not be supported by the migrations plugin but I just want to know if anyone knows for sure what's going on.

I am afraid the 2.x plugin cannot be and will never become powerful enough to support all those cases. In 3.x Migrations plugin this is all fixed by design. You might want to switch here. I just released a post on [how to use 3.x Migrations in 2.x apps](http://www.dereuromark.de/2016/10/03/use-3-x-migrations-for-your-2-x-cakephp-app) by the way. — mark, Oct 06 '16 at 20:33

dype · Answer 1 · 2017-12-10T09:34:18.430

8

Found here: http://docs.phinx.org/en/latest/migrations.html

In order to create a : decimal(9,3)

$table->addColumn('distance', 'decimal', [
            'default' => null,
            'null' => false,
            'precision'=>9,
            'scale'=>3
        ]);

edited Dec 10 '17 at 09:34

answered Mar 21 '17 at 09:45

dype

500
1
4
15

This answer does not apply to the CakePHP framework, which is what I'm using. – atomCode Mar 29 '17 at 22:00
This happens to be the correct answer for CakePHP 3.5 and MySQL. The accepted answer didn't work for me. – Inigo Flores Nov 09 '17 at 10:58
This is indeed the correct answer. Note that the precision and scale should be integers instead of strings. – jurrieb Nov 19 '17 at 12:57
@jurrieb, thanks for the precisions about integers. I've update the answer. – dype Dec 10 '17 at 09:35

score 2 · Accepted Answer · answered Oct 10 '16 at 19:12

After further investigation and some help from Cake Development Corp. It turns out that the correct to way specify precision and scale is by using "limit" not "length" like I was attempting. So it should be like this:

'license_fee' => array(
   'type' => 'number',
   'null' => true,
   'limit' => '15,6', //this is where I was wrong by using length
   'default' => 0
),

This will work also if using 'type' => 'decimal' which is actually the same datatype. The end result is as expected:

license_fee               | numeric(15,6) | default 0

I hope this useful for someone.

score 0 · Answer 3 · answered Sep 28 '22 at 00:30

For 3.10 version:

$table->addColumn('name', ['type' => 'decimal', 'length' => 10, 'precision' => 3])

At /vendor/cakephp/cakephp/src/Database/Schema/TableSchema.php are the valid keys that can be used in a column:

$_columnKeys = [
    'type' => null,
    'baseType' => null,
    'length' => null,
    'precision' => null,
    'null' => null,
    'default' => null,
    'comment' => null,
];

CakePHP Migrations - How to specify scale and precision

3 Answers3

Linked