PostgreSQL Text column to integer column

Question

I have to convert one text column to integer column. Showed code work when I have text (all numbers) in fields but now I find some empty strings '' where query stops.

ALTER TABLE mytable 
ALTER COLUMN mycolumn TYPE integer USING (TRIM(mycolumn)::integer);

Can here be done so that empty strings be converted to integer 0 so this query will pass?
How to do this?

concate your field with a '0'. something like (Trim('0'|mycolumns)::integer) — devanand
– devanand, Commented Dec 19, 2013 at 11:02
This works excellent, thank you very much! (Trim('0'||mycolumns)) — Wine Too
– Wine Too, Commented Dec 19, 2013 at 11:08

user_0 · Accepted Answer · 2014-01-23 20:52:59Z

1

You can update your values before altering table:

UPDATE mytable SET mycolumn = '0' WHERE mycolumn = '';

answered Jan 23, 2014 at 20:52

user_0

3,38323 silver badges38 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL Text column to integer column

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