insert empty values instead of NULL in numeric fields

Question

Hello

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

Answer 1

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.

Answer 2

Thank you for your anwser Frank.

I admit that the SQL is bad.

For the moment i do not have time to rewrite all SQL code of the application.

I can accept a hack solution before to write a new version of the application.

Can we temporarily fix that with a hack ? please explain how.

Thank you

Esab75

Answer 3

Thank you all for your anwsers.

So, if i understand, postgreSQL don't have any parameter or simple solution to allow inserting empty values in numeric fields ? that's right ?

@ Frank Heikens : I cannot use pg_query_params , we don't use parameters , i have to rewrite all the code of the application, no time no money for this for the moment.

if i use/abuse varchar like you say, the database will accept inserting non numeric values in my numeric fields, i just want to convert '' to NULL ON Insert/Update to get no database error.

@ FastAI :

try 1/3 : Maybe it can work, but i have to write stored procedures for my 200 tables. It's too much time.

try 2/3 : we have consider this, but like Frank said, we cannot know when it's real empty value, this is not simple. Sometimes we have INSERT INTO mytable (num, label) VALUES (10, ''Hello'') to insert 'Hello' into the field with quotes.

try 3/3 : Attach mysql tables on postgreSQL, I didn't undertand how to do this.

Thank you again

Answer 4

Thank you again for your anwsers, and try to help us.

As we can see there is no simple/fast solution for this problem.

So we gonna see with our customer if we can change database to MySQL,Oracle or MSSQL.

We will correct the SQL code in a future version. Thank your for your advices.

Best regards.