CRUD Operations in PostgreSQL

In this post, I’ll walk you through each CRUD operation with simple PostgreSQL examples and show you exactly what results to expect.

What is CRUD?

CRUD stands for:

• Create: Insert new records into a table
• Read: Retrieve existing records from a table
• Update: Modify existing records in a table
• Delete: Remove records from a table

Let’s dive into practical examples using a simple users table.

Setting Up Our Example Table

First, let’s create a sample table to work with:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    age INTEGER
);

1. CREATE (INSERT) Operations

Creating records means inserting new data into our table.

Insert a Single Record

INSERT INTO users (name, email, age) 
VALUES ('John Doe', 'john@example.com', 30);

Output:

INSERT 0 1

This means 1 row was inserted successfully.

Insert Multiple Records

INSERT INTO users (name, email, age) 
VALUES 
    ('Jane Smith', 'jane@example.com', 25),
    ('Bob Johnson', 'bob@example.com', 35),
    ('Alice Brown', 'alice@example.com', 28);

Output:

INSERT 0 3

Three rows were inserted.

View Our Data

Let’s check what we’ve created so far:

SELECT * FROM users;

Output:

 id |     name     |       email       | age 
----+--------------+-------------------+-----
  1 | John Doe     | john@example.com  |  30
  2 | Jane Smith   | jane@example.com  |  25
  3 | Bob Johnson  | bob@example.com   |  35
  4 | Alice Brown  | alice@example.com |  28
(4 rows)

2. READ (SELECT) Operations

Reading data means retrieving existing records from our table.

Read All Records

SELECT * FROM users;

Output:

 id |     name     |       email       | age 
----+--------------+-------------------+-----
  1 | John Doe     | john@example.com  |  30
  2 | Jane Smith   | jane@example.com  |  25
  3 | Bob Johnson  | bob@example.com   |  35
  4 | Alice Brown  | alice@example.com |  28
(4 rows)

Read Specific Columns

SELECT name, email FROM users;

Output:

     name     |       email       
--------------+-------------------
 John Doe     | john@example.com
 Jane Smith   | jane@example.com
 Bob Johnson  | bob@example.com
 Alice Brown  | alice@example.com
(4 rows)

Read with Conditions

SELECT * FROM users WHERE age > 30;

Output:

 id |     name    |      email      | age 
----+-------------+-----------------+-----
  3 | Bob Johnson | bob@example.com |  35
(1 row)

Read with Sorting

SELECT * FROM users ORDER BY age ASC;

Output:

 id |     name     |       email       | age 
----+--------------+-------------------+-----
  2 | Jane Smith   | jane@example.com  |  25
  4 | Alice Brown  | alice@example.com |  28
  1 | John Doe     | john@example.com  |  30
  3 | Bob Johnson  | bob@example.com   |  35
(4 rows)

3. UPDATE Operations

Updating means modifying existing records in our table.

Update a Single Record

Let’s update John Doe’s age:

UPDATE users 
SET age = 31 
WHERE name = 'John Doe';

Output:

UPDATE 1

One row was updated.

Let’s verify the change:

SELECT * FROM users WHERE name = 'John Doe';

Output:

 id |   name   |       email       | age 
----+----------+-------------------+-----
  1 | John Doe | john@example.com  |  31
(1 row)

Update Multiple Records

Let’s add 1 year to everyone’s age:

UPDATE users 
SET age = age + 1;

Output:

UPDATE 4

All four rows were updated.

Let’s check the updated data:

SELECT * FROM users;

Output:

 id |     name     |       email       | age 
----+--------------+-------------------+-----
  1 | John Doe     | john@example.com  |  32
  2 | Jane Smith   | jane@example.com  |  26
  3 | Bob Johnson  | bob@example.com   |  36
  4 | Alice Brown  | alice@example.com |  29
(4 rows)

4. DELETE Operations

Deleting means removing records from our table.

Delete Specific Records

Let’s remove Alice Brown from our table:

DELETE FROM users 
WHERE name = 'Alice Brown';

Output:

DELETE 1

One row was deleted.

Let’s verify:

SELECT * FROM users;

Output:

 id |     name     |       email       | age 
----+--------------+-------------------+-----
  1 | John Doe     | john@example.com  |  32
  2 | Jane Smith   | jane@example.com  |  26
  3 | Bob Johnson  | bob@example.com   |  36
(3 rows)

Delete with Conditions

Let’s remove all users older than 30:

DELETE FROM users 
WHERE age > 30;

Output:

DELETE 2

Two rows were deleted.

Let’s check what remains:

SELECT * FROM users;

Output:

 id |    name     |       email       | age 
----+-------------+-------------------+-----
  2 | Jane Smith  | jane@example.com  |  26
(1 row)

Conclusion

CRUD operations form the backbone of database interactions in applications. With these basic PostgreSQL commands, you can perform any data manipulation task:

1. INSERT to create new records
2. SELECT to read existing records
3. UPDATE to modify existing records
4. DELETE to remove records

Practice these operations with your own tables, and you’ll quickly become proficient with PostgreSQL database management. Remember to always use WHERE clauses with UPDATE and DELETE to avoid unintentionally modifying or removing all your data!