postgresql handling null values

Question

I am using postgresql for database. And the condition on the field is not null. When user keeps blank for that field and hit on enter its giving null pointer exception becuase in postgresql for integer it wont accept accept "".Any idea about how to handle this case

Null pointer exception? Are you sure this is exactly what you are getting? — Quassnoi
– Quassnoi, Commented Apr 19, 2011 at 14:08
What is your development environment? Language? Persistence layer? — Eelke
– Eelke, Commented Apr 19, 2011 at 14:09
Before asking how to avoid a problem, you should ask yourself "what do I expect my system to do in this case"? (when a integer field is left blank in an input form). — leonbloy
– leonbloy, Commented Apr 19, 2011 at 14:12

Sam Choukri · Accepted Answer · 2011-04-19 18:21:29Z

4

If you can modify the INSERT statement, you can use the COALESCE() function to automatically replace a NULL value with another value you specify.

Here is an example to automatically insert 0 (zero) in the status field, when the $status variable is not set:

INSERT INTO foo (name, status) VALUES ($name, COALESCE($status, 0) );

The COALESCE function can take any number of arguments and returns the first non-NULL value it finds. This comes in handy when you have a "waterfall" of values you want to try before settling on the default value. For example:

COALESCE($status1, $status2, $status3, $status4, 0)

answered Apr 19, 2011 at 18:21

Sam Choukri

1,90411 silver badges17 bronze badges

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

Comments

Quassnoi · Accepted Answer · 2011-04-19 14:09:14Z

3

Since your field does not accept NULL, you should set it with an actual integer.

Replace the empty string with a 0 (or other default value) on the client side.

answered Apr 19, 2011 at 14:09

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

2 Comments

tapps Over a year ago

Can you check if I am in the right track?var abc = document.getElementById('abc').value; if(abc == "" ){ abc.value = 0; }

Quassnoi Over a year ago

@divya: yes, something like that. But since this is javascript and may be forged, you should additionally sanitize any user input in your server-side db layer.

elvenbyte · Accepted Answer · 2011-04-19 14:08:37Z

1

Check your form with javascript, and if is coming a "" from the textbox, just assign 0 to that value. Then pass that value as an Integer value and that's all.

answered Apr 19, 2011 at 14:08

elvenbyte

7741 gold badge17 silver badges34 bronze badges

2 Comments

tapps Over a year ago

Can you check if I am in the right track?var abc = document.getElementById('abc').value; if("".equals(abc)){ abc.value = 0; }

elvenbyte Over a year ago

Yes, you are. It's something like that.

Collectives™ on Stack Overflow

postgresql handling null values

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related