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?

Solution

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

Comments

[p4t]

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]

@phao5814 it should be IS NULL

[Francesco Frassinelli]

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