PostgreSQL: How to Cast varchar to Boolean

Often in PostgreSQL you may want to cast a varchar column to a boolean.

You can use the following syntax to do so:

SELECT *,
       all_star::boolean
FROM athletes;

This particular example will cast the all_star column from the athletes table from a varchar data type to a boolean.

Note that the ::boolean syntax will convert any of the following values:

• true, t, y, yes, on, 1 = true
• false, f, n, no, off, 0 = false

As long as your varchar column only contains these values then you will be able to cast it to a boolean column without any errors.

The following example shows how to use this syntax in practice.

Example: How to Cast Column from varchar to Boolean in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball players:

-- create table named 'athletes'
CREATE TABLE athletes (
  id INTEGER,
  all_star VARCHAR
);

-- add rows to the table 
INSERT INTO athletes VALUES (1, 'yes');
INSERT INTO athletes VALUES (2, 'true');
INSERT INTO athletes VALUES (3, 'false');
INSERT INTO athletes VALUES (4, 'true');
INSERT INTO athletes VALUES (5, 'false');
INSERT INTO athletes VALUES (6, 'false');
INSERT INTO athletes VALUES (7, 'false');

-- view all rows in table
SELECT * FROM athletes;

 id | all_star 
----+----------
  1 | yes
  2 | true
  3 | false
  4 | true
  5 | false
  6 | false
  7 | false

The all_star column is currently a varchar column that describes whether or not the particular player is an all-star or not.

We can use the following syntax to convert this column to a boolean column instead:

SELECT *,
       all_star::boolean
FROM athletes;

 id | all_star | all_star 
----+----------+----------
  1 | yes      | t
  2 | true     | t
  3 | false    | f
  4 | true     | t
  5 | false    | f
  6 | false    | f
  7 | false    | f

Notice that the new all_star column casts all of the values from the original all_star column to the boolean values t or f.

You can use similar syntax to cast a varchar column to a boolean column in your own table in PostgreSQL.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Select Rows in Date Range
PostgreSQL: How to Extract Month Name from Date
PostgreSQL: How to Generate Date Series Between Two Dates