DEV Community: Manoj Kumar

Modifying Tables Safely in PostgreSQL — ALTER TABLE, Constraints, and Defaults

Manoj Kumar — Sun, 29 Mar 2026 14:02:09 +0000

Creating a table right the first time is ideal but in real projects tables change. Business rules shift, new requirements come in, and things that were optional before become mandatory. This set of problems is all about using ALTER TABLE to modify existing tables without throwing everything away and starting over.

1. Making Email Mandatory on an Existing Table

The customers table already exists and the email column currently allows NULL. To make it required going forward you add a NOT NULL constraint.

ALTER TABLE customers
ALTER COLUMN email SET NOT NULL;

One thing worth knowing here is that if there are already rows in the table with NULL in the email column PostgreSQL will reject this command. You would need to fill in those empty emails first before the constraint can be applied. The database protects you from creating a constraint that existing data already violates.

2. Adding a UNIQUE Constraint to an Existing Column

The users table has a username column but it was created without a uniqueness rule. To enforce it now without recreating the table:

ALTER TABLE users
ADD CONSTRAINT users_username_unique UNIQUE (username);

Giving the constraint a name like users_username_unique is optional but it is a good habit. Named constraints are much easier to drop later if you ever need to remove them.

3. Adding a CHECK Constraint to Price

The products table needs to enforce that price is always greater than zero. CHECK constraints can be added to existing tables the same way.

ALTER TABLE products
ADD CONSTRAINT products_price_check CHECK (price > 0);

Again PostgreSQL will check existing rows before applying this. If any product already has a price of zero or below the command will fail until those rows are fixed.

4. Setting a Default Value on an Existing Column

The orders table has a status column but no default was set when it was created. Adding a default now means any future inserts that do not mention status will automatically get pending.

ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'pending';

This only affects future inserts. Existing rows that already have NULL in status are not touched by this command.

5. Adding a New Column with Constraints

This one adds a brand new salary column to the employees table. The column cannot be NULL and must always be above 10000.

ALTER TABLE employees
ADD COLUMN salary NUMERIC NOT NULL DEFAULT 10001
CHECK (salary > 10000);

The DEFAULT 10001 is needed here because if there are existing rows in the table PostgreSQL needs to know what value to put in the new salary column for those rows. Without a default it cannot add a NOT NULL column to a table that already has data. Once the column is added and the existing rows have values the default can be removed if you want to force future inserts to always provide a salary explicitly.

6. Changing a Foreign Key to Use CASCADE on Delete

Foreign key constraints cannot be modified directly. To change the behavior you drop the old constraint and add a new one with the CASCADE rule.

First find and drop the existing foreign key:

ALTER TABLE employees
DROP CONSTRAINT employees_department_id_fkey;

Then add it back with ON DELETE CASCADE:

ALTER TABLE employees
ADD CONSTRAINT employees_department_id_fkey
FOREIGN KEY (department_id)
REFERENCES departments(id)
ON DELETE CASCADE;

Now when a department is deleted all employees linked to that department are automatically removed as well. The two step drop and recreate is just how PostgreSQL handles constraint changes.

7. Removing a CHECK Constraint

To remove a constraint you need its name. If you named it when you created it this is straightforward. If you did not name it PostgreSQL generated one automatically and you can find it by checking the table definition.

-- Check the constraint name first
\d accounts

-- Then drop it
ALTER TABLE accounts
DROP CONSTRAINT accounts_balance_check;

Once dropped there is nothing stopping a balance from going negative. Make sure removing a constraint is actually what you want before running this because there is no undo in a live database without a backup.

8. Adding a Composite Unique Constraint

The payments table needs to make sure that the combination of user_id and transaction_id is unique. The same user cannot have the same transaction ID twice.

ALTER TABLE payments
ADD CONSTRAINT payments_user_transaction_unique
UNIQUE (user_id, transaction_id);

This works the same as defining a composite unique constraint at table creation time. The combination of both columns has to be unique. Each column on its own can repeat, but the pair together cannot.

What This Set Taught Me

ALTER TABLE is something you will use constantly in real projects. Schema design is never finished on day one. Requirements change, bugs get caught, and data quality rules get added as the system grows.

The key habit to build is naming your constraints. It costs nothing when you create them and saves a lot of pain when you need to find and drop them later. The other thing worth remembering is that adding constraints to tables with existing data is not always instant. PostgreSQL validates every existing row before it accepts the new rule which is the right behavior even if it feels slow on large tables.

PostgreSQL Table Design — Constraints, Defaults, and Foreign Keys Explained Simply

Manoj Kumar — Sun, 29 Mar 2026 14:00:10 +0000

This set of problems is all about designing tables the right way from the start. Not just creating columns but making the database itself enforce the rules so bad data can never sneak in. Each problem adds something new and by the end you have a solid picture of how real tables are built.

1. Students Table — Primary Key

Every student needs a unique ID. That is what PRIMARY KEY does. It makes sure no two rows have the same ID and the value can never be empty.

sql

Handling Duplicate Transfers in PostgreSQL — Idempotency and How Real Payment Systems Stay Safe

Manoj Kumar — Sun, 29 Mar 2026 13:58:11 +0000

This one is about a problem that does not come from bad code or a hardware failure. It comes from the real world being messy. A user taps pay, the network stutters, the app retries, and now the same transfer request hits the database twice. Does the money move twice? In a naive system, yes it does. This problem is about understanding why that happens and how to stop it.

The Setup

Alice has 1000, Bob has 500. We want to transfer 200 from Alice to Bob.

SELECT name, balance FROM accounts;
-- Alice: 1000
-- Bob: 500

Running the Same Transfer Twice

