Add Postgres Not Null in SQL to Existing Column

Introduction

In this article, we will explain the use of the Postgres NOT NULL SQL operator from three perspectives, including:

  • What? What does NOT NULL do?
  • Why? In what circumstances do we want to use an operator like this?
  • How? How do we use the NULL (combined with NOT) operator in our SQL?

Prerequisites

  • Basic understanding of how to execute SQL statements, either with PostgreSQL alone, using PG Admin or some other database administration tool, or by using a language – like Python, for example – that provides a way to connect to your database and send it SQL commands, whether that be to query, insert, or update your database.
  • Basic understanding of what “NULL” means.

What is the NOT NULL operator? How does it work? Why use it?

In Postgres, NOT NULL is usually used in the WHERE clause; returning a true or false, depending on the opposite of what NULL returns. Confused yet? Yay for those double-negatives!

A use of a useful use case for understanding use of NOT NULL

If we want to return all the records in our “users” table indiscriminately, we execute the following SQL statement:

SELECT t_name_first, t_name_last, b_hawt
FROM Users

Notice the data returned in the view below. Pay particular attention to the field titled “b_hawt”.

t_name_firstt_name_lastb_hawt
BrunhildaHorseface
JasmineWonderbewbstrue
CarmineWartalicious
KimmyFemlarougetrue

Great job! You win a new car! Kidding. But if you keep reading, you will win valuable knowledge. Let’s say we want to return only the records where the user is marked in our database as “hawt” (b_hawt = true). Good thing the company’s Judge of Hawtness worked closely with the Database Admin to track this data! Before we continue:

  • Show your Database Admin some recognition and appreciation for his genius.
  • Study the first SQL statement above and the data returned. Can you guess what additional line we need to add and how it will be structured? Hint: It begins with the SQL “WHERE” clause. I’ll give you some space, so your brain is not corrupted by the answer.

Leisure-time screen space about one of our users, Jasmine, so you don’t see the answer below immediately

What kind of person is Jasmine? She likes short walks to the refrigerator, shares your favorite philosophies in regard to bedroom activities, politics, and child-rearing. She’s also super smart but not in a way that crushes your ego. Finally, Jasmine gets along great with all your friends but is too loyal to ever fool around behind your back.

OK. Enough stalling. Let’s look now at the SQL that filters our data so we see only users marked as “hawt”:

SELECT t_name_first, t_name_last
FROM Users
WHERE b_hawt IS NOT NULL

The above SQL command yields the following results:

t_name_firstt_name_lastb_hawt
JasmineWonderbewbstrue
KimmyFemlarougetrue

Can you see why our results dropped from 4 rows to 2? I won’t tell if you don’t know the answer. After all, your thirst for new knowledge is what brought you here!

As I’m sure you figured out, because you are a super-genius with a planet-sized brain, the difference is that we are testing the boolean value – which is either true or false (or NULL) – in the “bhawt” field. If the value is NULL, the statement gets interpreted _for that row as “false” because it is not the value of NOT NULL that we tested for. The condition of “NOT NULL” not being met, no record is returned for that row. Sort of like if you ask a woman to go out and she doesn’t answer. She didn’t say yes (true) or no (false). She didn’t answer at all.

What about a value of “false”?

You might ask, “But what if we have some “false” values in a row or rows and we don’t want to pull those records, either? It’s this kind of question that validates my judgement of your exceptionally high intelligence! Let’s look at an example.

SELECT t_name_first, t_name_last, b_hawt
FROM Users

Notice the data returned in the view below. Pay particular attention to the field titled “b_hawt”.

t_name_firstt_name_lastb_hawt
BrunhildaHorseface
JasmineWonderbewbstrue
CarmineWartaliciousfalse
KimmyFemlarougetrue

Notice the difference? Carmine’s record – specifically the “b_hawt” field – is no longer empty. Take your Database Admin – or whoever entered the data – to lunch for getting on the ball with this. We now know that Carmine’s “hawt” rating is “false”. But also notice that Brunhilda still hasn’t been updated. This is where we can gain an understanding of why we want to use the NOT NULL operator in our applications:

Sometimes values are that special kind of empty that is defined as NULL.

This can be quite common. So we are being good coders who think about the future and guard against errors by checking for NULL. That said, let’s look at a small change we can make to our SQL so that we check for “false” as well.

With the data we see above, if we use our previous SQL query that checks only for NOT NULL, we get the following three records:

t_name_firstt_name_lastb_hawt
JasmineWonderbewbstrue
CarmineWartaliciousfalse
KimmyFemlarougetrue

Not good enough. We still need to eliminate the record that has a “false” in it’s row.

SELECT t_name_first, t_name_last
FROM Users
WHERE b_hawt IS NOT NULL
AND b_hawt = TRUE

Now we finally get the results we want:

t_name_firstt_name_lastb_hawt
JasmineWonderbewbstrue
KimmyFemlarougetrue

Can you see why our results dropped from 3 rows to 2? It’s because of our additional line of SQL code:

AND b_hawt = TRUE

That line says to PostgreSQL, “Hey we filtered the recordset returned to be sure no NULLs exist and we now also want to filter to leave out any rows that have “false” in the “b_hawt” field.

Conclusion

Here we learned how to use the NOT NULL operator in PostgreSQL queries. We also explored a real world example of why one may want to use this operator. Did you see the pattern in our examples of eliminating some rows from the returned recordsets?

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.