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

Question

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?

Laurenz Albe · Accepted Answer · 2020-04-10 05:11:17Z

2

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.

answered Apr 10, 2020 at 5:11

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related