This is what happens when a network retry fires the same request again without any duplicate protection.

First execution:

BEGIN;

UPDATE accounts
SET balance = balance - 200,
    last_updated = CURRENT_TIMESTAMP
WHERE name = 'Alice';

UPDATE accounts
SET balance = balance + 200,
    last_updated = CURRENT_TIMESTAMP
WHERE name = 'Bob';

COMMIT;

Check balances:

SELECT name, balance FROM accounts;
-- Alice: 800
-- Bob: 700

Second execution, same query runs again because the app retried:

BEGIN;

UPDATE accounts
SET balance = balance - 200,
    last_updated = CURRENT_TIMESTAMP
WHERE name = 'Alice';

UPDATE accounts
SET balance = balance + 200,
    last_updated = CURRENT_TIMESTAMP
WHERE name = 'Bob';

COMMIT;

Check balances again:

SELECT name, balance FROM accounts;
-- Alice: 600
-- Bob: 900

Alice lost 400 total. Bob gained 400 total. The transfer ran twice and PostgreSQL had no way to know the second one was a duplicate. From the database's perspective both were perfectly valid transactions. This is the problem.

Why PostgreSQL Cannot Catch This on Its Own

PostgreSQL guarantees that each transaction is atomic, consistent, isolated, and durable. But it cannot read your mind. Both transfer requests were valid SQL, both passed the CHECK constraint, both committed cleanly. Nothing about the second transaction looked wrong to the database. The duplicate problem is a business logic problem, not a database constraint problem. The database cannot solve it alone.

The Fix — Idempotency Keys

The standard solution in real payment systems is an idempotency key. Every transfer request gets a unique ID generated by the client, like a UUID. Before processing the transfer, the system checks whether a transaction with that ID has already been processed. If it has, it skips the transfer and returns the original result. If it has not, it processes it and records the ID.

First we need a table to track processed transactions:

CREATE TABLE transactions (
    transaction_id UUID PRIMARY KEY,
    sender TEXT NOT NULL,
    receiver TEXT NOT NULL,
    amount INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Now the transfer logic checks this table before doing anything:

BEGIN;

-- Check if this transaction was already processed
DO $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM transactions
        WHERE transaction_id = 'a1b2c3d4-e5f6-7890-abcd-ef1234567890'
    ) THEN
        RAISE EXCEPTION 'Duplicate transaction detected. Skipping.';
    END IF;
END $$;

-- Record the transaction first
INSERT INTO transactions (transaction_id, sender, receiver, amount)
VALUES (
    'a1b2c3d4-e5f6-7890-abcd-ef1234567890',
    'Alice',
    'Bob',
    200
);

-- Now do the actual transfer
UPDATE accounts
SET balance = balance - 200,
    last_updated = CURRENT_TIMESTAMP
WHERE name = 'Alice';

UPDATE accounts
SET balance = balance + 200,
    last_updated = CURRENT_TIMESTAMP
WHERE name = 'Bob';

COMMIT;

First run goes through cleanly. Alice is at 800, Bob is at 700, and the transaction ID is recorded.

Second run with the exact same transaction ID hits the IF EXISTS check, finds the record, raises an exception, and the whole block rolls back. No second deduction happens.

SELECT name, balance FROM accounts;
-- Alice: 800
-- Bob: 700

The balance is exactly what it should be after one transfer. The retry had zero effect.

What Happens If the System Crashes Between the INSERT and the COMMIT

This is worth thinking about. What if we recorded the transaction ID but then crashed before the balance updates committed?

Because everything is inside a single transaction block, a crash before COMMIT rolls back everything including the INSERT into the transactions table. The transaction ID is not recorded. When the system comes back up and the retry fires, the ID check finds nothing and the transfer goes through normally.

The INSERT and the two UPDATEs either all commit together or none of them do. That is atomicity protecting the idempotency logic itself.

How Real Systems Like PhonePe and GPay Handle This

Real payment systems take this a few steps further. The idempotency key is usually generated on the client side and sent as part of the API request header. The backend stores it in a fast lookup store like Redis before even touching the database, so duplicate requests can be caught early before they reach the transaction layer. The database level check is a second safety net for cases where the Redis check is bypassed or the cache expires.

Some systems also use a status field on the transaction record. Instead of just checking for existence, they check the status. If the status is pending it means a previous attempt started but did not finish, so the system can decide whether to retry or wait. If the status is completed it returns the original result immediately without reprocessing.

What I Took Away from This

The database handles ACID guarantees beautifully but it cannot know whether two identical requests are intentional or accidental. That distinction lives in the application layer and the standard tool for handling it is an idempotency key tied to a transaction log table.

The elegant part is that because the idempotency check and the balance updates live inside the same transaction block, you never end up in a state where the key is recorded but the money did not move, or the money moved but the key was not recorded. Atomicity keeps the duplicate protection and the actual business logic perfectly in sync.

This is the kind of thing that separates a hobby project from a system you would actually trust with someone's money.

Does Your Payment Data Survive a Crash? Testing Durability in PostgreSQL

Manoj Kumar — Sun, 29 Mar 2026 13:55:39 +0000

Everything we explored before this was about correctness during a transaction. This one is about what happens after. Specifically, once a transfer is committed and the system crashes five seconds later, does that transfer still exist when the database comes back up? The answer should always be yes and understanding why that is the case is what this problem is really about.

This is the D in ACID. Durability.

The Transfer — Committing the Transaction

Starting with Alice at 1000 and Bob at 500. We do a clean transfer of 300 from Alice to Bob and commit it.

BEGIN;

UPDATE accounts
SET balance = balance - 300,
    last_updated = CURRENT_TIMESTAMP
WHERE name = 'Alice';

