Cara menggunakan postgresql bit vs boolean

Question

Summary: in this tutorial, you will learn about the PostgreSQL Boolean data type and how to use it in designing the database tables.

Table of Contents Show

Introduction to the PostgreSQL Boolean type
PostgreSQL Boolean examples
Set a default value of the Boolean column

Introduction to the PostgreSQL Boolean type

PostgreSQL supports a single Boolean data type:

INSERT INTO stock_availability (product_id, available)
VALUES
	(100, TRUE),
	(200, FALSE),
	(300, 't'),
	(400, '1'),
	(500, 'y'),
	(600, 'yes'),
	(700, 'no'),
	(800, '0');
Code language: SQL (Structured Query Language) (sql)

3 that can have three values:

INSERT INTO stock_availability (product_id, available)
VALUES
	(100, TRUE),
	(200, FALSE),
	(300, 't'),
	(400, '1'),
	(500, 'y'),
	(600, 'yes'),
	(700, 'no'),
	(800, '0');
Code language: SQL (Structured Query Language) (sql)

4,

INSERT INTO stock_availability (product_id, available)
VALUES
	(100, TRUE),
	(200, FALSE),
	(300, 't'),
	(400, '1'),
	(500, 'y'),
	(600, 'yes'),
	(700, 'no'),
	(800, '0');
Code language: SQL (Structured Query Language) (sql)

5 and

INSERT INTO stock_availability (product_id, available)
VALUES
	(100, TRUE),
	(200, FALSE),
	(300, 't'),
	(400, '1'),
	(500, 'y'),
	(600, 'yes'),
	(700, 'no'),
	(800, '0');
Code language: SQL (Structured Query Language) (sql)

6.

PostgreSQL uses one byte for storing a boolean value in the database. The

INSERT INTO stock_availability (product_id, available)
VALUES
	(100, TRUE),
	(200, FALSE),
	(300, 't'),
	(400, '1'),
	(500, 'y'),
	(600, 'yes'),
	(700, 'no'),
	(800, '0');
Code language: SQL (Structured Query Language) (sql)

3 can be abbreviated as

INSERT INTO stock_availability (product_id, available)
VALUES
	(100, TRUE),
	(200, FALSE),
	(300, 't'),
	(400, '1'),
	(500, 'y'),
	(600, 'yes'),
	(700, 'no'),
	(800, '0');
Code language: SQL (Structured Query Language) (sql)

8.

In standard SQL, a Boolean value can be

INSERT INTO stock_availability (product_id, available)
VALUES
	(100, TRUE),
	(200, FALSE),
	(300, 't'),
	(400, '1'),
	(500, 'y'),
	(600, 'yes'),
	(700, 'no'),
	(800, '0');
Code language: SQL (Structured Query Language) (sql)

9,

SELECT *
FROM stock_availability
WHERE available = 'yes';



Code language: SQL (Structured Query Language) (sql)

0, or

INSERT INTO stock_availability (product_id, available)
VALUES
	(100, TRUE),
	(200, FALSE),
	(300, 't'),
	(400, '1'),
	(500, 'y'),
	(600, 'yes'),
	(700, 'no'),
	(800, '0');
Code language: SQL (Structured Query Language) (sql)

6. However, PostgreSQL is quite flexible when dealing with

INSERT INTO stock_availability (product_id, available)
VALUES
	(100, TRUE),
	(200, FALSE),
	(300, 't'),
	(400, '1'),
	(500, 'y'),
	(600, 'yes'),
	(700, 'no'),
	(800, '0');
Code language: SQL (Structured Query Language) (sql)

9 and

SELECT *
FROM stock_availability
WHERE available = 'yes';
Code language: SQL (Structured Query Language) (sql)

0 values.

The following table shows the valid literal values for

INSERT INTO stock_availability (product_id, available)
VALUES
	(100, TRUE),
	(200, FALSE),
	(300, 't'),
	(400, '1'),
	(500, 'y'),
	(600, 'yes'),
	(700, 'no'),
	(800, '0');
Code language: SQL (Structured Query Language) (sql)

9 and

SELECT *
FROM stock_availability
WHERE available = 'yes';
Code language: SQL (Structured Query Language) (sql)

0 in PostgreSQL.

TrueFalsetruefalse‘t’‘f ‘‘true’‘false’‘y’‘n’‘yes’‘no’‘1’‘0’

Note that the leading or trailing whitespace does not matter and all the constant values except for

INSERT INTO stock_availability (product_id, available)
VALUES
	(100, TRUE),
	(200, FALSE),
	(300, 't'),
	(400, '1'),
	(500, 'y'),
	(600, 'yes'),
	(700, 'no'),
	(800, '0');
Code language: SQL (Structured Query Language) (sql)

4 and

INSERT INTO stock_availability (product_id, available)
VALUES
	(100, TRUE),
	(200, FALSE),
	(300, 't'),
	(400, '1'),
	(500, 'y'),
	(600, 'yes'),
	(700, 'no'),
	(800, '0');
Code language: SQL (Structured Query Language) (sql)

5 must be enclosed in single quotes.

PostgreSQL Boolean examples

Let’s take a look at some examples of using the PostgreSQL Boolean data type.

First, create a new table

SELECT *
FROM stock_availability
WHERE available = 'yes';



Code language: SQL (Structured Query Language) (sql)

8 to log which products are available.

