PostgreSQL v7.4 ALTER TABLE to change column

Question

I have a need to change the length of CHAR columns in tables in a PostgreSQL v7.4 database. This version did not support the ability to directly change the column type or size using the ALTER TABLE statement. So, directly altering a column from a CHAR(10) to CHAR(20) for instance isn't possible (yeah, I know, "use varchars", but that's not an option in my current circumstance). Anyone have any advice/tricks on how to best accomplish this? My initial thoughts:

-- Save the table's data in a new "save" table. CREATE TABLE save_data AS SELECT * FROM table_to_change;

-- Drop the columns from the first column to be changed on down. ALTER TABLE table_to_change DROP column_name1; -- for each column starting with the first one that needs to be modified ALTER TABLE table_to_change DROP column_name2; ...

-- Add the columns back, using the new size for the CHAR column ALTER TABLE table_to_change ADD column_name1 CHAR(new_size); -- for each column dropped above ALTER TABLE table_to_change ADD column_name2...

-- Copy the data bace from the "save" table UPDATE table_to_change SET column_name1=save_data.column_name1, -- for each column dropped/readded above column_name2=save_date.column_name2, ... FROM save_data WHERE table_to_change.primary_key=save_data.primay_key;

Yuck! Hopefully there's a better way? Any suggestions appreciated. Thanks!

Answer 1

I would dump the table contents to a flat file with COPY, drop the table, recreate it with the correct column setup, and then reload (with COPY again).

http://www.postgresql.org/docs/7.4/static/sql-copy.html

Is it acceptable to have downtime while performing this operation? Obviously what I've just described requires making the table unusable for a period of time, how long depends on the data size and hardware you're working with.

Edit: But COPY is quite a bit faster than INSERTs and UPDATEs. According to the docs you can make it even faster by using BINARY mode. BINARY makes it less compatible with other PGSQL installs but you won't care about that because you only want to load the data to the same instance that you dumped it from.

Answer 2

Not PostgreSQL, but in Oracle I have changed a column's type by:

1. Add a new column with a temporary name (ie: TMP_COL) and the new data type (ie: CHAR(20))
2. run an update query: UPDATE TBL SET TMP_COL = OLD_COL;
3. Drop OLD_COL
4. Rename TMP_COL to OLD_COL

Answer 3

The best approach to your problem is to upgrade pg to something less archaic :)

Seriously. 7.4 is going to be removed from "supported versions" pretty soon, so I wouldn't wait for it to happen with 7.4 in production.