While upgrading our Jive Messenger 2.3 to Wildfire 2.4, I ran into a problem with the PostgreSQL upgrade script. The following line:
ALTER TABLE jiveGroupUser ALTER COLUMN username TYPE VARCHAR(100) NOT NULL;
/code
works great on PostgreSQL 8.x, but not so well on 7.x as the ability to alter a column type wasn’'t there yet. I worked around this problem by running the following instead:
ALTER TABLE jiveGroupUser RENAME TO jiveGroupUser23;
ALTER TABLE jiveGroupUser23 DROP CONSTRAINT jiveGroupUser_pk;
CREATE TABLE jiveGroupUser (
groupName VARCHAR(50) NOT NULL,
username VARCHAR(100) NOT NULL,
administrator INTEGER NOT NULL,
CONSTRAINT jiveGroupUser_pk PRIMARY KEY (groupName, username, administrator)
);
INSERT INTO jiveGroupUser (groupName, username, administrator)
SELECT groupName, username, administrator FROM jiveGroupUser23;
DROP TABLE jiveGroupUser23;
/code
Of course, be sure to (as always) back up your database prior to running any upgrade script in the event that a restore is necessary.
Cheers,
-craig