CREATE TABLE stock_availability (
   product_id INT PRIMARY KEY,
   available BOOLEAN NOT NULL
);
Code language: SQL (Structured Query Language) (sql)

Second, insert some sample data into the

SELECT *
FROM stock_availability
WHERE available = 'yes';
Code language: SQL (Structured Query Language) (sql)

8 table. We use various literal value for the boolean values.

INSERT INTO stock_availability (product_id, available)
VALUES
	(100, TRUE),
	(200, FALSE),
	(300, 't'),
	(400, '1'),
	(500, 'y'),
	(600, 'yes'),
	(700, 'no'),
	(800, '0');
Code language: SQL (Structured Query Language) (sql)

Third, use the following statement to check for the availability of products:

SELECT *
FROM stock_availability
WHERE available = 'yes';
Code language: SQL (Structured Query Language) (sql)

 product_id | available
------------+-----------
        100 | t
        300 | t
        400 | t
        500 | t
        600 | t
(5 rows)
Code language: SQL (Structured Query Language) (sql)

You can imply the true value by using the Boolean column without any operator. For example, the following query returns all products that are available:

SELECT *
FROM stock_availability
WHERE available;
Code language: SQL (Structured Query Language) (sql)

Similarly, if you want to look for

INSERT INTO stock_availability (product_id, available)
VALUES
	(100, TRUE),
	(200, FALSE),
	(300, 't'),
	(400, '1'),
	(500, 'y'),
	(600, 'yes'),
	(700, 'no'),
	(800, '0');
Code language: SQL (Structured Query Language) (sql)

5 values, you compare the value of the Boolean column against any valid Boolean constants.

The following query returns the products that are not available.

SELECT *
FROM stock_availability
WHERE available = 'no';
Code language: SQL (Structured Query Language) (sql)

 product_id | available
------------+-----------
        200 | f
        700 | f
        800 | f
(3 rows)
Code language: SQL (Structured Query Language) (sql)

Or you can use the

 product_id | available
------------+-----------
        100 | t
        300 | t
        400 | t
        500 | t
        600 | t
(5 rows)
Code language: SQL (Structured Query Language) (sql)

1 operator to check if values in the Boolean column are false like this:

SELECT *
FROM stock_availability
WHERE NOT available;
Code language: SQL (Structured Query Language) (sql)

Set a default value of the Boolean column

To set a default value for an existing Boolean column, you use the

 product_id | available
------------+-----------
        100 | t
        300 | t
        400 | t
        500 | t
        600 | t
(5 rows)
Code language: SQL (Structured Query Language) (sql)

2 clause in the ALTER TABLE statement.

For example, the following

 product_id | available
------------+-----------
        100 | t
        300 | t
        400 | t
        500 | t
        600 | t
(5 rows)
Code language: SQL (Structured Query Language) (sql)

3 statement sets the default value for the

 product_id | available
------------+-----------
        100 | t
        300 | t
        400 | t
        500 | t
        600 | t
(5 rows)
Code language: SQL (Structured Query Language) (sql)

4 column in the

SELECT *
FROM stock_availability
WHERE available = 'yes';
Code language: SQL (Structured Query Language) (sql)

8 table:

ALTER TABLE stock_availability 
ALTER COLUMN available
SET DEFAULT FALSE;
Code language: SQL (Structured Query Language) (sql)

If you insert a row without specifying the value for the

 product_id | available
------------+-----------
        100 | t
        300 | t
        400 | t
        500 | t
        600 | t
(5 rows)
Code language: SQL (Structured Query Language) (sql)

4 column, PostgreSQL uses

SELECT *
FROM stock_availability
WHERE available = 'yes';
Code language: SQL (Structured Query Language) (sql)

0:

INSERT INTO stock_availability (product_id)
VALUES (900);
Code language: SQL (Structured Query Language) (sql)

INSERT INTO stock_availability (product_id, available)
VALUES
	(100, TRUE),
	(200, FALSE),
	(300, 't'),
	(400, '1'),
	(500, 'y'),
	(600, 'yes'),
	(700, 'no'),
	(800, '0');
Code language: SQL (Structured Query Language) (sql)

0

INSERT INTO stock_availability (product_id, available)
VALUES
	(100, TRUE),
	(200, FALSE),
	(300, 't'),
	(400, '1'),
	(500, 'y'),
	(600, 'yes'),
	(700, 'no'),
	(800, '0');
Code language: SQL (Structured Query Language) (sql)

1

Likewise, if you want to set a default value for a Boolean column when you create a table, you use the

 product_id | available
------------+-----------
        100 | t
        300 | t
        400 | t
        500 | t
        600 | t
(5 rows)
Code language: SQL (Structured Query Language) (sql)

8 constraint in the column definition as follows:

INSERT INTO stock_availability (product_id, available)
VALUES
	(100, TRUE),
	(200, FALSE),
	(300, 't'),
	(400, '1'),
	(500, 'y'),
	(600, 'yes'),
	(700, 'no'),
	(800, '0');
Code language: SQL (Structured Query Language) (sql)

2

In this tutorial, you have learned about the PostgreSQL

INSERT INTO stock_availability (product_id, available)
VALUES
	(100, TRUE),
	(200, FALSE),
	(300, 't'),
	(400, '1'),
	(500, 'y'),
	(600, 'yes'),
	(700, 'no'),
	(800, '0');
Code language: SQL (Structured Query Language) (sql)