Replace NULL in my Table with <SOME VALUE> in PostgreSQL

Question

Upon searching ways to replace NULL values in my table with 0 on Stack Overflow, it appears that many threads I've found point to using the COALESCE function. E.g. postgresql return 0 if returned value is null

I understand that the COALESCE function "replaces" null values for your specific query; however, the table itself remains untouched. That is, if you queried the table again in a separate query without COALESCE, null values would still exist.

My question is, is there a way for me to replace NULL values permanently in my table with a specified value (like 0) so I don't have to COALESCE in every query? And as an extension to my question, is it considered bad practice to modify the original table instead of doing manipulations in queries?

YOu can add a "Not Null" constraint on that column of table. That would restrict any null in future to be inserted — lat long
– lat long, Commented May 23, 2018 at 4:39
Oh, I'm copying the data into the table from a CSV into my table to begin with so NOT NULL didn't work for me (I believe it's because I'm copying in after defining the table? Correct me if I'm wrong) — ptk
– ptk, Commented May 23, 2018 at 4:41
in this case COALESCE is a good option. otherwise you can add a trigger on the insert of the table and replace any Null value from there, regardless from which query that insert is coming from — lat long
– lat long, Commented May 23, 2018 at 4:46
Around 5 columns have NULL values scattered within them? I just thought that it would be a lot cleaner if I just removed them initially to avoid COALESCING too many times later in queries but tbh I'm a newbie at SQL so not sure about best practices etc. — ptk
– ptk, Commented May 23, 2018 at 4:50

SQLChao · Accepted Answer · 2018-05-23 04:55:03Z

16

You can just do an UPDATE:

UPDATE table SET col1 = 0 WHERE col1 IS NULL;

This should effectively update all matching records on your table

edited May 23, 2018 at 4:55

SQLChao

7,8851 gold badge19 silver badges33 bronze badges

answered May 23, 2018 at 4:42

asp

3012 silver badges6 bronze badges

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

2 Comments

ptk Over a year ago

thanks for the suggestion! this definitely works; however, I'm not sure value = NULL is valid syntax. Should it not be value IS NULL instead?

SQLChao Over a year ago

@phao5814 it should be IS NULL

Adi · Accepted Answer · 2018-05-23 06:49:00Z

0

I understand you got the answer but you can also use in your further query nvl function. You can replace at the runtime the NULL values with 0 just to be sure your query is working as expected.

SELECT 
        id
       ,nvl(col1, 0)
FROM
       ...

It's not updating in the table but you are sure that all NULL values are displayed as 0 like you want. Maybe you forget to update.

answered May 23, 2018 at 6:49

Adi

3091 gold badge5 silver badges19 bronze badges

1 Comment

Francesco Frassinelli Over a year ago

nvl is available on Oracle, not Postgres, which uses coalesce.

Collectives™ on Stack Overflow

Replace NULL in my Table with <SOME VALUE> in PostgreSQL

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related