Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

postgresql
sql
database

What is the three-valued logic of PostgreSQL?

Ubaid Bakhtiar

Grokking Modern System Design Interview for Engineers & Managers

Ace your System Design Interview and take your career to the next level. Learn to handle the design of applications like Netflix, Quora, Facebook, Uber, and many more in a 45-min interview. Learn the RESHADED framework for architecting web-scale applications by determining requirements, constraints, and assumptions before diving into a step-by-step design process.

In programming languages like C, Python, and JavaScript, logic can either be true or false. A simple comparison among two conditions can only yield one of these boolean values. However, there is a third possibility to a logical expression in SQL. In SQL, a logical expression can either be true, false, or unknown.

Where does the idea of the unknown come from?

Unknown essentially means that the logical expression is neither true nor false. Rather, it can be anything and is unknown. Unknown data value originates from the availability of null in SQL, representing the absence of data in the database. A NULL value means that SQL does not know what the data is, and the logical operation cannot be true or false but unknown. The NULL value is used to represent the missing data in our database.

Note: Every arithmetic operation with one or more NULL values returns an unknown result.

Code example

Let’s look at the following example of Null in PostgreSQL:

100 + NULL < 300
Comparison with null

The output of the above comparison would be NULL because the logical operation is simply a comparison between NULL and 300, that is, NULL < 300. There is no way of knowing if the condition is right or wrong. So, we get an unknown output.

We can also analyze the three-valued logic in a coding example.

Logical operator with null operand in SQL

The difference of NULL in SQL compared to other languages promotes it from a binary logic language to a trinary logic language. We may run the following code as an example of logical operation:

CREATE TABLE Students (
FirstName varchar(255),
Stipend int,
Expenses int
);
INSERT INTO Students (FirstName,Stipend,Expenses) VALUES ('George H.', 2400, 2000);
INSERT INTO Students (FirstName,Stipend,Expenses) VALUES ('Ben', NULL, 3200);
INSERT INTO Students (FirstName,Stipend,Expenses) VALUES ('Catherine', 3400, 2000);
INSERT INTO Students (FirstName,Stipend,Expenses) VALUES ('Dough', 700, 2000);
SELECT * FROM Students WHERE Stipend + Expenses > 3000;
Example of logical operation

The above code creates a table Students in PostgreSQL and inputs records in it. The lines 7 to 10 represent the insertion of data in the PostgreSQL database. We want to know which student has a combined value of stipend and expenses greater than 3000. The following statement is used to perform the logical operation:

SELECT * FROM Students WHERE Stipend + Expenses > 3000;
Logical operation statement

If we use some language other than PostgreSQL or SQL, we'll also have Ben in the output. But instead, only Aaron and Catherine are shown in the output. So The actual operation performed by SQL is as follows:

NULL + 3200 > 3000
NULL>3000
>>NULL
Unknown data as null

Ben's stipend is NULL, which means PostgreSQL is comparing the sum of an unknown value and 3200 with 3000. This comparison would reduce to a comparison between an unknown value or a NULL and 3000. In this case, PostgreSQL would return a null value which essentially represents an unknown decision.

RELATED TAGS

postgresql
sql
database

CONTRIBUTOR

Ubaid Bakhtiar
Copyright ©2022 Educative, Inc. All rights reserved

Grokking Modern System Design Interview for Engineers & Managers

Ace your System Design Interview and take your career to the next level. Learn to handle the design of applications like Netflix, Quora, Facebook, Uber, and many more in a 45-min interview. Learn the RESHADED framework for architecting web-scale applications by determining requirements, constraints, and assumptions before diving into a step-by-step design process.

Keep Exploring