How to cast from text to int if column contain bot

Question

As in title. I have one column which has some text values. For example column data1 has values ('31',32','',NULL). Now I want to update another column say data2 (type INT) with data from data1, so I am trying to do something like that:

UPDATE table SET data2=CAST(data1 AS INT).

The problem is because PostgreSQL cannot cast NULL or empty values to INT.

Answer 1

Actually, you can cast NULL to int, you just can't cast an empty string to int. Assuming you want NULL in the new column if data1 contains an empty string or NULL, you can do something like this:

UPDATE table SET data2 = cast(nullif(data1, '') AS int);

If you want some other logic, you can use for example (empty string converts to -1):

UPDATE table SET data2 = CASE WHEN data1 = '' THEN -1 ELSE cast(data1 AS int) END;