Login

Changing field type in production

Author:
lawgon
Posted:
April 17, 2007
Language:
SQL
Version:
Not specified
Score:
2 (after 2 ratings)

Assume a model called 'Child' with a field called 'schoolstd' whic h is of integer type and not null. You need to change it to char type and not null and the same time preserve the data. The above snippet does this in postgresql.

1
2
3
4
5
6
7
begin;
alter table web_child add column strnum varchar(10);
update web_child set strnum = to_char(schoolstd,'999');
alter table web_child alter column strnum set not null;
alter table web_child drop column schoolstd;
alter table web_child rename column strnum to schoolstd;
commit;

More like this

  1. create_template_postgis-ubuntu_lucid by clawlor 14 years, 2 months ago
  2. PostgreSQL fulltext with language translations by diverman 14 years, 3 months ago
  3. Drop all tables in MySQL database by mpasternacki 14 years, 9 months ago
  4. grep and delete sqlite tables by kifkif 14 years, 11 months ago
  5. Substitute hyphens with spaces to enale URLs to reference to multi-word tags by miernik 15 years ago

Comments

Please login first before commenting.