UPDATE accounts
SET balance = balance + 300,
    last_updated = CURRENT_TIMESTAMP
WHERE name = 'Bob';

COMMIT;

Check the balances right after:

SELECT name, balance FROM accounts;

Alice is at 700. Bob is at 800. Transaction committed, data updated.

Simulating a Crash or Restart

Now we simulate what happens when the system goes down. In a real scenario this could be a server power cut, a container crash, or PostgreSQL being forcefully stopped. To simulate it locally you can stop the PostgreSQL service abruptly.

On Linux or Mac:

sudo systemctl stop postgresql

Or forcefully kill the process to simulate a hard crash:

sudo pkill -9 postgres

Wait a few seconds and then bring it back up:

sudo systemctl start postgresql

Now reconnect to the database and check the balances again:

SELECT name, balance FROM accounts;

Alice is still at 700. Bob is still at 800.

The committed transfer survived the crash completely. Nothing was lost.

Why Does This Work — The Write Ahead Log

PostgreSQL does not write changes directly to the data files the moment you run an UPDATE. Instead, before touching the actual data, it writes a record of the change to something called the Write Ahead Log or WAL. This is a sequential log file on disk that records every change that is about to happen.

The rule is simple. The log entry must be written to disk before the actual data change is confirmed as committed. So by the time COMMIT returns successfully to you, the change is already recorded in the WAL on disk.

If the system crashes after COMMIT but before the actual data files are updated, PostgreSQL reads the WAL during startup and replays any changes that were logged but not yet written to the main data files. Your data is recovered automatically.

This is why the committed transfer survived. It was in the WAL before the crash happened.

What Happens If the Crash Occurs Just Before COMMIT

This is the other scenario worth thinking about. The transfer started, Alice was deducted, Bob was credited, but the system crashed a split second before COMMIT ran.

When PostgreSQL restarts it looks at the WAL and sees a transaction that was started but never committed. It does not replay those changes. The incomplete transaction is rolled back during recovery.

Alice goes back to 1000. Bob goes back to 500. The crash is treated exactly like a ROLLBACK.

-- After restart, if crash happened before COMMIT
SELECT name, balance FROM accounts;

-- Alice: 1000
-- Bob: 500

No partial update survives. No money is lost in either direction.

The Two Scenarios Side by Side

Crash after COMMIT means the transfer is permanent. WAL has the full record and PostgreSQL replays it on startup. Both balances reflect the transfer.

Crash before COMMIT means the transfer never happened as far as the database is concerned. The incomplete transaction is discarded during recovery. Both balances go back to what they were before the transfer started.

There is no in between state that persists. Either the full transaction made it or none of it did.

What About the Tiny Gap Right at COMMIT

There is a brief moment during COMMIT where PostgreSQL is in the process of flushing the WAL to disk. If the hardware fails at exactly that microsecond, PostgreSQL uses checksums and log sequencing to figure out during recovery whether the commit completed or not. If it cannot confirm the commit was fully written, it treats the transaction as incomplete and rolls it back.

This is why PostgreSQL recommends running on hardware with reliable disk write guarantees and why things like UPS systems and battery backed disk controllers matter in production payment systems. The software guarantees durability as long as the hardware faithfully confirms that a disk write actually happened.

What I Understood from This

Durability is the promise that a committed transaction is permanent no matter what happens next. The WAL is the mechanism that makes that promise real. It is a running record of everything that should happen, written before it actually happens, so that a crash at any point can always be recovered from cleanly.

For a payment system this is not optional. Users need to know that a successful transfer confirmation means the money moved, full stop. Not probably moved, not moved unless the server crashes, but moved permanently. The WAL plus PostgreSQL's crash recovery is what makes that guarantee possible.

ACID is not just four letters. Each one is a specific guarantee and Durability is the one that makes all the others matter even when the power goes out.

Concurrent Transactions in PostgreSQL — Isolation Levels, Dirty

Manoj Kumar — Sun, 29 Mar 2026 13:53:20 +0000

This was the most eye opening problem in the entire series so far. Everything before this was single session stuff. One query, one transaction, one outcome. This one throws two sessions at the same account at the same time and asks what happens. The answer depends entirely on something called isolation levels and understanding this is what separates someone who writes SQL from someone who actually understands databases.

The Setup

Same table, same data.

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    balance INT NOT NULL CHECK (balance >= 0),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO accounts (name, balance)
VALUES ('Alice', 1000), ('Bob', 500);

Alice has 1000. Bob has 500. Now imagine two people trying to send money from Alice's account at the exact same moment.

The Problem We Are Trying to Avoid

Alice has 1000. Session 1 tries to deduct 800. Session 2 also tries to deduct 700. If both succeed, Alice ends up with negative 500 which the CHECK constraint will catch. But the scarier version is when one session reads the balance before the other commits, does its own math on that stale number, and both deductions go through based on the same starting balance. That is a lost update and that is real money disappearing.

Experiment 1 — Default Isolation (Read Committed)

PostgreSQL's default isolation level is Read Committed. This means a transaction can only see data that has already been committed by other transactions. It cannot see uncommitted changes from another session.

Open Session 1 and start a transaction but do not commit it yet:

-- Session 1
BEGIN;

UPDATE accounts
SET balance = balance - 800,
    last_updated = CURRENT_TIMESTAMP
WHERE name = 'Alice';

-- Do not commit yet. Leave this transaction open.

Alice's balance is now 200 inside Session 1 but this change is not committed so the rest of the world cannot see it yet.

Now open Session 2 and try to read Alice's balance:

-- Session 2
SELECT balance FROM accounts WHERE name = 'Alice';

