How to cast text to int if column contains int and NULL values in PostgreSQL
As in the title. I have one column that has some text values. For example, column1 data has values ('31', 32 ',' ', NULL). Now I want to update another column: data2 (type INT) with data from data1, so I am trying to do something like this:
UPDATE table SET data2=CAST(data1 AS INT).
The problem is that PostgreSQL cannot pass NULL or null values to INTs.
+1
source to share
1 answer
Actually you can use NULL
for int, you just cannot use empty string for int. Assuming you want NULL in the new column, if it data1
contains an empty string or NULL, you can do something like this:
UPDATE table SET data2 = cast(nullif(data1, '') AS int);
If you need different 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;
+5
source to share