CODEWITHSUNDEEP
sqlpostgresql
user3206440
user3206440

Reputation: 5049

SQL - conditionally set column values to NULL

I have a table - some_table which has a number of columns and some of them have some invalid value in some rows which need to transformed into NULL.

I cannot use the below due as mutating the original table is not allowed by permissions for one and also it needs to be repeated for all column names. 

UPDATE some_table TABLE@@ SET column_name = NULL WHERE column_name = 'invalid value';

So it needs to be a 'SELECT' operation to create a new table with invalid values converted to NULL - is there a quick way to do this ?

Updating with an answer from @Jonny below

NULLIF is a good option. However is there a way to apply it to all columns rather having to do it for each column separately - sometimes the number of columns is pretty huge.

Upvotes: 0

Views: 3538

Answers (2)

Jonny
Jonny

Reputation: 1161

You could use a NULLIF

Have a look at 9.16.3. NULLIF https://www.postgresql.org/docs/current/static/functions-conditional.html

SELECT NULLIF('invalid value', column_name)
FROM some_table

Upvotes: 1

Michael Bláha
Michael Bláha

Reputation: 383

How about something like:

INSERT INTO some_table2 (column_name, ...) SELECT * FROM some_table WHERE column_name <> 'invalid value';
INSERT INTO some_table2 (column_name, ...) SELECT null, ... FROM some_table WHERE column_name = 'invalid_value';

Upvotes: 0

Related Questions