insert empty values instead of NULL in numeric fields

Question

I need your help please.

We have a PHP application running on MySQL, and we need to use PostgreSQL for a new customer.

the problem is that when we insert empty strings in a field of type numeric, we get an error.

I think we should actually use NULL instead of empty string.

However we would like to avoid having to recode all the SQL code of our application, my question is whether it is possible in the structure of a table (or elsewhere) to ensure that our numeric column accepts empty values? or convert them to NULL?

In the documentation I have seen that it is possible to define your own types, it is possible for example to create a type based on numeric and ensure that it accepts empty values? If yes how? I found no solution

Thank you

Frank Heikens · Accepted Answer · 2010-08-31 12:28:50Z

4

the problem is that when we insert empty strings in a field of type numeric, we get an error.

That's correct, a string isn't a number. Not even an empty string.

I think we should actually use NULL instead of empty string.

That's correct, your current SQL is wrong. If your MySQL-configuration would use a better SQL_MODE, your queries will also fail on MySQL. Fix your code, it's by far the best option you have.

Hacking around by creating dummy datatypes is just a hack, it's not a solution for bad SQL. The problem is your SQL, not your database.

answered Aug 31, 2010 at 12:28

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

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

Collectives™ on Stack Overflow

insert empty values instead of NULL in numeric fields

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related