Solved

invalid input syntax for type numeric

Posted on 2009-05-12
11
10,550 Views
Last Modified: 2012-08-14
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
Comment
Question by:tankergoblin
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 3

Accepted Solution

by:
phuctran earned 300 total points
ID: 24371922
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
 
LVL 12

Expert Comment

by:geowrian
ID: 24371983
You are putting an empty string into a numeric field. You need to put a number there or NULL (no quotes)
0
 
LVL 7

Author Comment

by:tankergoblin
ID: 24371995
how to put NULL

like this?
INSERT INTO ediprice_a.testing(
            studentid, amount_paid)
    VALUES ('beb123',  );
i receive error too
0
 
LVL 7

Author Comment

by:tankergoblin
ID: 24371998
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
 
LVL 12

Expert Comment

by:geowrian
ID: 24372004
Like this:
INSERT INTO ediprice_a.testing(
            studentid, amount_paid)
    VALUES ('beb123',  NULL);
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 12

Assisted Solution

by:geowrian
geowrian earned 100 total points
ID: 24372017
@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
 
LVL 3

Assisted Solution

by:phuctran
phuctran earned 300 total points
ID: 24380596
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
 
LVL 7

Author Comment

by:tankergoblin
ID: 24381142
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
 
LVL 7

Author Comment

by:tankergoblin
ID: 24381145
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
 
LVL 12

Assisted Solution

by:cminear
cminear earned 100 total points
ID: 24385403
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
 
LVL 3

Assisted Solution

by:phuctran
phuctran earned 300 total points
ID: 24386971
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now