Postgresql 9.4 Alter Column Text to Boolean with Values

Question

I have a table that looks like this:

CREATE TABLE "TestResults" (
"Id"                text PRIMARY KEY,
"Name"              text NOT NULL UNIQUE,
"Result"            text CHECK ("Comment" IN ('Pass', 'Fail')),
"CreatedBy"         text NOT NULL,
"CreatedOn"         timestamp with time zone NOT NULL,
);

We are using PostgreSQL 9.4

The user is currently able to select either Pass or Fail from a drop down menu, and we have been storing those strings in the database in the Result column. We would like to change that to a boolean value.

How can I change the Result column from text to boolean while keeping the values the users have already entered?

Thank you.

mu is too short · Accepted Answer · 2018-02-20 03:57:14Z

16

Any time you want to change a column's type but there is no default conversion from the old values to the new ones, you want to use a USING clause to specify how to convert the old values to the new ones:

The optional USING clause specifies how to compute the new column value from the old; if omitted, the default conversion is the same as an assignment cast from old data type to new. A USING clause must be provided if there is no implicit or assignment cast from old to new type.

So first get rid of the CHECK constraint, you can find the name by doing a \d "TestResults" from psql but it is probably "TestResults_Result_check":

alter table "TestResults" drop constraint "TestResults_Result_check";

Then an ALTER COLUMN (with a USING clause) to change the type:

alter table "TestResults"
alter column "Result"
set data type boolean
using case
    when "Result" = 'Pass' then true
    when "Result" = 'Fail' then false
    else null
end;

answered Feb 20, 2018 at 3:57

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mkhira2 Over a year ago

Thank you! That's exactly what I was looking for. I was having trouble finding information on how to use the case statement. The documentation is overall pretty wonderful for postgreSQL but I found it lacking in that area.

Collectives™ on Stack Overflow

Postgresql 9.4 Alter Column Text to Boolean with Values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related