Session 2 still sees 1000. It cannot see the uncommitted deduction from Session 1. That is Read Committed doing its job. No dirty reads.

Now Session 2 tries to deduct 700 from Alice:

-- Session 2
BEGIN;

UPDATE accounts
SET balance = balance - 700,
    last_updated = CURRENT_TIMESTAMP
WHERE name = 'Alice';

Session 2 gets blocked. PostgreSQL puts it in a wait state because Session 1 is holding a lock on Alice's row. Session 2 cannot touch that row until Session 1 either commits or rolls back.

Now go back to Session 1 and commit:

-- Session 1
COMMIT;

Alice's balance is now 200. Session 1's lock is released. Session 2 wakes up, reads the updated balance of 200, and tries to deduct 700. That would bring Alice to negative 500 which violates the CHECK constraint. PostgreSQL blocks it.

ERROR: new row for relation "accounts" violates check constraint "accounts_balance_check"

Session 2 rolls back. Alice stays at 200. The system protected itself.

Experiment 2 — Repeatable Read Isolation

Repeatable Read is stricter. Once a transaction reads a row, it will keep seeing the same value for that row for the entire duration of the transaction, even if another session commits a change to it in the meantime.

-- Session 1
BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT balance FROM accounts WHERE name = 'Alice';
-- Sees 1000

-- Session 2
BEGIN;

UPDATE accounts
SET balance = balance - 200
WHERE name = 'Alice';

COMMIT;
-- Alice is now 800

Now back in Session 1:

-- Session 1
SELECT balance FROM accounts WHERE name = 'Alice';
-- Still sees 1000, not 800

Session 1 is living in a snapshot of the data from the moment it started. Even though Session 2 committed a real change, Session 1 does not see it. This prevents non-repeatable reads which is when the same query returns different results within the same transaction.

Now if Session 1 tries to update Alice based on what it thinks the balance is:

-- Session 1
UPDATE accounts
SET balance = balance - 800
WHERE name = 'Alice';

PostgreSQL detects that another transaction already modified this row after Session 1's snapshot was taken. It throws a serialization error and forces Session 1 to retry.

ERROR: could not serialize access due to concurrent update

Experiment 3 — Serializable Isolation

Serializable is the strictest level. PostgreSQL acts as if all transactions ran one after another in a sequence even if they are actually running at the same time. If it detects that the concurrent execution could produce a result that no sequential order would produce, it aborts one of the transactions.

-- Session 1
BEGIN ISOLATION LEVEL SERIALIZABLE;

SELECT balance FROM accounts WHERE name = 'Alice';
-- Sees 1000

UPDATE accounts
SET balance = balance - 800
WHERE name = 'Alice';

-- Session 2
BEGIN ISOLATION LEVEL SERIALIZABLE;

SELECT balance FROM accounts WHERE name = 'Alice';
-- Also sees 1000

UPDATE accounts
SET balance = balance - 700
WHERE name = 'Alice';

When both sessions try to commit, PostgreSQL figures out that there is no valid sequential order where both of these transactions can succeed without conflicting. It picks one to commit and forces the other to fail with a serialization error. The losing session has to catch that error and retry the whole transaction from the beginning.

This is the gold standard for correctness but it comes with a cost. Retries add complexity and under very high load they can slow things down.

Summary of Isolation Levels

Read Committed is the default and stops dirty reads. It is good for most situations but can still have issues with lost updates if your application reads a value, thinks about it, and then writes based on that stale value.

Repeatable Read locks your view of the data for the whole transaction. What you read at the start is what you keep seeing. Good for reports or anything where consistency within one transaction matters.

Serializable is the strictest. It guarantees correctness even in the most complex concurrent scenarios but requires your application to handle retries when transactions conflict.

What This Changed for Me

Before this problem I thought transactions were just about wrapping updates together. After this I understand that transactions also have to deal with time, specifically the fact that other transactions are running at the same time and changing the same data.

Isolation levels are how you tell the database how paranoid to be about that. For a payment system the answer is usually very paranoid. Serializable or at minimum Repeatable Read with proper retry logic is what keeps balances correct when thousands of transfers are happening every second.

The CHECK constraint stops impossible balances. Transactions stop partial updates. Isolation levels stop concurrent sessions from stepping on each other. All three working together is what makes a payment system actually trustworthy.

How PostgreSQL Keeps Your Wallet Data Honest — Constraints, Violations, and Consistency

Manoj Kumar — Sun, 29 Mar 2026 13:51:01 +0000

After exploring how transactions work for money transfers, this one goes a level deeper. The question here is not just about what happens when a transaction fails, it is about understanding why it fails and who is responsible for catching the problem. Is it the database stopping you or is it your own code?

This distinction matters more than it sounds.

Quick Recap of the Table

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    balance INT NOT NULL CHECK (balance >= 0),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO accounts (name, balance)
VALUES ('Alice', 1000), ('Bob', 500);

The line that does all the heavy lifting here is CHECK (balance >= 0). That single constraint is PostgreSQL's built in promise that no account will ever hold a negative balance. It does not matter what query you write or who runs it. The database will refuse it.

Test 1 — Directly Setting a Negative Balance

The most straightforward violation. Just try to set Alice's balance to a negative number directly.

UPDATE accounts
SET balance = -100
WHERE name = 'Alice';

PostgreSQL throws an error immediately.

ERROR: new row for relation "accounts" violates check constraint "accounts_balance_check"

Alice's balance stays at 1000. The update never happened. The database caught it before it could touch the data.

Test 2 — Overdrafting Through a Deduction

This is more realistic. Alice has 1000 and we try to deduct 1500 from her.

UPDATE accounts
SET balance = balance - 1500
WHERE name = 'Alice';

