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.

Does postgresql have the `TO_CHAR` function? – wallyk May 20 '15 at 22:58 — wallyk, May 20 '15 at 22:58

score 16 · Accepted Answer · answered May 20 '15 at 23:13

16

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 |

answered May 20 '15 at 23:13

Crozin

43,890
13
88
135

1

A note about foreign keys: If the column you're trying to change from `int` to `character varying` is the target of a foreign key constraint, you'll have to drop the foreign key constraint before you'll be able to run the `ALTER TABLE` statement in this answer. You'll also need to alter the type of the column referencing the original column, and then you can recreate the foreign key constraint. – Jeremy Sep 15 '17 at 16:32

score 0 · Answer 2 · edited May 23 '17 at 11:45

I suggest a four step process:

Create a new string column. name it temp for now. See http://www.postgresql.org/docs/9.3/static/ddl-alter.html for details
Set the string column. something like update myTable set temp=cast(intColumn as text) see http://www.postgresql.org/docs/9.3/static/functions-formatting.html for more interesting number->string conversions

Make sure everything in temp looks the way you want it.

Remove your old integer column. Once again, see http://www.postgresql.org/docs/9.3/static/ddl-alter.html for details
Rename temp to the old column name. Again: http://www.postgresql.org/docs/9.3/static/ddl-alter.html

This assumes you can perform the operation while no clients are connected; offline. If you need to make this (drastic) change in an online table, take a look at setting up a new table with triggers for live updates, then swap to the new table in an atomic operation. see ALTER TABLE without locking the table?

Changing a column type from integer to string

2 Answers2