2

I have a table with m columns and n rows. The ETL tools I was using wrote an empty ('') to every cell without a value and I would like them to be NULL instead. This got me thinking if it would be possible to UPDATE every cell with value '' with a NULL, in a single statement?
In my table every column happens to be VARCHAR (or CHARACTER VARYING).

    Col1|Col2|...|Coln          Col1|Col2|...|Coln
Row1   a   ''  ''    1      Row1   a NULL NULL   1
Row2   c   ''   e   ''      Row2   c NULL   e NULL
...                     -->  ...
Rown  aa    f  ''  123      Rown  aa    f NULL 123

Is it possible with SQL? How about PL/pgSQL? I did some googling around but couldn't find anything really relevant.

  • 1
    Really? Did you try something like: UPDATE table SET Col1 = NULL WHERE Col1=''; – ypercubeᵀᴹ Oct 9 '14 at 9:42
  • Seems to me like you didn't do any "googling" at all, or we don't understand your question as it speaks of a simple UPDATE statement. – Kamil Gosciminski Oct 9 '14 at 9:48
  • I'm guessing the asker is wondering if there's an easy way to write the SQL for all columns. – Colin 't Hart Oct 9 '14 at 9:53
  • "- - would [it] be possible to UPDATE every cell with value '' with a NULL". Sorry if I wasn't clear enough. Would it be possible to update every column in a single query. – JamesBrown Oct 9 '14 at 10:08
  • Do you mean iterating over table and column names as variables? so something like this? foreach table $t foreach column $c update $t set $c='' where $c is null you will need to use a procedure for this. (mine is only pseudo code how I interpret your question) – user49013 Oct 9 '14 at 19:45
6

You can use either n UPDATE statements, one for each column:

UPDATE table SET Col1 = NULL WHERE Col1 = '';
UPDATE table SET Col2 = NULL WHERE Col2 = '';
---
UPDATE table SET ColN = NULL WHERE ColN = '';

or one with CASE:

UPDATE table 
SET Col1 = CASE WHEN Col1 = '' THEN NULL ELSE Col1 END,
    Col2 = CASE WHEN Col2 = '' THEN NULL ELSE Col2 END,
    ---
    ColN = CASE WHEN ColN = '' THEN NULL ELSE ColN END 
WHERE                                           
    Col1 = '' OR Col2 = '' OR ... OR ColN = '' ;      --- optional WHERE clause  

The WHERE clause is not needed. But in case you run this again later, when there are very few rows with nulls, it will help to keep the transaction "smaller", updating only those rows and not the whole table.

Another equivalent, just shorter syntax, using the NULLIF() function:

UPDATE table 
SET Col1 = NULLIF(Col1, ''),
    Col2 = NULLIF(Col2, ''),
    ---
    ColN = NULLIF(ColN, '')
WHERE                                           
    Col1 = '' OR Col2 = '' OR ... OR ColN = '' ;      --- optional WHERE clause  
| improve this answer | |
1

Here is my idea based on the interpretation from this question.

This sentence is quite hard to understand:

UPDATE every cell with value '' with a NULL

Does it mean that you want to put '' in every column with null?

If so, You can use this:

ALTER TABLE `table` CHANGE COLUMN `col` `col` VARCHAR(xxx) NOT NULL DEFAULT '';
ALTER TABLE `table` CHANGE COLUMN `coln` `coln` TINYINT NOT NULL DEFAULT 0;

This will set all null values to ''. On numeric columns, it will set to 0.

Warning: According to a comment, this is INVALID syntax for Postgres.


If you means the oposite, @ypercube's answer is the correct one.

  • The syntax is not valid for Postgres. – ypercubeᵀᴹ Oct 9 '14 at 11:51
  • And what is confusing?: "The ETL tools I was using wrote an empty ('') to every cell without a value and I would like them to be NULL instead." – ypercubeᵀᴹ Oct 9 '14 at 11:52
  • Yeah, I got stuck on UPDATE every cell with value '' with a NULL and couldn't understand it fully. I will keep the answer here anyway but marked as community wiki, just in case someone is looking for it. – Ismael Miguel Oct 9 '14 at 11:55
  • Then update your answer with valid Postgres syntax. – ypercubeᵀᴹ Oct 9 '14 at 11:56
  • I can't, I have no way to test it. But I will check the syntax. – Ismael Miguel Oct 9 '14 at 11:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.