PostgreSQL evaluates balance - 1500 which gives negative 500 and immediately checks it against the constraint. It fails. Same error, same result. Alice still has 1000.

The constraint does not care whether you typed a negative number directly or arrived at one through math. If the result is below zero it gets rejected.

Test 3 — Overdraft Inside a Transaction

Now let us put the same bad deduction inside a transaction and see if wrapping it in BEGIN and COMMIT changes anything.

BEGIN;

UPDATE accounts
SET balance = balance - 1500
WHERE name = 'Alice';

UPDATE accounts
SET balance = balance + 1500
WHERE name = 'Bob';

COMMIT;

The first UPDATE fails because of the CHECK constraint. PostgreSQL immediately aborts the transaction. The second UPDATE never even runs. COMMIT does nothing because the transaction is already in a failed state. Everything rolls back.

Alice stays at 1000. Bob stays at 500.

Test 4 — Trying to Force It with a Manual COMMIT After Error

Some people assume that if they just push through and call COMMIT after an error, maybe the second update will save. It does not work that way.

BEGIN;

UPDATE accounts
SET balance = balance - 1500
WHERE name = 'Alice';

-- Error happens here, transaction is now aborted

UPDATE accounts
SET balance = balance + 1500
WHERE name = 'Bob';

COMMIT;

Once PostgreSQL hits an error inside a transaction block the entire session for that transaction is marked as aborted. Every statement after the error returns this:

ERROR: current transaction is aborted, commands ignored until end of transaction block

You cannot sneak anything through after a failure. The only way out is ROLLBACK.

Database Constraint vs Application Logic — The Real Difference

This is the part worth thinking about the most.

The CHECK constraint on balance is enforced by the database itself. It does not matter what application is talking to the database, what language it is written in, or who is running the query. PostgreSQL will always block a negative balance. You cannot bypass it even if you tried.

But there are things the database cannot catch on its own. For example, what if Alice tries to send money to herself? What if someone sends zero rupees? What if the same transfer request comes in twice because of a network retry? These are logic problems that the database schema cannot express as simple constraints. They have to be handled in your application code before the query even runs.

So the right mental model is this. The database is your last line of defence. It catches the things that should never happen under any circumstance. Your application code is the first line of defence. It catches the things that are technically valid SQL but make no sense in your business logic.

Both layers need to exist. Relying only on application logic is dangerous because bugs happen. Relying only on database constraints is not enough because constraints cannot understand your business rules.

What I Took Away

The CHECK constraint feels like a small thing when you first see it but after running these tests it feels essential. It is the kind of protection that works silently in the background and only shows up when something goes wrong, which is exactly when you need it most.

The bigger lesson though is about layers. Real payment systems are not safe because of one clever piece of code. They are safe because multiple layers are each doing their own job. Constraints at the database level, validation at the application level, and transaction blocks tying it all together.

Building a Safe Money Transfer System in PostgreSQL — Transactions, Rollbacks, and What Happens When Things Break

Manoj Kumar — Sun, 29 Mar 2026 13:49:06 +0000

This one hit different from all the previous problems. We are not just querying data anymore. We are building something that handles real money movement, the kind of thing that powers apps like PhonePe and GPay. And the core idea here is simple but critical — either both updates happen or none of them do. No in between.

The Setup

We have an accounts table that stores user balances. The CHECK constraint on balance makes sure no one can go below zero, which is already a safety net built into the table itself.

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    balance INT NOT NULL CHECK (balance >= 0),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO accounts (name, balance)
VALUES ('Alice', 1000), ('Bob', 500);

So Alice starts with 1000 and Bob starts with 500.

The Basic Transfer — The Right Way

The goal is to move money from Alice to Bob inside a single transaction block. A transaction is a group of SQL statements that either all succeed together or all fail together. That is the entire point of wrapping this in BEGIN and COMMIT.

BEGIN;

UPDATE accounts
SET balance = balance - 200,
    last_updated = CURRENT_TIMESTAMP
WHERE name = 'Alice';

UPDATE accounts
SET balance = balance + 200,
    last_updated = CURRENT_TIMESTAMP
WHERE name = 'Bob';

COMMIT;

After this runs Alice has 800 and Bob has 700. Clean transfer, both sides updated, transaction committed.

Now Let's Break It on Purpose

This is where things get interesting. The task was to deliberately introduce failures at different points and see what the database does.

Breaking the Credit Step

Here we deduct from Alice but then write a broken UPDATE that will fail before Bob gets credited.

BEGIN;

UPDATE accounts
SET balance = balance - 200,
    last_updated = CURRENT_TIMESTAMP
WHERE name = 'Alice';

-- This is intentionally broken
UPDATE accountsss
SET balance = balance + 200
WHERE name = 'Bob';

COMMIT;

The second UPDATE references a table that does not exist so PostgreSQL throws an error. The COMMIT never runs. The whole transaction is rolled back automatically.

Alice's balance stays at 1000. Bob's balance stays at 500. Nothing changed.

Manually Rolling Back After a Partial Step

You can also trigger a rollback yourself using ROLLBACK instead of COMMIT.

BEGIN;

UPDATE accounts
SET balance = balance - 200,
    last_updated = CURRENT_TIMESTAMP
WHERE name = 'Alice';

-- Realized something is wrong, rolling back manually
ROLLBACK;

Even though the deduct step ran, ROLLBACK undoes it completely. Alice still has 1000. This is useful when you want to cancel a transaction mid way based on some condition in your application code.

Triggering the CHECK Constraint

The balance column has CHECK (balance >= 0) which means you cannot push anyone below zero. Let us try to overdraft Alice.

BEGIN;

