Deep Dive into Numeric & Character Data Types

This chapter explores the foundational aspects of defining database schemas in PostgreSQL. We examine how to enforce data integrity using numeric precision, scale, check constraints, and temporal data types (dates and timestamps), followed by establishing a robust connection pool in Node.js using the pg library.

1. Why Do We Need Constraints & Precise Data Types?

When designing a database schema, defining column names is not enough. Without strict rules, invalid, corrupt, or logically impossible data can enter your system.

Constraints act as guardrails for your database. They are rules enforced at the database engine level that prevent invalid data from being written to disk.

The Risks of Weak Schema Definitions

• Financial Inaccuracy: Storing monetary values as floating-point numbers (FLOAT or DOUBLE PRECISION) leads to rounding errors due to binary floating-point representation.
• Logical Anomalies: Allowing negative values in fields like salary, age, or stock_quantity breaks business logic.
• Missing Audit Trails: Failing to capture exactly when a record was created or updated makes debugging and auditing impossible.

2. The Core Mental Model: Precision and Scale

When dealing with exact fractional numbers (such as currency, measurements, or scientific data), PostgreSQL provides the NUMERIC (or DECIMAL) data type.

The NUMERIC type is defined using two parameters: Precision and Scale.

Key Definitions

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

Tabular Synthesis: Precision and Scale Examples

3. Deep Dive into Numeric Data Types & Check Constraints

A CHECK constraint is a boolean expression that every row in a table must satisfy before an INSERT or UPDATE operation can succeed.

Enforcing Positive Values

In our schema, we define the salary column as:

salary NUMERIC(10, 2) CHECK (salary > 0)

This ensures that no employee can be entered with a negative or zero salary.

💡 Real-World Career Analogy: Unpaid Internships vs. Building Projects

The instructor highlights a common real-world dilemma when discussing the CHECK (salary > 0) constraint: Unpaid Internships.

Many early-career developers accept unpaid internships out of fear of missing out (FOMO). However, the instructor advises never to give away your time for free.

• The Reality of Unpaid Internships: You often spend significant time and money commuting, only to sit around doing basic tasks without receiving structured mentorship.
• The Better Alternative: Stay home and build real-world, production-grade projects. Read official documentation, leverage AI tools like ChatGPT, and deploy actual products. By building independently, you will gain deeper technical skills than many working professionals with 2–3 years of experience.

4. Handling Temporal Data: Dates and Timestamps

PostgreSQL provides robust data types for tracking dates, times, and historical changes.

1. The DATE Type

• Used when you only need the calendar date (Year, Month, Day) without the time of day.
• Example: hire_date DATE NOT NULL DEFAULT CURRENT_DATE
• CURRENT_DATE is a built-in SQL function that automatically inserts the current system date if no value is provided.

2. The TIMESTAMP Type

• Used when you need both the calendar date and the precise time of day.
• Example: created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
• CURRENT_TIMESTAMP captures the exact date, hour, minute, second, and millisecond of the transaction.

Tracking Record Lifecycle

It is an industry best practice to include audit columns on every table:

• created_at: Set once when the row is inserted.
• updated_at: Set initially on creation, and updated whenever the row is modified.

5. Step-by-Step Table Creation & Mock Data Insertion

Let's write and execute the SQL DDL (Data Definition Language) script to create our table with all the constraints and temporal defaults discussed.

Step 1: Create the Table Schema

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    salary NUMERIC(10, 2) NOT NULL CHECK (salary > 0),
    hire_date DATE NOT NULL DEFAULT CURRENT_DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Insert Mock Data for Verification

To verify that our constraints, default values, and data types work correctly, we run an INSERT query:

INSERT INTO employees (first_name, last_name, email, salary)
VALUES 
('Amit', 'Sharma', 'amit.sharma@example.com', 75000.00),
('Priya', 'Patel', 'priya.patel@example.com', 92000.50),
('Rohan', 'Verma', 'rohan.verma@example.com', 58000.00);

Data Flow and Validation Pipeline

Step 3: Verify the Inserted Data

Run a SELECT query to view the populated table:

SELECT * FROM employees;

6. Connecting PostgreSQL to Node.js via Connection Pooling

To interact with our database from a backend application, we must establish a connection between our Node.js server and PostgreSQL. We do this using the pg (node-postgres) library.

Why Use Connection Pooling?

Opening a new database connection for every single HTTP request is highly inefficient. It introduces latency due to the TCP handshake and consumes significant database resources.

A Connection Pool maintains a cache of active, reusable database connections. When a request comes in, it borrows a connection from the pool, executes the query, and immediately returns it to the pool.

Implementing the Connection Pool in Express/Node.js

In your index.js file, import the Pool class from the pg package to manage your database connections:

// index.js
import pkg from 'pg';
const { Pool } = pkg;

// Initialize the Connection Pool
const pool = new Pool({
  user: 'postgres',
  host: 'localhost',
  database: 'company_db',
  password: 'your_secure_password',
  port: 5432,
  max: 20, // Maximum number of clients in the pool
  idleTimeoutMillis: 30000, // Close idle clients after 30 seconds
  connectionTimeoutMillis: 2000, // Return an error if a connection takes longer than 2 seconds
});

// Export the pool to use across your application
export { pool };

Summary and Key Takeaways

• Constraints are Essential: Constraints prevent bad data from corrupting your database. They are enforced at the database level, serving as your final line of defense.
• Precision vs. Scale: In NUMERIC(P, S), Precision (P) is the total number of digits, and Scale (S) is the number of digits allowed after the decimal point.
• Use Exact Types for Money: Always use NUMERIC or DECIMAL for financial transactions to avoid floating-point rounding errors.
• Check Constraints: Use CHECK constraints to enforce business rules directly inside your database schema (e.g., CHECK (salary > 0)).
• Temporal Defaults: Use CURRENT_DATE and CURRENT_TIMESTAMP to automatically track when records are created or modified.
• Connection Pooling: Always use a connection pool (pg.Pool) instead of opening single client connections in production Node.js applications to ensure high performance and scalability.