%3CLINGO-SUB%20id%3D%22lingo-sub-1561303%22%20slang%3D%22en-US%22%3EAlter%20table%20column%20in%20PostgreSQL%3A%20An%20alternative%20approach%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1561303%22%20slang%3D%22en-US%22%3E%3CP%20style%3D%22margin%3A%200in%3B%22%3EDue%20to%20performance%20and%20locking%20reasons%2C%20changing%20a%20datatype%20column%20using%20ALTER%20COLUMN%20can%20be%20a%20long-running%20operation.%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%22%3ESuppose%20we%20have%20a%20table%20PRU%20with%20two%20columns.%20One%20is%20a%20column%20called%20%3CSTRONG%3Eid%3C%2FSTRONG%3E%20with%20type%20bigserial.%20In%20the%20second%20column%20called%20%3CSTRONG%3EA%3C%2FSTRONG%3E%20we%20have%20integer%20data%20currently%20saved%20as%20Text%20type.%20Let's%20say%20we%20want%20to%20change%20the%20type%20of%20column%20A%20to%20Integer.%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you'd%20like%20to%20follow%20along%20with%20an%20example%20of%20this%20scenario%2C%20let's%20first%20create%20a%20table%20and%20generate%20data%20for%20it.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ECREATE%20TABLE%20PRU%20(id%20bigserial%2C%20A%20TEXT)%3B%0AINSERT%20INTO%20PRU%20(A)%20VALUES%20('111')%3B%0AINSERT%20INTO%20PRU%20(A)%20VALUES%20('111')%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%22%3EGenerate%20rows%20until%202M%2C%20by%20looping%20the%20following%20statement%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3EINSERT%20INTO%20PRU%20SELECT%20*%20FROM%20PRU%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%22%3EWe%20want%20to%20change%20column%20A's%20datatype%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3EALTER%20%2F*optionA*%2F%20TABLE%20PRU%20ALTER%20COLUMN%20A%20TYPE%20INTEGER%20USING%20A%3A%3AINTEGER%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%22%3EWe%20could%20review%20stats%20from%20the%20command%20above%20with%20following%20query%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ESELECT%20*%20FROM%20pg_stat_statements%20WHERE%20query%20like%20'%25optionA%25'%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%22%3EThis%20method%20is%20the%20easiest%20one%2C%20but%20could%20generate%20high%20contention%20due%20to%20required%20exclusive%20lock%20for%20the%20table.%20This%20exclusive%20lock%20could%20generate%20errors%20in%26nbsp%3B%20the%20application.%20You%20might%20have%20to%20stop%20your%20application%20to%20perform%20this%20type%20of%20long%20running%20operation.%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20style%3D%22margin%3A%200in%3B%22%20id%3D%22toc-hId--1272361298%22%20id%3D%22toc-hId--1272361298%22%3EAlternative%3A%20Add%20a%20column%20and%20rename%3C%2FH2%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%22%3EAnother%20approach%20to%20change%20the%20datatype%20of%20the%20column%20could%20be%20to%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3EAdd%20an%20extra%20column%3C%2FLI%3E%0A%3CLI%3EMigrate%20the%20data%20to%20the%20new%20column%3C%2FLI%3E%0A%3CLI%3EDrop%20old%20column%20and%20rename%20new%20one%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%22%3EThe%20advantages%20of%20this%20method%20is%20you%20have%20more%20control%20over%20the%20process.%20It%20can%20be%20executed%20over%20multiple%20hours%20or%20days%20as%20needed.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3E%2F%2F%0A%2F%2F%20Add%202%20auxiliary%20columns%0A%2F%2F%0AALTER%20TABLE%20PRU%20ADD%20COLUMN%20A1%20INTEGER%2C%20ADD%20COLUMN%20A1_CHANGED%20BOOLEAN%3B%0A%0A%2F%2F%0A%2F%2F%20Trigger%20to%20take%20care%20of%20ongoing%20changes%20from%20the%20applications%0A%2F%2F%0ACREATE%20OR%20REPLACE%20FUNCTION%20set_a1()%0A%26nbsp%3B%20RETURNS%20TRIGGER%20AS%0A%24func%24%0ABEGIN%0A%20%20%20%20IF%20(TG_OP%3D'INSERT')%20THEN%0A%20%20%20%20%20%20%20%20NEW.a1%3A%3DNEW.a%3A%3Ainteger%3B%0A%20%20%20%20ELSEIF%20(TG_OP%3D'UPDATE')%20THEN%0A%20%20%20%20%20%20%20%20IF%20(NEW.a%20%26lt%3B%26gt%3B%20OLD.a)%20THEN%0A%20%20%20%20%20%20%20%20%20%20%20%20NEW.a1%3A%3DNEW.a%3A%3Ainteger%3B%0A%20%20%20%20%20%20%20%20ELSEIF%20(NEW.a%20is%20null%20and%20OLD.a%20is%20not%20null)%20THEN%0A%20%20%20%20%20%20%20%20%20%20%20%20NEW.a1%3A%3Dnull%3B%0A%20%20%20%20%20%20%20%20ELSEIF%20(NEW.a%20is%20not%20null%20and%20OLD.a%20is%20null)%20THEN%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20NEW.a1%3A%3DNEW.a%3A%3Ainteger%3B%0A%20%20%20%20%20%20%20%20END%20IF%3B%0A%20%20%20%20END%20IF%3B%0A%20%20%20%20NEW.a1_changed%3A%3Dtrue%3B%0A%20%20%20%20RETURN%20NEW%3B%0AEND%0A%24func%24%20LANGUAGE%20plpgsql%3B%0A%0ADROP%20TRIGGER%20IF%20EXISTS%20set_a1%20ON%20PRU%3B%0A%0ACREATE%20TRIGGER%20set_a1%0ABEFORE%20INSERT%20OR%20UPDATE%20ON%20pru%0AFOR%20EACH%20ROW%20EXECUTE%20PROCEDURE%20set_a1()%3B%0A%0A%2F%2F%0A%2F%2F%20Update%20sentence%20with%20limit%20number%20or%20fows%20to%20update%20in%20single%20transaction%0A%2F%2F%0A%2F%2F%20This%20sentence%20must%20be%20repeated%20multiple%20times%20until%20all%20rows%20were%20updated%0A%2F%2F%0A%0AUPDATE%20%2F*optionB*%2F%20PRU%20SET%20A1%3DA%3A%3AINTEGER%2C%20A1_CHANGED%3Dtrue%20WHERE%20id%20IN%20(SELECT%20id%20FROM%20PRU%20WHERE%20A1_CHANGED%20is%20null%20limit%20100000)%3B%0A%0A%2F%2F%0A%2F%2F%20Check%20the%20process%0A%2F%2F%0A%2F%2F%20Current%20changed%20rows%3A%0A%0ASELECT%20COUNT(1)%20FROM%20PRU%20WHERE%20A1_CHANGED%3Dtrue%3B%0A%0A%2F%2F%20Current%20pending%20rows%3A%0ASELECT%20COUNT(1)%20FROM%20PRU%20WHERE%20A1_CHANGED%20is%20null%3B%0A%0A%2F%2F%0A%2F%2F%20Final%20work%0A%2F%2F%0A%2F%2F%20After%20no%20rows%20need%20changes%2C%20we%20can%20switch%20the%20columns%0ABEGIN%20WORK%3B%0ALOCK%20TABLE%20PRU%20IN%20SHARE%20MODE%3B%0AALTER%20%2F*optionB*%2F%20TABLE%20PRU%20DROP%20COLUMN%20A%3B%0AALTER%20%2F*optionB*%2F%20TABLE%20PRU%20DROP%20COLUMN%20A1_CHANGED%3B%0AALTER%20%2F*optionB*%2F%20TABLE%20PRU%20RENAME%20A1%20TO%20A%3B%0ADROP%20TRIGGER%20set_a1%20ON%20PRU%3B%0ADROP%20FUNCTION%20set_a1()%3B%0ACOMMIT%20WORK%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%22%3EWe%20could%20review%20stats%20from%20this%20command%20with%20following%20command%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ESELECT%20*%20FROM%20pg_stat_statements%20WHERE%20query%20like%20'%25optionB%25'%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%22%3EThe%20tradeoff%20here%20is%20that%20we%20can%20observe%20more%20resources%20used%2C%20and%20more%20space%20allocated%20to%20the%20table%2C.%20However%2C%20the%20application%20has%20been%20working%20most%20of%20the%20time%20without%20high%20contention%20locking.%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20either%20case%2C%20you%20should%20be%20careful%20to%20first%20understand%20how%20stable%20the%20data%20in%20the%20column%20is%20so%20you%20don't%20miss%20new%20changes.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1561303%22%20slang%3D%22en-US%22%3E%3CP%3EHere%20you%20have%20an%20alternative%20way%20to%20alter%20a%20table%20column%20type%20based%20on%20an%20online%20migration%20using%20extra%20columns%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1561303%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Ealter%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Edatatype%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Elock%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPerformance%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Epostgres%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Epostgresql%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E

