PostgreSQL Tables

In the previous chapter, we learned about schemas in PostgreSQL and how they help organize objects inside a database. Now it’s time to explore the most fundamental building block of a relational database: the table.

1. What is a Table?

A table is a structured collection of rows and columns where data is stored. Each column has a data type (e.g., INTEGER, TEXT,TIMESTAMP), and each row represents a record.

For example, a simple users table might look like this:

2. Creating Tables

Let’s create a users table:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Explanation:

• id SERIAL PRIMARY KEY → auto-incrementing unique ID.
• name VARCHAR(100) → up to 100 characters, cannot be null.
• email VARCHAR(150) UNIQUE → must be unique and not null.
• created_at → defaults to the current timestamp.

3. Inserting Data into Tables

INSERT INTO users (name, email) 
VALUES ('Alice', 'alice@example.com'),
       ('Bob', 'bob@example.com');

This will insert two rows into the users table.

4. Querying Tables

To fetch all users:

SELECT * FROM users;

To fetch a specific user by ID:

SELECT * FROM users WHERE id = 1;

5. Updating Tables

To update Alice’s email:

UPDATE users 
SET email = 'alice@newdomain.com' 
WHERE id = 1;

6. Deleting Data

To delete Bob’s record:

DELETE FROM users WHERE id = 2;

7. Working with Tables in Next.js

Let’s integrate with our Next.js project. Suppose we want to fetch all users from the database and display them in an API route.

// pages/api/users.js
import pool from "@/lib/db";

export default async function handler(req, res) {
  try {
    const result = await pool.query("SELECT * FROM users ORDER BY id ASC");
    res.status(200).json(result.rows);
  } catch (error) {
    console.error("Error fetching users:", error);
    res.status(500).json({ error: "Failed to fetch users" });
  }
}

Now, when you visit /api/users, you’ll get a JSON list of users directly from PostgreSQL.

8. Altering Tables

You can modify existing tables using ALTER TABLE.

ALTER TABLE users ADD COLUMN age INT;

This will add a new age column to the users table.

9. Dropping Tables

To delete a table completely:

DROP TABLE users;

Use this command cautiously — it will permanently remove the table and all its data.

10. Best Practices

• Always use PRIMARY KEY constraints for uniqueness.
• Use NOT NULL where appropriate to avoid missing values.
• Use VARCHAR with sensible limits instead of unbounded TEXT.
• Back up tables before applying destructive changes like DROP.
• Index columns that are frequently searched for better performance.

11. Summary

In this chapter, we learned everything about PostgreSQL tables: creating them, inserting data, querying, updating, deleting, and altering them. We also saw how to integrate table queries inside a Next.js API route.

In the next chapter, we’ll explore PostgreSQL Data Types and understand how to design tables with the right types for your application’s needs.