[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 12186
  • Last Modified:

invalid input syntax for type numeric

say i have below:
CREATE TABLE test.testing
(
  studentid character varying(5),
  amount_paid numeric
)

and i try to insert

INSERT INTO test.testing(
            studentid, amount_paid)
    VALUES ('beb123', '' );

and i received this message

ERROR:  invalid input syntax for type numeric: ""

********** Error **********

ERROR: invalid input syntax for type numeric: ""
SQL state: 22P02

is that mean null is not allowed in numeric data type
0
tankergoblin
Asked:
tankergoblin
  • 4
  • 3
  • 3
  • +1
5 Solutions
 
phuctranCommented:
why do you want to have null in numeric field?  It will make your life more difficult later.
http://www.databasedesign-resource.com/null-values-in-a-database.html


0
 
geowrianCommented:
You are putting an empty string into a numeric field. You need to put a number there or NULL (no quotes)
0
 
tankergoblinAuthor Commented:
how to put NULL

like this?
INSERT INTO ediprice_a.testing(
            studentid, amount_paid)
    VALUES ('beb123',  );
i receive error too
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
tankergoblinAuthor Commented:
why do you want to have null in numeric field?  It will make your life more difficult later.

what you suggest me to do.
my data is in number and null will happen in that column..
0
 
geowrianCommented:
Like this:
INSERT INTO ediprice_a.testing(
            studentid, amount_paid)
    VALUES ('beb123',  NULL);
0
 
geowrianCommented:
@phuctran
Agreed in general, but it's not always possible or  most effective. I would suggest that the OP consider putting 0 instead of NULL as that is the most logical value to be placed in that field. Putting NULL in that field will make your coding and SQL later more complicated as it will have to check for NULL values before doing calculations on that field.
0
 
phuctranCommented:
I cannot say what number should you put in that field but you should not put null there even though the field allows you to do so.  For example, if that field is always positive (high, length, hours ...) then you can put a negative number instead of null.  Depends on your situation.  As geowrian said, 0 is a good one.
0
 
tankergoblinAuthor Commented:
but if i want to left it blank is it possible

also i insert data into database is base on the data in a text file. that mean i m manually add data into database.

example:
in my text file i have

name  mark
alex   54
helen
david 44

how to put 0 in mark on helen row.

i dun understand why postgresql numeric data type cannot accept empty column...
0
 
tankergoblinAuthor Commented:
if there is no other way and by putting 0 is only solution then how can i put 0 on each empty column detected
0
 
cminearCommented:
geowrian did give an example of how to perform an INSERT with a NULL; or as has been pointed out in your other question, you can just drop that column from the list of column names and don't submit the "" value to it.
  INSERT INTO ediprice_a.testing(studentid) VALUES ('beb123');

As to the issue of NULL vs. 0, as you show in your example data file, there isn't a value for the mark, so don't force it.  Yes, there are many good arguments for why NULLs should be avoided in a database.  But there are also many uses for them.  And arguments about NULLs making the SQL coding harder are just an excuse for not wanting to think about or cope with what they may mean to the underlying model.

As an example, let's say we want a table for recording patient visits to a doctor's office.  It could have a schema like this (just trying to capture enough for my example --- not intended to be complete).
Visit:  patient_id, visit_id, height, weight, arrival, departure, notes

Every time a patient visits, they are expected to have their height and weight measured.  But there may be cases where weight isn't measured: the scale is currently broken, a new assistant forgets to do it, or the patient has a stroke on the way to the scale.   (It's not likely, but it could happen.)  Do we enter "0" for the weight, or leave it empty (NULL)?  I would say we leave it empty, because that easily conveys that "we do not know what the weight is."  If we put in 0 just so we don't have a NULL, we are opening ourselves to a different coding issue which may be hidden and thus easily get missed.

Say we have a report generated to get the minimum, maximum and average weights of patients.  With the 0's in there, it gives us a skewed average and a wrong minimum.  "So just check that the weight is not zero" could be the answer.  My counter-answer is "and how is this any different than checking that the value is not NULL?"

The point is you have to think critically about your model.  If a value should never be empty, not even temporarily, then you make the column NOT NULL and you go on.  But if there are valid reasons with your data where a value could be unknown, then you let the NULLs be added to the database and deal with them, because that is just how your data lays out.  Yes, "amount_paid' does tend to suggest that it should always have a value.  But if you just put in "0" when you don't know the value, how do you then tell when something was given away for free (also "0")?  If you instead enter "-1" for NULL, how do you then tell when you paid to have some person take the item, rather than that person paying you (opposite transaction means negative)?
0
 
phuctranCommented:
It is true that you can use null in your numeric field and it is based on your situation to use or not use null.  The results are the same.  

However, to me, a numeric field should not have null values.  When I query a numeric field, I expect a number.  If the height of a patient is 0, I know that it is not true right away.  Normally, I query then save into variables in my program.  I just don't want to get a null value where I expect a number.  Of course, "just write a query that change null to 0" before return the recordset.  That is true also.  There are many ways to get to the destination.  To me, it is easier that the numeric field has numbers.

So, maybe in your situation, it is easier to have null in the numeric field.

@cminear
For the "amount_paid" you pointed out, to me, I have to raise the error message (log, email, ...) for that record and do not place a null or 0 in the field (negative does not work).  I would not place anything in the database that I am not sure.  If the field is the number of incoming calls and you don't have it in your importing text file, then it may be safe to use 0 (in some extreme cases, it is not).  So, basically, using null or numbers depends on your data.  To me, I will try not use null if possible.  In that way, I can force that when anyone writes a INSERT query to save into database, he/she must know what fields he/she is saving because the numeric field is NOT NULL.

Anyway, this is just my opinion.  I am sorry that I did not give any solutions for the OP.


0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now