Postgres, replace all integer values with NULL

Question

I'm trying to replace all values in a column where the value is 0 with NULL

SELECT score
REPLACE(score, 0, ISNULL)
FROM kpis
WHERE score=0

or

SELECT score
REPLACE(score, 0, ISNULL)
FROM kpis

But I get a syntax error:

ERROR:  syntax error at or near "REPLACE"
LINE 2: REPLACE(score, 0, ISNULL)

Use a case expression instead. (Also, REPLACE is for strings, not integers.) — jarlh
– jarlh, Commented Mar 24, 2021 at 9:11
It's unclear to me if you want to just return NULL in the SELECT, or permanently change the values in the table (i.e. run an UPDATE) — user330315
– user330315, Commented Mar 24, 2021 at 9:47

user330315 · Accepted Answer · 2021-03-24 09:48:03Z

2

You can use nullif() which is functionally equivalent to a CASE expression, but a bit shorter to write.

SELECT nullif(score, 0) as score
FROM kpis

answered Mar 24, 2021 at 9:48

user330315

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

Comments

sas · Accepted Answer · 2021-03-24 09:26:22Z

2

Replace all values in a column where the value is 0 with NULL

update kpis set score = NULL where score = 0;

Replace all integer values in a column with NULL

update kpis set score = NULL where score ~ '^[0-9]*$'

edited Mar 24, 2021 at 9:26

answered Mar 24, 2021 at 9:15

sas

5522 silver badges9 bronze badges

Comments

Zaynul Abadin Tuhin · Accepted Answer · 2021-03-24 09:26:00Z

1

use case when

SELECT 
case when score = 0 then null else score end as score
from kpis

answered Mar 24, 2021 at 9:26

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Comments

Kazi Mohammad Ali Nur Romel · Accepted Answer · 2021-03-24 10:17:26Z

1

Instead of replace you can use NULLIF(). Below query will set score=null when score is 0.

update kpis set score=NULLIF(score, 0)

Or you can simple use below query as suggested by @Sas.

update kpis set score = NULL where score = 0;

edited Mar 24, 2021 at 10:17

answered Mar 24, 2021 at 9:26

Kazi Mohammad Ali Nur Romel

16.2k2 gold badges17 silver badges29 bronze badges

3 Comments

user330315 Over a year ago

This is wrong. coalesce() returns the first non-null value, so your expression will always return the score or 0 - but never NULL

Kazi Mohammad Ali Nur Romel Over a year ago

You are absolutely right. I have changed my answer. Thanks a ton.

Richard Huxton Over a year ago

Note that this will update the whole table, even if you aren't changing the score. Better is to apply a WHERE clause as in one of the other answers, at which point you don't need the NULLIF.

Collectives™ on Stack Overflow

Postgres, replace all integer values with NULL

4 Answers 4

Comments

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related