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, 3 months ago
  2. PostgreSQL fulltext with language translations by diverman 14 years, 4 months ago
  3. Drop all tables in MySQL database by mpasternacki 14 years, 10 months ago
  4. grep and delete sqlite tables by kifkif 15 years ago
  5. Substitute hyphens with spaces to enale URLs to reference to multi-word tags by miernik 15 years, 1 month ago

Comments

Please login first before commenting.