PostgreSQL: Data Types and Table Management

Data Types and Table Management#

PostgreSQL, the database that’s both powerful and just a little bit quirky, offers a variety of data types and table management features. If you’ve ever wondered how to properly set up tables and constraints without turning your database into an unmanageable mess, you’re in the right place.

Common PostgreSQL Data Types#

PostgreSQL supports a wide range of data types. Choose wisely, or your future self (or your DBA) will come back to haunt you.

• Numeric types: INTEGER, BIGINT, DECIMAL, NUMERIC, SERIAL (Perfect for counting things, like how many times your queries have failed.)
• Character types: CHAR, VARCHAR, TEXT (For storing everything from usernames to deeply regrettable comments.)
• Date and time types: DATE, TIME, TIMESTAMP, INTERVAL (Because tracking when bad decisions were made is crucial.)
• Boolean type: Stores TRUE or FALSE (Also known as “Did this project meet the deadline?” data type.)
• JSON and JSONB types: If you love chaos, store JSON inside your relational database.
• Arrays and composite types: Because sometimes, a single value just isn’t enough.

Creating and Managing Tables#

Creating tables is where the magic begins—or where the nightmare starts, depending on your approach.

Syntax for CREATE TABLE#

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    registered_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Pro tip: Always think ahead. Changing a table structure later is like renovating a house while living in it—messy and full of regret.

Viewing Table Structure#

• \dt — Lists all tables.
• \d customers — Shows the structure of the customers table.

Constraints#

Constraints ensure that your database doesn’t turn into a lawless wasteland.

• PRIMARY KEY: Keeps your rows unique, just like your bad ideas.
• FOREIGN KEY: Links tables together, whether they like it or not.
• UNIQUE: Ensures values stay unique (looking at you, duplicate usernames!).
• CHECK: Validates data, because sometimes developers lie.
• NOT NULL: Because missing values can break everything.

Indexing Basics#

Indexes make your queries faster—because no one likes waiting.

Creating an Index#

CREATE INDEX idx_customers_email ON customers(email);

Tip: Indexing everything is like carrying your entire wardrobe everywhere—it slows you down.

Altering and Dropping Tables#

Need to modify a table? PostgreSQL allows it, but proceed with caution.

Modifying a Table#

ALTER TABLE customers ADD COLUMN phone_number TEXT;
ALTER TABLE customers ALTER COLUMN name TYPE TEXT;
ALTER TABLE customers DROP COLUMN registered_on;

Warning: Dropping a column is like deleting production data. Think twice, execute once.

Dropping Tables#

DROP TABLE customers CASCADE;

Fun Fact: CASCADE means “burn it all down”—all dependent objects vanish. Be sure before you commit database arson.

Hands-On Exercise#

Let’s get our hands dirty with some SQL.

1. Create a customers table with appropriate data types and constraints.
2. Insert sample data into the customers table.
3. Modify the table:
   • Add a new column (email)
   • Change a column data type (phone_number from INTEGER to TEXT)
   • Drop an unnecessary column.
4. Create an index on a frequently searched column.
5. Delete and recreate tables to understand the implications of dropping tables.

-- Step 1: Create the table
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    phone_number INTEGER
);

-- Step 2: Insert sample data
INSERT INTO customers (name, phone_number) VALUES ('Alice', 123456789);
INSERT INTO customers (name, phone_number) VALUES ('Bob', 987654321);

-- Step 3: Modify the table
ALTER TABLE customers ADD COLUMN email VARCHAR(255);
ALTER TABLE customers ALTER COLUMN phone_number TYPE TEXT;
ALTER TABLE customers DROP COLUMN phone_number;

-- Step 4: Create an index
CREATE INDEX idx_customers_email ON customers(email);

-- Step 5: Drop and recreate the table
DROP TABLE customers CASCADE;
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE
);

Final Thoughts#

Databases are like relationships—good design upfront saves a lot of heartache later. Keep your schema clean, your constraints strict, and your queries indexed. Happy querying!