Change Boolean column to integer

Asked

Viewed 1,345 times

6

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.

inserir a descrição da imagem aqui

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?

  • No, switch to smallint .

  • Only creating table a again?

  • or you can create an https://www.postgresql.org/docs/9.1/static/datatype-enum.html I think in your case an Enum ... you can make an alter table

2 answers

3


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

3

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

  • 2

    Remembering that if the table OS is already populated, only change column type STATUS of BOOLEAN for SMALLINT can be fatal for the data already contained in the table. Surely the data will be converted by means of a CAST implicit, making all that is TRUE become Finalizada and everything FALSE become Aceita.

  • @Lacobus, well remembered... I corrected that.

  • I am a great supporter of the idea that structure definition instructions (DDL) should never alter data in a implicit. It is good practice that this is already done only by data manipulation instructions (DML) so explicit. In many controlled corporate environments, data management and data structure is done separately, and in such environments, scripts so they wouldn’t be accepted by DBAs.

  • policies and terms of use are not mentioned in the question.

  • Even so, good practices will always be "good".

Browser other questions tagged

You are not signed in. Login or sign up in order to post.