CODEWITHSUNDEEP
postgresql
Parity Bit
Parity Bit

Reputation: 113

Postgres - Cast Column to Type or Null If Unable To Cast

I have a postgres table I loaded from a mongodb collection in postgres. Although the postgres column is of type 'bigint', there are rows that are larger than the max big int, so when I try to update another table from this table, it errors out. There are also bigint columns with illegal characters, such as "_2131441" which I cleared via

WHERE col_name !~ '^([0-9]+[.]?[0-9]*|[.][0-9]+)$';

How can I force cast an entire column to be valid according to it's type, and set it to null/none if otherwise?

Upvotes: 0

Views: 1602

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246073

Use a CASE expression:

CASE WHEN col_name !~ '^(\+|-)?[[:digit:]]+$'
     THEN NULL::bigint
     WHEN col_name::numeric NOT BETWEEN -9223372036854775808 AND 9223372036854775807
     THEN NULL::bigint
     ELSE col_name::bigint
END

Note that bigint is an integer and does not allow a decimal separator.

Upvotes: 2

Related Questions