PostgreSql: Disable Numeric value out of range

Question

I'm creating an API with PHP and PostgreSQL. This is one of the query I use:

SELECT id,  email,  first_name,  last_name, [...] FROM clients WHERE id = ?

The column "id" is an integer and I don't need a bigger type. ? is replaced by a value coming from the user.

But if the user sends a too big integer (9999999999999) PostgreSQL returns an error:

SQLSTATE[22003]: Numeric value out of range: 7 ERROR: value \"99999999999999999999999\" is out of range for type integer

Should I check the overflow in the PHP logic ?

Can I turn this error into warning ? The same query on MySQL doesn't fail...

Thank you

Craig Ringer · Accepted Answer · 2014-06-06 13:04:59Z

3

"The same query on MySQL doesn't fail" isn't a great recommendation (unless you're running MySQL in ANSI STRICT mode). MySQL accepts 0000-00-00 as a date, coerces invalid entries to nulls in places, etc, and often does so with at most a warning to tell you it's mangled your data.

That said, I do wish PostgreSQL offered versions of type conversion functions that returned an error code / returned null, so I could explicitly write:

INSERT INTO mytable(blah) VALUES (bigint_in_nullifinvalid('99999999999999999'));

It doesn't, though.

You can write such a function in PL/PgSQL but it's not efficient, and there's none built-in.

While I'm really happy that PostgreSQL won't mangle my data without being asked to, there are times I'd rather like it to mangle my data when I did ask it to.

In general it's better to:

Validate on the application side first;
Let the database enforce integrity; and
Trap errors and report them to the user. You can match the SQLSTATE return to find out details about the error.

PostgreSQL doesn't have any "insert invalid data with warnings" option.

answered Jun 6, 2014 at 13:04

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

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

1 Comment

Thomas K Over a year ago

Ok, I'll add a if statement to check the overflow of "integer type". Thank you

Collectives™ on Stack Overflow

PostgreSql: Disable Numeric value out of range

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