Common Mistakes When Creating and Modifying Tables

Hey there, future PostgreSQL gurus! Today’s topic is all about probably one of the most valuable skills in programming — avoiding mistakes. Of course, you can’t dodge all of them, especially if you’re just starting to learn SQL. But getting a handle on the typical ones? Totally doable. It’s like walking through a dark room full of furniture: you’ll bump into stuff a couple times, but then you’ll know where everything is. I’m here to help you sidestep those “sharp corners” when creating and changing tables.

Mistake 1: Picking the Wrong Data Type

Working with data types is like picking the right key for a lock. The wrong “key” (data type) can make your table work weirdly or just be less efficient.

Example of a mistake:

You want to create a column to store phone numbers and, out of habit, you pick INTEGER. Sounds logical, since phone numbers are numbers, right? But here’s the catch: INTEGER isn’t right for data that:

• Starts with zero (0123456789 will just become 123456789).
• Contains symbols like "+" or spaces.

-- Wrong:
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    phone_number INTEGER -- Oops
);

-- Right:
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    phone_number VARCHAR(15) -- Works for all phone number formats
);

How to avoid?
Always think about the nature of the data you’re going to store before picking a data type. If you’re not sure, check out the official PostgreSQL docs on data types.

Mistake 2: Ignoring NOT NULL, CHECK, UNIQUE Constraints

Constraints help keep your data solid. Forget them, and your table will turn into chaos, like having empty values where they shouldn’t be.

Example of a mistake: You create a table for storing students, but forget that name and age are required.

-- Wrong:
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER
);

INSERT INTO students (name, age) VALUES (NULL, NULL); -- Uh-oh, what’s this?!

The right way:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL, -- Name is required
    age INTEGER CHECK (age > 0) -- Age must be positive
);

How to avoid?
Always set constraints for required fields. Think of it as a “safety net” that keeps bad data out.

Mistake 3: Uniqueness Issues

Sometimes you might forget to add a UNIQUE constraint to a column that should only have unique values. That leads to duplicate data.

Example of a mistake: You create a table for storing email addresses, but forget to add UNIQUE.

-- Wrong:
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100)
);

INSERT INTO users (email) VALUES ('user@example.com');
INSERT INTO users (email) VALUES ('user@example.com'); -- That email already exists!

The right way:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100) UNIQUE -- Unique emails
);

How to avoid?
Always add UNIQUE if values should be unique. If you want more flexibility, you can use CONSTRAINT with a name to identify the constraint:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100),
    CONSTRAINT unique_email UNIQUE (email)
);

Mistake 4: Trouble When Modifying Tables (ALTER TABLE)

Using ALTER TABLE can be tricky, especially if there’s already data in the table. For example, you might forget about allowed values in a column, which leads to errors with existing data.

Example of a mistake: You want to add a NOT NULL constraint to an existing column, but there are NULL values in it.

-- Wrong:
ALTER TABLE students ALTER COLUMN name SET NOT NULL; -- Error!

If there are already rows with NULL in the table, PostgreSQL won’t let you add the constraint.

What to do?

Before adding constraints, make sure your data fits the requirements. For example:

UPDATE students SET name = 'Unknown' WHERE name IS NULL;
ALTER TABLE students ALTER COLUMN name SET NOT NULL;

Mistake 5: Deleting Tables or Data Without Checking

Dropping a table with DROP TABLE or deleting data with DELETE — that’s something you can’t undo. So always double-check what you’re deleting.

Example of a mistake:

DROP TABLE courses; -- Oops, that was the wrong table!

How to avoid?

Use the \dt command in psql to see what tables exist, and make sure you’re deleting the right one.

Or use DROP TABLE IF EXISTS to avoid errors when trying to drop a table that doesn’t exist:

DROP TABLE IF EXISTS courses;

Mistake 6: Trouble With Temporary Tables

Temporary tables disappear at the end of a session. If you try to use a temp table after the session’s over, you’ll get an error.

Example of a mistake:

CREATE TEMP TABLE temp_students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

-- Session ended, and now...
SELECT * FROM temp_students; -- Error: table is gone!

How to avoid?

Store temp data you need between sessions in regular tables, or document that temp tables are only for use within a single session.

Mistake 7: Forgetting Constraints During Testing

When developing, you might skip some constraints, thinking “eh, I’ll add them later.” But, trust me, “later” can easily be forgotten.

Example of a mistake:

CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

-- Inserting data:
INSERT INTO test_table (name) VALUES ('Duplicate Name');
INSERT INTO test_table (name) VALUES ('Duplicate Name'); -- Here come the problems...

How to avoid?

Always create tables with constraints, even during testing:

CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE -- Saves you a headache
);