Logical Operators (AND, OR, NOT) & Null Handling

This chapter covers the fundamentals of verifying database states, troubleshooting common query execution errors, and selecting appropriate PostgreSQL data types to enforce schema integrity. You will learn how to structure tables using auto-incrementing identifiers, exact numeric types, and variable-length character strings.

1. Query Verification & Troubleshooting Table Existence

When working with relational databases, verifying that your queries execute successfully and target the correct relations is a fundamental first step. A common issue encountered during database development is attempting to query a table that does not exist in the active schema or has been misspelled.

The Naive vs. Systematic Query Workflow

When a query is executed, the PostgreSQL engine must first parse the SQL statement, validate the syntax, and verify the existence of the target relation (table) in the system catalog.

Debugging the "Relation Does Not Exist" Error

If you attempt to run a query and receive an error stating that the relation does not exist, follow this debugging workflow:

1. Check Spelling and Case Sensitivity: PostgreSQL folding defaults to lowercase unless identifiers are wrapped in double quotes ("Course_Enrollments").
2. Verify Schema Context: Ensure the table is in the active schema (typically public). If it is in a custom schema, prefix the table name (e.g., schema_name.table_name).
3. Confirm Table Creation: Verify that the table creation transaction was committed successfully.

-- Naive query that may fail if misspelled or uncommitted
SELECT * FROM course_enrollment;

-- Corrected query targeting the exact table name
SELECT * FROM course_enrollments;

2. The Role of Data Types in Schema Design

Data types are the building blocks of database schemas. They define the nature of the data that can be stored in a column, enforce data integrity at the engine level, and optimize storage and query performance.

Choosing the wrong data type can lead to:

• Data Loss / Rounding Errors: Using floating-point numbers for financial data.
• Storage Inefficiency: Using overly large data types for small values (e.g., using BIGINT when SMALLINT suffices).
• Performance Degradation: Improper indexing and slow comparison operations on variable-length text instead of structured types.

3. The SERIAL Data Type: Auto-Incrementing Identifiers

The SERIAL data type is a special notation used to create auto-incrementing integer columns. It is not a true data type, but rather a convenient shorthand that instructs PostgreSQL to set up a sequence generator.

How SERIAL Works Under the Hood

When you define a column as SERIAL:

1. PostgreSQL creates a sequence object behind the scenes.
2. It sets the default value of the column to fetch the next value from that sequence using the nextval() function.
3. It implicitly adds a NOT NULL constraint to the column.

-- Conceptual equivalence of the SERIAL shorthand

-- 1. Using the SERIAL shorthand:
CREATE TABLE student_records (
    student_id SERIAL PRIMARY KEY,
    student_name VARCHAR(100)
);

-- 2. What PostgreSQL executes under the hood:
CREATE SEQUENCE student_records_student_id_seq;

CREATE TABLE student_records_equivalent (
    student_id INTEGER NOT NULL DEFAULT nextval('student_records_student_id_seq') PRIMARY KEY,
    student_name VARCHAR(100)
);

Real-Life Analogy

Think of the SERIAL data type as a ticket dispenser at a deli counter. Every customer who walks in pulls a ticket, and the number automatically increments by one (1, 2, 3, 4...). No two customers can ever pull the same number, and the counter clerk does not need to manually assign numbers to keep track of the queue.

4. The INT (Integer) Data Type: Handling Whole Numbers

The INT (or INTEGER) data type is used to store whole numbers without any fractional or decimal components.

Common Use Cases

• Counts: Number of students enrolled in a course, total page views.
• Ratings: Discrete scale values (e.g., rating a course from 1 to 5).
• Foreign Keys: Mapping relationships between tables where the primary key is an integer.

CREATE TABLE course_ratings (
    rating_id SERIAL PRIMARY KEY,
    course_id INT,
    rating_value INT CHECK (rating_value BETWEEN 1 AND 5)
);

5. The NUMERIC(P, S) Data Type: Precision vs. Scale

For values that require exact fractional representation—such as financial transactions, monetary values, or precise scientific measurements—PostgreSQL provides the NUMERIC (or DECIMAL) data type.

Understanding Precision (P) and Scale (S)

The NUMERIC type accepts two parameters:

• Precision (P): The total count of significant digits in the entire number (both to the left and to the right of the decimal point).
• Scale (S): The number of digits allowed to the right of the decimal point.

Code Example: Declaring Precision and Scale

CREATE TABLE financial_transactions (
    transaction_id SERIAL PRIMARY KEY,
    -- Total of 8 digits allowed, exactly 2 after the decimal point.
    -- Maximum value: 999999.99
    amount NUMERIC(8, 2) 
);

Note: Unlike floating-point types (REAL or DOUBLE PRECISION), which use binary approximations and can introduce rounding errors, NUMERIC stores numbers exactly as specified. Always use NUMERIC for monetary values.

6. The VARCHAR(n) Data Type: Variable Character Storage

The VARCHAR(n) (Variable Character) data type stores character strings of variable length up to a specified limit n.

Key Characteristics of VARCHAR

• Variable Storage: If you define a column as VARCHAR(100) but only store a string of 5 characters, PostgreSQL only stores those 5 characters (plus a small overhead of 1 to 4 bytes for length metadata). It does not pad the remaining space with blanks.
• The Parameter n: Represents the maximum number of characters (not bytes) the column can hold.
• Unbounded VARCHAR: If you omit n (i.e., write simply VARCHAR or TEXT), PostgreSQL allows strings of any length up to the system limit.

Note: In PostgreSQL, the theoretical maximum limit for a single field value is 1 GB. However, in practical application development, keeping individual field values within a reasonable limit (e.g., under 10 MB for standard text fields) is recommended to prevent memory bloat during query processing and sorting operations.

CREATE TABLE course_details (
    course_id SERIAL PRIMARY KEY,
    -- Enforces a strict limit of 150 characters
    course_title VARCHAR(150) NOT NULL,
    -- Unbounded variable character length for long descriptions
    course_description VARCHAR 
);

7. Tabular Synthesis of Core PostgreSQL Data Types

Summary and Key Takeaways

• Query Troubleshooting: Always verify table names against the system catalog when encountering "relation does not exist" errors. Ensure transactions are committed.
• Auto-Incrementing Keys: Use the SERIAL shorthand to automatically manage unique integer identifiers for primary keys without manual application-side calculation.
• Discrete vs. Continuous Numbers: Use INT for whole numbers (like counts and ratings) and NUMERIC for precise fractional values (like currency).
• Precision and Scale: In NUMERIC(P, S), P represents the total number of digits, while S represents the number of digits after the decimal point.
• Efficient Text Storage: Use VARCHAR(n) to enforce character-length limits at the database level, or unbounded VARCHAR when the text length is highly variable.
• Exact vs. Approximate Math: Never use floating-point types for financial calculations; always rely on the exact precision of the NUMERIC data type.