UPDATE accounts
SET balance = balance - 5000,
    last_updated = CURRENT_TIMESTAMP
WHERE name = 'Alice';

UPDATE accounts
SET balance = balance + 5000,
    last_updated = CURRENT_TIMESTAMP
WHERE name = 'Bob';

COMMIT;

The first UPDATE tries to set Alice's balance to negative 4000. The CHECK constraint fires immediately and rejects it. The error causes the whole transaction to roll back. Bob does not get 5000 either. Both accounts stay exactly as they were.

Checking the State Before and After

Before any transfer:

SELECT * FROM accounts;

Alice — 1000, Bob — 500.

After a successful transfer of 200:

Alice — 800, Bob — 700.

After a failed transfer where the credit step was broken:

Alice — 1000, Bob — 500. Exactly the same as before. Nothing leaked out.

That is the whole point. A failed transaction leaves zero trace. The database goes back to the state it was in before BEGIN was called.

What I Actually Learned Here

The thing that stuck with me the most is that partial updates are the scariest thing in a payment system. If Alice loses 200 but Bob never gets it, that money just vanishes. Transactions exist to make sure that can never happen.

PostgreSQL does not just protect you when things go wrong on its own. It also gives you tools like ROLLBACK to protect yourself when your own code detects something is off. Combined with constraints like CHECK, you have multiple layers making sure the data stays consistent no matter what breaks and at what point it breaks.

This is not just a database concept. This is exactly how real payment systems are built at their core.

Sakila SQL Practice — User Roles, Permissions, and Access Control in PostgreSQL

Manoj Kumar — Sun, 29 Mar 2026 13:46:55 +0000

This one was different from all the previous practice problems. No filtering, no sorting, no aliases. This set was all about controlling who can access what in the database. It is called access control or privilege management and it is something that matters a lot in real world databases where you do not want everyone seeing everything.

All of this was done using the DVD Rental database in PostgreSQL.

Task 1 — Create a Login Role That Can Only Read from the Film Table

The first task was to create a user called report_user who can log in but can only read from the film table and nothing else.

CREATE ROLE report_user WITH LOGIN PASSWORD 'password123';
GRANT SELECT ON film TO report_user;

CREATE ROLE sets up the new user. WITH LOGIN means they can actually log into the database. WITH PASSWORD gives them a password to log in with. GRANT SELECT gives them read only access to the film table.

Task 2 — Fix the Permission Denied Error on the Customer Table

When report_user tries to query the customer table they get a permission denied error. That is expected because we only gave them access to film. To fix it you grant them SELECT on customer as well.

GRANT SELECT ON customer TO report_user;

This is actually the point of the exercise. By default a new role cannot access anything. You have to explicitly grant every permission. That is what makes PostgreSQL access control safe by default.

Task 3 — Allow report_user to See Only Certain Columns of the Customer Table

Instead of giving full access to the customer table, this task wanted report_user to only see three specific columns. PostgreSQL lets you grant SELECT on individual columns instead of the whole table.

REVOKE SELECT ON customer FROM report_user;
GRANT SELECT (customer_id, first_name, last_name) ON customer TO report_user;

First I revoked the full table access I gave in the previous task. Then I granted SELECT only on the three columns that are allowed. Now report_user can query customer but they will only ever see customer_id, first_name, and last_name. Any attempt to select email or other columns will be denied.

Task 4 — Create support_user with Specific Permissions

This task was about creating a role with a mix of permissions. support_user should be able to read from customer, update only the email column, but not delete anything.

CREATE ROLE support_user WITH LOGIN PASSWORD 'support123';
GRANT SELECT ON customer TO support_user;
GRANT UPDATE (email) ON customer TO support_user;

No DELETE grant is given at all which means support_user cannot delete rows. Just like with SELECT, you can grant UPDATE on a specific column only, which is exactly what we did here for email.

Task 5 — Remove SELECT Access on Film from report_user

Revoking a permission works the same way as granting, just with REVOKE instead of GRANT.

REVOKE SELECT ON film FROM report_user;

After this report_user can no longer query the film table. Clean and straightforward.

Task 6 — Create a Group Role with SELECT on All Tables

Instead of granting permissions to individual users one by one, you can create a group role and assign permissions to the group. Then you add users to the group and they all inherit the same permissions.

CREATE ROLE readonly_group;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_group;

CREATE ROLE without WITH LOGIN means this is a group role, not a user that can log in. GRANT SELECT ON ALL TABLES IN SCHEMA public gives read access to every table in the public schema at once. Much cleaner than granting table by table.

Task 7 — Create analyst1 and analyst2 and Add Them to the Group

CREATE ROLE analyst1 WITH LOGIN PASSWORD 'analyst1pass';
CREATE ROLE analyst2 WITH LOGIN PASSWORD 'analyst2pass';

GRANT readonly_group TO analyst1;
GRANT readonly_group TO analyst2;

Both users are created with login access. Then GRANT readonly_group TO gives each of them all the permissions that readonly_group has. Now if you ever want to add or remove a permission for all analysts, you just change it on readonly_group and it applies to everyone in the group automatically.

What This Set Taught Me

This was honestly the most interesting set so far. The idea that permissions have to be explicitly granted, that you can control access down to individual columns, and that group roles make managing multiple users so much cleaner, these are things that matter the moment you work on a real database with real users. Security in SQL is not just about writing the right queries, it is also about making sure the right people can only see what they are supposed to see.

Sakila SQL Practice — Filtering, Pattern Matching, and Pagination Queries

Manoj Kumar — Sun, 29 Mar 2026 13:44:10 +0000

