PostgreSQL Boolean Data Type

A boolean type is a data type that represents true or false. The PostgreSQL database supports the native boolean type, you can use BOOLEAN or BOOL to define a boolean column to store boolean values.

PostgreSQL BOOLEAN value

In PostgreSQL, true, 'true', 't', 'yes', 'y', and '1' are all treated as true, false, 'false', 'f', 'no', 'n', and '0' are all treated as false. Note that all values ​​here are case-insensitive. Except for true and false, all others are of character type.

The PostgreSQL boolean type allows NULL values.

Note that in the SQL standard, a boolean accepts only TRUE, FALSE, and NULL.

PostgreSQL BOOLEAN Examples

Follow these steps to practise this example:

1. First, use the following statement to create a table named test_boolean to demonstrate the usage of the BOOLEAN type.

   CREATE TABLE test_boolean (
     v VARCHAR NOT NULL,
     b BOOLEAN NOT NULL
   );
   

2. Second, insert some rows for testing into the test_boolean table. Among them, we use various literal values of boolean type.

   INSERT INTO test_boolean (v, b)
   VALUES
       (TRUE, TRUE),
       (FALSE, FALSE),
       ('t', 't'),
       ('f', 'f'),
       ('T', 'T'),
       ('F', 'F'),
       ('1', '1'),
       ('0', '0'),
       ('y', 'y'),
       ('n', 'n'),
       ('Y', 'Y'),
       ('N', 'N'),
       ('yes', 'yes'),
       ('no', 'no'),
       ('YES', 'YES'),
       ('NO', 'NO')
   RETURNING *;
   

      v   | b
   --------+---
    true  | t
    false | f
    t     | t
    f     | f
    T     | t
    F     | f
    1     | t
    0     | f
    y     | t
    n     | f
    Y     | t
    N     | f
    yes   | t
    no    | f
    YES   | t
    NO    | f
   (16 rows)

   Here, we can see that various literal values ​​have been converted to corresponding Boolean values.

3. Use the following statement to retrieve rows based on columns of BOOLEAN type:

   SELECT *
   FROM test_boolean
   WHERE b = 'Y';
   

     v   | b
   ------+---
    true | t
    t    | t
    T    | t
    1    | t
    y    | t
    Y    | t
    yes  | t
    YES  | t
   (8 rows)

   Here, we used a character value 'Y' for the BOOLEAN column in the WHERE condition. The statement returns all rows with b is true. You can also use 't', 'yes', 'y', or '1' replace 'Y' in the above statement.

Conclusion

In PostgreSQL, you can use BOOLEAN or BOOL columns to store boolean values. PostgreSQL treats true, 'true', 't', 'yes', 'y', and '1' as true and false, 'false', 'f', 'no', 'n', '0' as false.