Change datatype of column to integer (column with n/a) in PostgreSQL

Question

I have a column (phase_mapped) in PostgreSQL 11.0 with integers and n/a values. Current datatype of the column in nchar(3). I would like to convert the datatype of this column to integer but n/a is throwing an error. How can I set int datatype of such columns.

phase   phase_mapped
Phase 1   1  
Phase 2   2  
n/a       n/a

I tried following query

ALTER TABLE table
  ALTER COLUMN phase_mapped TYPE INT USING phase_mapped::integer;

Any help is highly appreciated

user330315 · Accepted Answer · 2020-07-16 09:06:56Z

2

You can provide an expression in the using part that deals with the n/a. You probably need to deal with empty strings as well.

ALTER TABLE table
  ALTER COLUMN phase_mapped TYPE INT 
  USING nullif(trim(nullif(phase_mapped,'n/a')),'')::integer;

Or simply remove all non-digit characters:

ALTER TABLE table
  ALTER COLUMN phase_mapped TYPE INT 
  USING nullif(regexp_replace(phase_mapped,'[^0-9]', '', 'g'),'')::integer;

answered Jul 16, 2020 at 9:06

user330315

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

Collectives™ on Stack Overflow

Change datatype of column to integer (column with n/a) in PostgreSQL

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