This is my second set of SQL practice problems using the Sakila database. This batch is all about filtering rows with WHERE conditions, pattern matching with LIKE, handling NULL values, and controlling how many rows you get back using LIMIT and OFFSET. A solid set of problems that covers a lot of ground you will use in real work.

Simple Filtering with WHERE

Starting with the basics. Get all movies with a rental rate greater than three dollars:

SELECT *
FROM film
WHERE rental_rate > 3;

Now adding a second condition. Movies with a rental rate above three and a replacement cost below twenty:

SELECT *
FROM film
WHERE rental_rate > 3
AND replacement_cost < 20;

Using OR this time. Movies that are either rated PG or have a rental rate of 0.99:

SELECT *
FROM film
WHERE rating = 'PG'
OR rental_rate = 0.99;

The difference between AND and OR is worth keeping in mind. AND means both conditions have to be true. OR means at least one of them has to be true.

Sorting and Limiting Results

Top 10 movies sorted by rental rate from highest to lowest:

SELECT *
FROM film
ORDER BY rental_rate DESC
LIMIT 10;

Skipping Rows with OFFSET

This is where OFFSET comes in. It lets you skip a certain number of rows before starting to fetch. Skip the first 5 movies and get the next 3 sorted by rental rate ascending:

SELECT *
FROM film
ORDER BY rental_rate ASC
LIMIT 3 OFFSET 5;

LIMIT controls how many rows you get and OFFSET controls where you start from. Together they are how pagination works in SQL.

Filtering by a Range with BETWEEN

Movies with a rental duration between 3 and 7 days:

SELECT *
FROM film
WHERE rental_duration BETWEEN 3 AND 7;

BETWEEN is inclusive on both ends so this includes movies with exactly 3 days and exactly 7 days as well.

Pattern Matching with LIKE

Movies where the title starts with A and ends with e:

SELECT *
FROM film
WHERE title LIKE 'A%e';

The percent sign is a wildcard in SQL. It matches any number of characters. So A%e means anything that starts with A, has anything in the middle, and ends with e.

Movies where the title starts with A or B and ends with s:

SELECT *
FROM film
WHERE (title LIKE 'A%s' OR title LIKE 'B%s');

Movies where the title contains the word Man, Men, or Woman:

SELECT *
FROM film
WHERE title LIKE '%Man%'
OR title LIKE '%Men%'
OR title LIKE '%Woman%';

Actors whose last name contains the letters man:

SELECT *
FROM actor
WHERE last_name LIKE '%man%';

Handling NULL Values

Find all customers who do not have an email address listed:

SELECT *
FROM customer
WHERE email IS NULL;

Find all movies where special features are not listed:

SELECT *
FROM film
WHERE special_features IS NULL;

NULL in SQL means the value is missing or unknown. You cannot use equals sign to check for NULL. You have to use IS NULL or IS NOT NULL. That is a mistake a lot of people make early on.

Combining Multiple Conditions

Movies released in 2006 with a rental rate of 2.99 or 3.99 and a title starting with S, top 5 results:

SELECT title, rental_rate, release_year
FROM film
WHERE release_year = 2006
AND rental_rate IN (2.99, 3.99)
AND title LIKE 'S%'
LIMIT 5;

The IN keyword is a cleaner way to write multiple OR conditions on the same column. Instead of writing rental_rate = 2.99 OR rental_rate = 3.99, you just pass both values inside IN and it checks both at once.

Top 10 movies with a rental rate of 2.99 or 4.99, rated R, and title containing the letter L:

SELECT *
FROM film
WHERE rental_rate IN (2.99, 4.99)
AND rating = 'R'
AND title LIKE '%L%'
LIMIT 10;

More Pagination

Display 10 customers after skipping the first 20, sorted alphabetically by last name:

SELECT *
FROM customer
ORDER BY last_name ASC
LIMIT 10 OFFSET 20;

Top 5 movies with the highest replacement cost, skipping the most expensive one:

SELECT *
FROM film
ORDER BY replacement_cost DESC
LIMIT 5 OFFSET 1;

Skipping the first row after sorting by replacement cost descending means you skip the single most expensive film and start from the second one.

Filtering by Date Range

Rentals that happened between May 1 and June 1 of 2005:

SELECT *
FROM rental
WHERE rental_date BETWEEN '2005-05-01' AND '2005-06-01';

Filtering by Duration

Movies where the rental duration is more than 7 days:

SELECT *
FROM film
WHERE rental_duration > 7;

What I Picked Up from This Set

This batch of problems made me a lot more comfortable with a few things. LIKE and the percent wildcard for pattern matching, IS NULL for catching missing values, IN as a cleaner alternative to stacking OR conditions, and OFFSET for skipping rows when you need pagination. These are not fancy features but they come up constantly in real queries and knowing them well makes a big difference.

the Sakila database

Manoj Kumar — Sun, 29 Mar 2026 13:42:21 +0000

After going through the basic problems on HackerRank, I moved on to practicing with the Sakila database. It is a sample DVD rental database that comes with MySQL and it is great for practicing real world style queries. This post covers a bunch of problems I worked through around column aliases, sorting, and pulling unique values. Nothing too heavy, just building good habits with clean queries.

Renaming Columns with Aliases

The first few problems were about making query results more readable using aliases. The AS keyword lets you rename a column in the output without changing anything in the actual table.

Fetching film titles and rental rates with friendlier column names:

SELECT title AS "Movie Title", rental_rate AS "Rate"
FROM film;

Getting customer names with aliased columns:

SELECT first_name AS "First Name", last_name AS "Last Name"
FROM customer;

Aliases are mostly for making results easier to read, especially when you are sharing output with someone who is not looking at the raw table.

Sorting Results with ORDER BY

