CODEWITHSUNDEEP
postgresql
rshar
rshar

Reputation: 1475

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

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

Upvotes: 0

Views: 970

Answers (1)

user330315
user330315

Reputation:

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;

Upvotes: 2

Related Questions