CODEWITHSUNDEEP
sqlpostgresqlcastingnull
sennin
sennin

Reputation: 8972

How to cast from text to int if column contain both int and NULL values in PostgreSQL

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.

Upvotes: 1

Views: 9583

Answers (1)

Lukáš Lalinský
Lukáš Lalinský

Reputation: 41306

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;

Upvotes: 5

Related Questions