Error updating schema in postgres with Doctrine ORM in Symfony

Question

I'm updating schema in a Symfony app. This app was not written by me, so I'm groping the dark as it were.

I have validated the schema with php bin/console doctrine:schema:validate, and get an OK, the mappings are correct. But the database schema is not in sync (I'm actually starting with an empty database...)

Then I run bin/console doctrine:schema:update --force, and get the following error:

SQLSTATE[22023]: Invalid parameter value: 7 ERROR: NUMERIC scale 10 must be between 0 and precision 2

LINE 1: ...ULL, application_id VARCHAR(255) NOT NULL, amount NUMERIC(2,...

Within the ServiceFee class:

/**
 * @ORM\Column(type="decimal", precision=2, scale=10)
 */
private $amount;

And the relevant SQL command (retrieved with bin/console doctrine:schema:update --dump-sql):

CREATE TABLE service_fees (
    id INT NOT NULL,
    created TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
    modified TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
    uuid VARCHAR(255) NOT NULL,
    organization_id VARCHAR(255) NOT NULL,
    application_id VARCHAR(255) NOT NULL,
    amount NUMERIC(2, 10) NOT NULL,
    charged BOOLEAN NOT NULL,
    payment_method_type VARCHAR(255) NOT NULL,
    transaction_number VARCHAR(255) NOT NULL,
    removed BOOLEAN NOT NULL,
    PRIMARY KEY(id)
);
CREATE UNIQUE INDEX unique_uuid ON service_fees (uuid);

After executing the command above, I start getting a new error, but I'm not sure if this is a result of an incomplete schema update:

SQLSTATE[42703]: Undefined column: 7 ERROR: column "min_value" does not exist

LINE 1: SELECT min_value, increment_by FROM "translations_id_seq"

Piotr · Accepted Answer · 2018-06-21 18:45:42Z

1

Your definition of the NUMERIC column is wrong. From PostgreSQL documentation:

The scale of a numeric is the count of decimal digits in the fractional part, to the right of the decimal point. The precision of a numeric is the total count of significant digits in the whole number, that is, the number of digits to both sides of the decimal point. So the number 23.5141 has a precision of 6 and a scale of 4. Integers can be considered to have a scale of zero.

Source: https://www.postgresql.org/docs/10/static/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL

answered Jun 21, 2018 at 18:45

Piotr

3312 silver badges6 bronze badges

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

3 Comments

icicleking Over a year ago

This is correct, I believe the original coder reversed the precision and scale. In other words, they wanted to allow numbers like 11111111.99, NUMERIC(10, 2), instead of the other way. Side note, I think the documentation paragraph should be reversed to reflect the argument order.

Piotr Over a year ago

Yes, it would be a lot more intuitive to talk about the precision first, and then about the scale. Anyway, Doctrine could raise a warning in your case, it would save you some time :)

icicleking Over a year ago

I submitted a request to change the docs to the postgres doc site. Thanks for your help

Collectives™ on Stack Overflow

Error updating schema in postgres with Doctrine ORM in Symfony

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related