Definition of PostgreSQL Boolean
PostgreSQL Boolean is a simple data type that we have used to represent only the structure of true or false data or values. PostgreSQL will support the SQL99 defined Boolean data type of SQL standard; Boolean is also known as “bool”, bool is an alias of Boolean data type in PostgreSQL. We can set the null value of Boolean data type in PostgreSQL; if we have set a Null value of Boolean data type, then it is not interrupted as true or false. In PostgreSQL, the Boolean data type has three states like true, false and null.
Syntax and Parameter
Syntax and parameter of PostgreSQL Boolean are given below:
Syntax:
Below is the syntax of the Boolean data type in PostgreSQL are as follows. We have defining Boolean data type at the time of table creation and insertion.
CREATE TABLE table_name (Any table name given to the table) (Column_name data
Type NOT NULL, Column_name (Column name to define Boolean data
Type) BOOLEAN NOT NULL (Define not null value of Boolean data type column));
Alter table table_name ADD COLUMN column_name BOOLEAN (Define Boolean data
type to the column)
Insert into table_name values (true, ‘t’, ‘true’, ‘y’, ‘yes’, ‘1’);
Insert into table_name values (false, ‘f’, ‘false’, ‘f’, ’no’, ‘0’);
Parameter:
Below is the parameter description of the above syntax are as follows.
- Create: Create a new table and define a Boolean data type of table column. We have defining Boolean data type at the time of table creation. Also, we have defined it after table creation using alter command.
- Table name: Create a new table to define the data type of column as Boolean in PostgreSQL. We can give any name to a newly created table.
- Column name: Name of the column on which we have defined a Boolean data type. We have set a Boolean value is true, false and null in PostgreSQL.
- Data type: Define the data type of column. We can define any data type of column. Boolean is the most useful and important data type.
- Not Null: We defined a not null value of the column. If we set not the column’s null value, then null value insertion is not allowed in the table.
- Boolean: It is a simple data type that has used to represent only the structure of true or false data or values. In PostgreSQL, the Boolean data type has three states like true, false and null.
- Add: Add a new column using alter command and defined Boolean data type to the same column in PostgreSQL.
- Alter: We have added a column after creating a table and defining a Boolean data type to the same column.
- True, t, yes, 1, and y: This is the valid, true value of Boolean data type.
- False, f, no, 0 and n: This is the valid false value of Boolean data type in PostgreSQL
How Boolean Data Type Works in PostgreSQL?
Below is the working of the Boolean data type.
- PostgreSQL Boolean in data type has three states like true, false and null.
- PostgreSQL will support the SQL99 defined Boolean data type of SQL standard; Boolean is also known as “bool”, bool is an alias of a Boolean data type.
- It is a simple data type that has used to represent only the structure of true or false data or values.
- We can set the null value of Boolean data type; if we have set a Null value of Boolean data type, then it is not interrupted as true or false.
- In PostgreSQL, we can define a Boolean value as null, true and false. After defining a true Boolean data type, all the values which under in true are retrieved.
- If we define Boolean value as false, all values of false will be retrieved from the table. Defining Boolean value as null is not interrupted as true or false.
- The valid, true value of the Boolean data type is as follows.
- true
- ‘t’
- ‘true’
- ‘y’
- ‘yes’
- 1
- The valid false value of the Boolean data type as follows.
- false
- ‘f’
- ‘false’
- ‘n’
- ‘no’
- 0
- All the PostgreSQL Boolean data type values are enclosed with the quotes except as true and false.
- Boolean support the single datatype, but Boolean has three states.
- A boolean data type is a simple data type that only represents a true and false value.
Examples of PostgreSQL Boolean
Below is the example of the Boolean data type is as follows.
1. We have defined the Boolean data type to a column at the time of table creation. We have created a product table, and on the availability column, we have to define the Boolean data type are as follows.
testing=# CREATE TABLE product (prod_id INT NOT NULL, prod_name character(10) NOT NULL, delivery_address character(20) NOT NULL, delivery_phone character(14), prod_availability BOOLEAN NOT NULL, delivery_date timestamp NOT NULL);
2. We have defining Boolean data type after the creation of the table. Below is the example of the same is as follows.
testing=# ALTER table product ADD COLUMN prod_delivery_status BOOLEAN;
3. Insert Boolean data into the table.
testing=# Insert into product values (1, 'ABC', 'Pune', '1234567890', 'yes', 'true');
testing=# Insert into product values (2, 'ABC', 'Pune', '1234567890', 'no', 'false');
testing=# Insert into product values (2, 'ABC', 'Pune', '1234567890', '1', '0');
testing=# select * from product;
4. Retrieve data from a table in which product availability is true.
testing=# select prod_id, prod_name, delivery_address, delivery_phone, prod_availability from product where prod_availability = true;
5. Retrieve data from a table in which product availability is false.
testing=# select prod_id, prod_name, delivery_address, delivery_phone, prod_availability from product where prod_availability = false;
Recommended Articles
This is a guide to PostgreSQL Boolean. Here we discuss the definition and how boolean data type works in PostgreSQL, along with examples. You may also have a look at the following articles to learn more –