Postgres incrementing null value in a column

Question

In Postgres 9.3 when a field in a table has a null value the following expression doesn't work:

update table_statatistic set members = members + 1 WHERE user_id = $1;

However when the field has an integer value then this query increments it by 1 without a problem.

The questions are:

Why is this happening.
How to fix it.

Why? Because every expression involving null yields null. That's how SQL is specified. modern-sql.com/concept/null — user330315
– user330315, Commented Mar 23, 2018 at 7:22
I think you have that problem because you abuse SQL NULL. The correct semantics are "unknown", but you want to treat it as 0. Maybe you should add a DEFAULT 0 clause to the column definition (and add a NOT NULL while you're at it). Not only will that make your problem go away, it will also make all queries simpler. — Laurenz Albe
– Laurenz Albe, Commented Mar 23, 2018 at 8:02

Serkan Arslan · Accepted Answer · 2018-03-23 06:56:37Z

14

you need to use coalesce for checking null values

update table_statatistic set members = coalesce(members, 0) + 1 WHERE user_id = $1

answered Mar 23, 2018 at 6:56

Serkan Arslan

13.4k4 gold badges33 silver badges46 bronze badges

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

Comments

Jay Shankar Gupta · Accepted Answer · 2018-03-23 08:51:17Z

2

Use COALESCE() instead: The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is often used to substitute a default value for null values when data is retrieved for display

UPDATE table_statatistic SET members = COALESCE(members,0) + 1 WHERE user_id = $1;

edited Mar 23, 2018 at 8:51

answered Mar 23, 2018 at 6:56

Jay Shankar Gupta

6,1281 gold badge14 silver badges31 bronze badges

6 Comments

Jimski Over a year ago

Why do I need to use coalesce. Nothing + 1 = 1. Why is this happening.

Jay Shankar Gupta Over a year ago

A field with a NULL value is a field with no value. So you can't add 1 with no value Null is not a value.

Jimski Over a year ago

This is counter intuitive and doesn't make logical sense, null means nothing. In many programming languages you can add integer to null just fine.

Jay Shankar Gupta Over a year ago

can you tell me the names of programming languages?

user330315 Over a year ago

@Jimski null is not 0 those are two very different things. null is the absence of any information. What if I asked you to add a chair to your spaceship. How many chairs would your spaceship have after that?

|

Collectives™ on Stack Overflow

Postgres incrementing null value in a column

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related