A big chunk of these problems were about sorting. ORDER BY is how you control the order rows come back in. DESC means highest to lowest or Z to A, and ASC is the default which goes lowest to highest or A to Z.

Films sorted by rental rate from highest to lowest, then alphabetically if the rate is the same:

SELECT title, rental_rate
FROM film
ORDER BY rental_rate DESC, title ASC;

Actor names sorted by last name first, then first name:

SELECT first_name, last_name
FROM actor
ORDER BY last_name ASC, first_name ASC;

Films sorted by length from longest to shortest:

SELECT title, length
FROM film
ORDER BY length DESC;

Actor names sorted by first name in descending order:

SELECT first_name, last_name
FROM actor
ORDER BY first_name DESC;

Film ratings sorted alphabetically with only unique values:

SELECT DISTINCT rating
FROM film
ORDER BY rating ASC;

Films sorted by rating ascending and length descending:

SELECT title, rating, length
FROM film
ORDER BY rating ASC, length DESC;

Actors sorted by last name ascending and first name descending:

SELECT first_name, last_name
FROM actor
ORDER BY last_name ASC, first_name DESC;

Films sorted by replacement cost ascending and rental rate descending:

SELECT title, replacement_cost, rental_rate
FROM film
ORDER BY replacement_cost ASC, rental_rate DESC;

Customer names sorted by last name ascending and first name descending:

SELECT first_name, last_name
FROM customer
ORDER BY last_name ASC, first_name DESC;

Rentals sorted by customer ID ascending and rental date descending:

SELECT *
FROM rental
ORDER BY customer_id ASC, rental_date DESC;

Films sorted by rental duration ascending and title descending:

SELECT title, rental_duration
FROM film
ORDER BY rental_duration ASC, title DESC;

When you have two ORDER BY columns, the second one only kicks in when two rows have the same value in the first column. That is the thing worth remembering here.

Pulling Unique Values with DISTINCT

These problems were about using DISTINCT to remove duplicate values from results.

Unique replacement costs from the film table:

SELECT DISTINCT replacement_cost
FROM film;

Unique rental durations:

SELECT DISTINCT rental_duration
FROM film;

Unique store IDs from inventory:

SELECT DISTINCT store_id
FROM inventory;

Unique replacement costs sorted in ascending order:

SELECT DISTINCT replacement_cost
FROM film
ORDER BY replacement_cost ASC;

Using Aliases with More Columns

Aliasing the length column to something more readable:

SELECT title, length AS "Duration (min)"
FROM film;

Customer names with their active status aliased:

SELECT first_name, last_name, active AS "Is Active"
FROM customer;

Film Categories Sorted Alphabetically

SELECT name
FROM category
ORDER BY name ASC;

Using LIMIT to Get Top or Bottom Results

The 10 shortest films:

SELECT title, length
FROM film
ORDER BY length ASC
LIMIT 10;

Top 5 customers with the highest customer ID:

SELECT first_name, last_name
FROM customer
ORDER BY customer_id DESC
LIMIT 5;

Grouping and Ordering Together

First unique customer ID based on active status:

SELECT DISTINCT customer_id, active
FROM customer
ORDER BY customer_id ASC;

Earliest rental date for each customer:

SELECT customer_id, MIN(rental_date) AS rental_date
FROM rental
GROUP BY customer_id
ORDER BY customer_id ASC;

First rental date for each store:

SELECT store_id, MIN(rental_date) AS rental_date
FROM rental
GROUP BY store_id
ORDER BY store_id ASC;

What I Took Away from All This

These problems look simple on the surface but they cover a lot of ground. Aliases keep your output clean and readable. ORDER BY with multiple columns gives you fine control over how results are sorted. DISTINCT is your go to when you only care about unique values. And LIMIT is great when you only need the top or bottom few rows.

Sakila is a really solid database to practice on because the tables actually relate to something real, which makes it easier to think through what your query should be doing instead of just memorizing syntax.

HackerRank SQL — Querying Every Row and Column from the CITY Table

Manoj Kumar — Sun, 29 Mar 2026 13:38:12 +0000

This is probably the most basic SQL query you will ever write. No filters, no conditions, no functions. Just get everything from the table and that is it.

Here is what I wrote:

SELECT *
FROM CITY;

That is the whole thing. SELECT star means give me every column and FROM CITY tells it which table to pull from. Since there is no WHERE clause the query just returns every single row in the table without skipping anything.

I know it looks almost too simple but this is actually the query most people write first when they open a new table and want to see what is in it. It is like a first look at the data before you start filtering or doing anything fancy with it.

If you are just starting out with SQL, get comfortable with this one. Everything else you learn from here builds on top of this exact structure.

HackerRank SQL — Finding the Difference Between Total and Distinct City Counts

Manoj Kumar — Sun, 29 Mar 2026 13:36:50 +0000

This one was actually pretty fun to think about. The problem wanted me to find the difference between the total number of city entries and the number of unique city entries. Basically how many duplicates are hiding in the table.

The idea is simple. COUNT gives you the total rows and COUNT with DISTINCT gives you only the unique ones. Subtract one from the other and you have your answer.

Here is what I wrote:

SELECT COUNT(CITY) - COUNT(DISTINCT CITY)
FROM STATION;

COUNT(CITY) counts every single row in the CITY column including duplicates. COUNT(DISTINCT CITY) counts only the unique city names. When you subtract the second from the first you get the number of city names that are repeated in the table.

The interesting thing here is that both counts are happening inside a single SELECT. You do not need two separate queries or anything complicated. SQL lets you do the math right there in the SELECT line itself.

This was the first problem where I had to think about aggregation a little. COUNT is one of those functions you will use constantly in SQL so getting comfortable with it early on is really worth it.