The Data Types overview flagged these two as the ones Beacon Robotics' schema never actually needed. This lesson introduces the natural column that would, employees.is_manager, true for anyone else's manager_id pointing at them, and the raw-byte binary family that exists for data that was never meant to be read as text at all.
Learning Objectives
- Know which of the six databases has a real, native BOOLEAN type, and which fake it with something else.
- Write a true/false column correctly on every database in this course.
- Name each database's raw-byte (binary) type, and explain when one is actually needed.
True or False?
is_manager
Real BOOLEAN or a Stand-In?
Only one database has a real one
Matching Type
BOOLEAN, BIT, TINYINT(1), or CHAR(1)
Stored Correctly
No value besides true/false slips through
Boolean: Mostly Fake
| Database | Boolean Support |
|---|---|
| MySQL / MariaDB | BOOLEAN / BOOL exist only as aliases for TINYINT(1), nothing stops a 2 from being stored |
| PostgreSQL | A real, native BOOLEAN type: TRUE/FALSE/NULL enforced, anything else rejected |
| SQL Server | No BOOLEAN keyword at all, BIT is the equivalent, storing 0, 1, or NULL |
| Oracle | No native SQL BOOLEAN before Oracle 23c, the traditional stand-in is NUMBER(1) or CHAR(1) with a CHECK constraint; native BOOLEAN from 23c onward |
| SQLite | No native BOOLEAN, stored as INTEGER, 0 and 1 by convention, nothing enforced |
PostgreSQL is the one database here that actually rejects a value that isn't true, false, or NULL for a BOOLEAN column, genuinely enforced at the type level. Every other database is really storing a small number (or, in Oracle's pre-23c case, a CHECK-constrained character) and trusting the application to only ever write 0/1 or 'Y'/'N' into it.
Adding is_manager to employees
Notice SQLite and pre-23c Oracle both lean on a CHECK constraint from Module 8 to do the job their type system can't, restricting an otherwise-unrestricted column to exactly the two values a real boolean should allow.
ALTER TABLE employees ADD COLUMN is_manager BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE employees ADD COLUMN is_manager BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE employees ADD is_manager BIT NOT NULL DEFAULT 0;
ALTER TABLE employees
ADD is_manager CHAR(1) DEFAULT 'N' NOT NULL
CHECK (is_manager IN ('Y', 'N'));ALTER TABLE employees ADD is_manager BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE employees ADD COLUMN is_manager INTEGER NOT NULL DEFAULT 0 CHECK (is_manager IN (0, 1));
Binary: Raw Bytes, Not Text
| Database | Binary Types |
|---|---|
| MySQL / MariaDB | BINARY(n) / VARBINARY(n) for small fixed/variable binary; TINYBLOB/BLOB/MEDIUMBLOB/LONGBLOB for large binary data |
| PostgreSQL | BYTEA, one type for any length of raw binary data |
| SQL Server | BINARY(n) / VARBINARY(n), or VARBINARY(MAX) for large binary data |
| Oracle | RAW(n) for small fixed/variable binary; BLOB for large binary data |
| SQLite | BLOB, one type, any length, no enforced limit |
Try It Yourself
Beacon Robotics wants to store a small profile photo for each employee directly in the database. Write the column definition for photo, on PostgreSQL.
ALTER TABLE employees ADD COLUMN photo BYTEA;
Frequently Asked Questions
Should files or images actually be stored directly in the database like this?
Usually not, beyond small thumbnails or attachments. Most real applications store the file in dedicated object storage (an S3-style service) and keep only a URL or path string in the database, BLOB/BYTEA/VARBINARY exist for the genuine cases that need the file itself inside the database, not as the default choice for every image or upload.
Can a PostgreSQL BOOLEAN column be compared with = 1, the way MySQL's TINYINT(1) stand-in can?
No, PostgreSQL has no implicit cast between BOOLEAN and integer, WHERE is_manager = 1 fails with an operator-does-not-exist error. WHERE is_manager = TRUE, or simply WHERE is_manager, is the correct PostgreSQL form, this is a genuine, common surprise for anyone coming from MySQL.
Common Beginner Mistakes
Writing WHERE is_manager = 1 on PostgreSQL, expecting it to work like MySQL
PostgreSQL's BOOLEAN is a real, distinct type with no implicit integer comparison, this raises an error rather than silently matching. WHERE is_manager = TRUE or WHERE is_manager is the correct PostgreSQL equivalent.
Storing large files or images directly in a database column by default
This bloats the database, slows down backups, and is rarely necessary, most real-world systems store the file in object storage and keep only a reference (a URL or path) in the row. Reserve BLOB/BYTEA for genuinely small or genuinely database-coupled binary data.
Best Practices
- Use the real BOOLEAN type wherever one genuinely exists (PostgreSQL, Oracle 23c+); elsewhere, use the engine's standard stand-in (TINYINT(1), BIT) rather than inventing a custom one.
- Reserve BLOB/BYTEA/VARBINARY for data that's genuinely binary, images, files, encrypted blobs, never for text that merely happens to be long, that's what the large-text types from the previous lesson are for.
- On SQLite and pre-23c Oracle, back a true/false column with a CHECK constraint, don't leave it as an unrestricted number or character.
Interview Questions
Which of the six databases in this course has a native BOOLEAN type, and which fake it?
Only PostgreSQL (and Oracle from 23c onward) has a genuinely native, enforced BOOLEAN type. MySQL/MariaDB's BOOLEAN is just an alias for TINYINT(1), nothing stops an INSERT from storing a 2 or a -5 in it. SQL Server has no BOOLEAN keyword at all, using BIT instead, and SQLite and pre-23c Oracle have no boolean concept in the type system whatsoever, relying entirely on a CHECK constraint to restrict an INTEGER or CHAR(1) column to just two values.
Why would WHERE is_manager = 1 fail on PostgreSQL but work fine on MySQL?
On MySQL, is_manager is really a TINYINT(1) underneath, comparing it to the integer 1 is just an ordinary integer comparison. On PostgreSQL, is_manager is a real, distinct BOOLEAN type with no implicit cast to or from integer, so comparing it directly to 1 raises an operator-does-not-exist error, PostgreSQL requires WHERE is_manager = TRUE (or just WHERE is_manager) instead.
Summary
That completes the Data Types module: the five real categories, string, numeric, date/time, boolean, and binary, and the actual keyword each of the six databases in this course uses for every one of them. Only PostgreSQL has a genuinely native, enforced BOOLEAN, every other database here fakes it with a number, a bit, or a CHECK-constrained character, and binary types exist specifically for raw bytes that were never meant to be read as text.
What's Next?
Module 10, Dates, Views, and Security, is next, starting with the real functions and arithmetic DATE and TIMESTAMP columns actually support once a value is already stored correctly.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.