default for column "xxxx" cannot be cast automatically to type boolean in Postgres DB

Question

I am trying to update a column in brs.parts table from character varying to boolean. The column has data as Y/N till now. I am using the below command

ALTER TABLE brs.parts ALTER COLUMN is_dpm_scanned TYPE BOOLEAN USING is_dpm_scanned::BOOLEAN

But I am getting the following error:

********* Error **********

ERROR: default for column "is_dpm_scanned" cannot be cast automatically to type boolean
SQL state: 42804

The table definition was:

CREATE TABLE brs.parts (
    id serial NOT NULL PRIMARY KEY,
    webcrt_part_id INTEGER,
    event_id INTEGER,
    webcrt_job_id INTEGER,
    incoming_serial_number CHARACTER VARYING(256),
    outgoing_serial_number CHARACTER VARYING(256), 
    part_number CHARACTER VARYING(256),
    tag_number NUMERIC,
    is_dpm_scanned CHARACTER VARYING(1) DEFAULT 'N'::CHARACTER VARYING,
    current_operation_id INTEGER,
    creation_date DATE,
    created_by CHARACTER VARYING(20) NOT NULL,
    last_updated_date DATE,
    last_updated_by CHARACTER VARYING(20) NOT NULL,
    is_delete CHARACTER VARYING(1) DEFAULT 'N'::CHARACTER VARYING
);

klin · Accepted Answer · 2022-08-12 21:37:44Z

79

You have to drop the default constraint before changing the type:

ALTER TABLE parts 
    ALTER COLUMN is_dpm_scanned DROP DEFAULT,
    ALTER COLUMN is_dpm_scanned TYPE BOOLEAN USING is_dpm_scanned::BOOLEAN,
    ALTER COLUMN is_dpm_scanned SET DEFAULT FALSE;

Comments

asherbret · Accepted Answer · 2020-09-15 04:13:29Z

4

If casting an integer column to boolean you need to specify explicitly what values are considered true and what are false. Usually, 0 is mapped to false and everything else to true. To do this execute:

ALTER TABLE mytable ALTER mycolumn TYPE bool USING 
  CASE WHEN mycolumn=0 THEN FALSE 
  ELSE TRUE 
END;

answered Sep 15, 2020 at 4:13

asherbret

6,0794 gold badges44 silver badges67 bronze badges

1 Comment

klin Over a year ago

Obviously, you don't need to use case. Casting integers to boolean works in Postgres as expected.

Collectives™ on Stack Overflow

default for column "xxxx" cannot be cast automatically to type boolean in Postgres DB

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related