Changing a column type from integer to string

Question

Using PostgreSQL, what's the command to migrate an integer column type to a string column type?

Obviously I'd like to preserve the data, by converting the old integer data to strings.

Answer 1

You can convert from INTEGER to CHARACTER VARYING out-of-the-box, all you need is ALTER TABLE query chaning column type:

SQL Fiddle

PostgreSQL 9.3 Schema Setup:

CREATE TABLE tbl (col INT);
INSERT INTO tbl VALUES (1), (10), (100);
ALTER TABLE tbl ALTER COLUMN col TYPE CHARACTER VARYING(10);

Query 1:

SELECT col, pg_typeof(col) FROM tbl

Results:

| col |         pg_typeof |
|-----|-------------------|
|   1 | character varying |
|  10 | character varying |
| 100 | character varying |