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.

Answer 1

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;