Due to performance and locking reasons, changing a datatype column using ALTER COLUMN can be a long-running operation.

 

Suppose we have a table PRU with two columns. One is a column called id with type bigserial. In the second column called A we have integer data currently saved as Text type. Let's say we want to change the type of column A to Integer.

 

If you'd like to follow along with an example of this scenario, let's first create a table and generate data for it.

 

 

CREATE TABLE PRU (id bigserial, A TEXT);
INSERT INTO PRU (A) VALUES ('111');
INSERT INTO PRU (A) VALUES ('111');

 

 

 

 

Generate rows until 2M, by looping the following statement:

 

 

INSERT INTO PRU SELECT * FROM PRU;

 

 

 

 

We want to change column A's datatype:

 

 

ALTER /*optionA*/ TABLE PRU ALTER COLUMN A TYPE INTEGER USING A::INTEGER;

 

 

 

 

We could review stats from the command above with following query:

 

 

SELECT * FROM pg_stat_statements WHERE query like '%optionA%';

 

 

 

This method is the easiest one, but could generate high contention due to required exclusive lock for the table. This exclusive lock could generate errors in  the application. You might have to stop your application to perform this type of long running operation.

 

 

Alternative: Add a column and rename

Another approach to change the datatype of the column could be to

  1. Add an extra column
  2. Migrate the data to the new column
  3. Drop old column and rename new one

