Change boolean column to integer

Question

Change boolean column to integer

Navigation

#1 by (3 votes)
#2 by (3 votes)

6

I'm using a database that has a field called: status: boolean , however I want to store the values of the Radio Button (0,1,2) so I need to change to integer .

I'vetried:

ALTERTABLEOSALTERCOLUMNSTATUSTYPEBOOLEANUSING(trim(STATUS)::integer);ALTERTABLEOSALTERCOLUMNSTATUSTYPEINTEGER;

Buthegavetheseerrors:

ERROR:functionpg_catalog.btrim(boolean)doesnotexistLINE1:...TABLEOSALTERCOLUMNSTATUSTYPEINTEGERUSING(trim(STATU...^HINT:Nofunctionmatchesthegivennameandargumenttypes.Youmightneedtoaddexplicittypecasts.ERROR:column"status" cannot be cast automatically to type integer
HINT:  You might need to specify "USING status::integer".

Can I solve this without having to re-create the table?

sql postgresql

asked by anonymous 24.10.2017 / 17:46

2 answers

3

___ erkimt ___ Change boolean column to integer ______ qstntxt ___

I'm using a database that has a field called: %code% , however I want to store the values of the Radio Button (0,1,2) so I need to change to %code% .

I'vetried:

CREATETYPEStatusAceitacaoASENUM('Aceita','Finalizada','Fechada');ALTERTABLEOSALTERCOLUMNSTATUSDROPDEFAULT;ALTERTABLEOSALTERCOLUMNSTATUSTYPEStatusAceitacaoUSINGCASEWHENSTATUSTHEN1ELSE0END;

Buthegavetheseerrors:

ALTERTABLEOSALTERCOLUMNSTATUSDROPDEFAULT;ALTERTABLEOSALTERCOLUMNSTATUSTYPEsmallintUSINGCASEWHENSTATUSTHEN1ELSE0END;

CanIsolvethiswithouthavingtore-createthetable?

______azszpr249400___

Assuming your %code% table is something like:

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;

1) An auxiliary column is created in the table with the desired type:

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;

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

%pre%

3) Exclude the original column:

%pre%

4) The auxiliary column is renamed to the original column name:

%pre%

5) A% check% is included in the %code% table to ensure data integrity of the %code% field (0, 1 or 2):

%pre%

6) Optionally, the %code% field can be %code% :

%pre%

Entering data:

%pre%

Testing %code% :

%pre%

Testing %code% :

%pre%

Running on SQLFiddle

______ ___ azszpr249050

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

%pre%

Or simply switch to smallint .

%pre%

Reference 1 ; Reference 2

___

24.10.2017 / 17:59

Python Combat Game What is a database procedure?

score 3 · Accepted Answer

Assuming your OS table is 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) An auxiliary column is created in the table with the desired type:

ALTER TABLE OS ADD COLUMN STATUS_AUX SMALLINT;

2) Converts 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) Exclude the original column:

ALTER TABLE OS DROP COLUMN STATUS;

4) The auxiliary column is renamed to the original column name:

ALTER TABLE OS RENAME COLUMN STATUS_AUX TO STATUS;

5) A% check% is included in the CONSTRAINT table to ensure data integrity of the OS field (0, 1 or 2):

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

6) Optionally, the STATUS field can be STATUS :

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 NOT NULL :

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

Testing CONSTRAINT :

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

Running on SQLFiddle