Change Boolean column to integer

Question

I’m using a database that has a field called: status: boolean, but I want to store in it the values of the radio button (0,1,2) so I need to change to integer.

I’ve tried to:

ALTER TABLE OS ALTER COLUMN STATUS TYPE BOOLEAN USING (trim(STATUS)::integer);
ALTER TABLE OS ALTER COLUMN STATUS TYPEINTEGER;

But you made these mistakes:

ERROR:  function pg_catalog.btrim(boolean) does not exist
LINE 1: ... TABLE OS ALTER COLUMN STATUS TYPE INTEGER USING (trim(STATU...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

ERROR:  column "status" cannot be cast automatically to type integer
HINT:  You might need to specify "USING status::integer".

I have how to solve this without having to create the table again?

Answer 1

Assuming that your table OS be something like:

CREATE TABLE OS
(
    ID INTEGER,
    STATUS BOOLEAN
);

INSERT INTO OS ( ID, STATUS ) VALUES ( 100, TRUE );
INSERT INTO OS ( ID, STATUS ) VALUES ( 200, FALSE );
INSERT INTO OS ( ID, STATUS ) VALUES ( 300, TRUE );
INSERT INTO OS ( ID, STATUS ) VALUES ( 400, FALSE );

1) Create an auxiliary column in the table with the desired type:

ALTER TABLE OS ADD COLUMN STATUS_AUX SMALLINT;

2) Convert the pre-existing data in the original column to the auxiliary column appropriately:

UPDATE OS SET STATUS_AUX = 0 WHERE STATUS = FALSE;
UPDATE OS SET STATUS_AUX = 2 WHERE STATUS = TRUE;

3) The original column is excluded:

ALTER TABLE OS DROP COLUMN STATUS;

4) Rename the auxiliary column to the original column name:

ALTER TABLE OS RENAME COLUMN STATUS_AUX TO STATUS;

5) Includes a CONSTRAINT table check OS to ensure the integrity of the field data STATUS (0, 1 or 2):

ALTER TABLE OS ADD CONSTRAINT chk_os_status CHECK ( STATUS = 0 OR STATUS = 1 OR STATUS = 2  );

6) Optionally, the field STATUS can be NOT NULL:

ALTER TABLE OS ALTER COLUMN STATUS SET NOT NULL;

Entering data:

INSERT INTO OS ( ID, STATUS ) VALUES ( 500, 0 );    -- OK!
INSERT INTO OS ( ID, STATUS ) VALUES ( 600, 1 );    -- OK!
INSERT INTO OS ( ID, STATUS ) VALUES ( 700, 2 );    -- OK!

Testing CONSTRAINT:

INSERT INTO OS ( ID, STATUS ) VALUES ( 800, 3 );    -- ERRO! (CHECK CONSTRAINT)

Testing NOT NULL:

INSERT INTO OS ( ID, STATUS ) VALUES ( 900, NULL ); -- ERRO! (NOT NULL)

Functioning in the Sqlfiddle

Answer 2

You can create a Type and use in your table as follows.

CREATE TYPE StatusAceitacao AS ENUM ('Aceita', 'Finalizada', 'Fechada'); 

ALTER TABLE OS ALTER COLUMN STATUS DROP DEFAULT;
ALTER TABLE OS ALTER COLUMN STATUS TYPE StatusAceitacao USING CASE WHEN STATUS THEN 1 ELSE 0 END;

Or simply switch to smallint.

ALTER TABLE OS ALTER COLUMN STATUS DROP DEFAULT;
ALTER TABLE OS ALTER COLUMN STATUS TYPE smallint USING CASE WHEN STATUS THEN 1 ELSE 0 END;

Reference 1; Reference 2