The advantages of this method is you have more control over the process. It can be executed over multiple hours or days as needed.

 

 

//
// Add 2 auxiliary columns
//
ALTER TABLE PRU ADD COLUMN A1 INTEGER, ADD COLUMN A1_CHANGED BOOLEAN;

//
// Trigger to take care of ongoing changes from the applications
//
CREATE OR REPLACE FUNCTION set_a1()
  RETURNS TRIGGER AS
$func$
BEGIN
    IF (TG_OP='INSERT') THEN
        NEW.a1:=NEW.a::integer;
    ELSEIF (TG_OP='UPDATE') THEN
        IF (NEW.a <> OLD.a) THEN
            NEW.a1:=NEW.a::integer;
        ELSEIF (NEW.a is null and OLD.a is not null) THEN
            NEW.a1:=null;
        ELSEIF (NEW.a is not null and OLD.a is null) THEN        
            NEW.a1:=NEW.a::integer;
        END IF;
    END IF;
    NEW.a1_changed:=true;
    RETURN NEW;
END
$func$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS set_a1 ON PRU;

CREATE TRIGGER set_a1
BEFORE INSERT OR UPDATE ON pru
FOR EACH ROW EXECUTE PROCEDURE set_a1();

//
// Update sentence with limit number or fows to update in single transaction
//
// This sentence must be repeated multiple times until all rows were updated
//

UPDATE /*optionB*/ PRU SET A1=A::INTEGER, A1_CHANGED=true WHERE id IN (SELECT id FROM PRU WHERE A1_CHANGED is null limit 100000);

//
// Check the process
//
// Current changed rows:

SELECT COUNT(1) FROM PRU WHERE A1_CHANGED=true;

// Current pending rows:
SELECT COUNT(1) FROM PRU WHERE A1_CHANGED is null;

//
// Final work
//
// After no rows need changes, we can switch the columns
BEGIN WORK;
LOCK TABLE PRU IN SHARE MODE;
ALTER /*optionB*/ TABLE PRU DROP COLUMN A;
ALTER /*optionB*/ TABLE PRU DROP COLUMN A1_CHANGED;
ALTER /*optionB*/ TABLE PRU RENAME A1 TO A;
DROP TRIGGER set_a1 ON PRU;
DROP FUNCTION set_a1();
COMMIT WORK;

 

 

 

 

We could review stats from this command with following command:

 

 

SELECT * FROM pg_stat_statements WHERE query like '%optionB%';

 

 

 

 

The tradeoff here is that we can observe more resources used, and more space allocated to the table,. However, the application has been working most of the time without high contention locking.

In either case, you should be careful to first understand how stable the data in the column is so you don't miss new changes.