Basic Data Operations in PostgreSQL

Introduction

In this lab, we will explore basic data operations in PostgreSQL. The primary goal is to learn how to manipulate data within a PostgreSQL database using fundamental SQL commands.

We'll begin by connecting to the database and creating an employees table with columns for ID, name, and department. Then, we'll learn how to insert data into the table using the INSERT INTO statement. Following that, we will cover querying data with SELECT, updating existing records, and deleting records. This lab provides a hands-on introduction to essential data management techniques in PostgreSQL.

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL postgresql(("PostgreSQL")) -.-> postgresql/PostgreSQLGroup(["PostgreSQL"]) postgresql/PostgreSQLGroup -.-> postgresql/table_init("Create Basic Table") postgresql/PostgreSQLGroup -.-> postgresql/row_add("Insert One Row") postgresql/PostgreSQLGroup -.-> postgresql/data_all("Select All Data") postgresql/PostgreSQLGroup -.-> postgresql/data_where("Filter With WHERE") postgresql/PostgreSQLGroup -.-> postgresql/data_sort("Sort Query Results") postgresql/PostgreSQLGroup -.-> postgresql/row_edit("Update Single Row") postgresql/PostgreSQLGroup -.-> postgresql/row_drop("Delete One Row") subgraph Lab Skills postgresql/table_init -.-> lab-550897{{"Basic Data Operations in PostgreSQL"}} postgresql/row_add -.-> lab-550897{{"Basic Data Operations in PostgreSQL"}} postgresql/data_all -.-> lab-550897{{"Basic Data Operations in PostgreSQL"}} postgresql/data_where -.-> lab-550897{{"Basic Data Operations in PostgreSQL"}} postgresql/data_sort -.-> lab-550897{{"Basic Data Operations in PostgreSQL"}} postgresql/row_edit -.-> lab-550897{{"Basic Data Operations in PostgreSQL"}} postgresql/row_drop -.-> lab-550897{{"Basic Data Operations in PostgreSQL"}} end

Insert Data into Tables

In this step, we will learn how to insert data into tables in PostgreSQL. Inserting data is a fundamental operation for populating your database with information. We'll cover the basic syntax and provide examples to help you understand the process.

Before we begin, ensure you have PostgreSQL installed and running. You can connect to your PostgreSQL server using the psql command-line tool.

First, let's connect to the database. Open your terminal and execute the following command:

sudo -u postgres psql

Now that we're connected to the database, let's create a simple table named employees if it doesn't already exist. This table will store information about employees, such as their ID, name, and department.

CREATE TABLE IF NOT EXISTS employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50)
);

This SQL statement creates a table named employees with three columns:

id: An integer that automatically increments with each new record (using SERIAL). It's also the primary key for the table.
name: A string (up to 50 characters) to store the employee's name.
department: A string (up to 50 characters) to store the employee's department.

Now, let's insert some data into the employees table. We'll insert three employee records.

INSERT INTO employees (name, department) VALUES ('Alice Smith', 'Sales');
INSERT INTO employees (name, department) VALUES ('Bob Johnson', 'Marketing');
INSERT INTO employees (name, department) VALUES ('Charlie Brown', 'Engineering');

Each INSERT INTO statement adds a new row to the employees table. We specify the table name (employees) and the columns we're inserting data into (name, department). The VALUES keyword is followed by the actual data we want to insert, enclosed in parentheses.

To verify that the data has been inserted correctly, we can query the employees table using the SELECT statement.

SELECT * FROM employees;

This will display all the rows and columns in the employees table. You should see the three employee records we just inserted. The output should look similar to this:

 id |     name      | department
----+---------------+------------
  1 | Alice Smith   | Sales
  2 | Bob Johnson   | Marketing
  3 | Charlie Brown | Engineering
(3 rows)

You have now successfully inserted data into a PostgreSQL table. You can continue to insert more records as needed.

Finally, exit the psql shell by typing:

\q

This will return you to the terminal.

Query Data with SELECT

In this step, we will learn how to query data from tables in PostgreSQL using the SELECT statement. The SELECT statement is the foundation for retrieving information from your database. We'll cover basic syntax, filtering, and ordering to help you master data retrieval.

First, ensure you are connected to the database. If you're not already connected, open your terminal and execute the following command:

sudo -u postgres psql

Let's learn how to select specific columns. Suppose we only want to retrieve the name and department columns.

SELECT name, department FROM employees;

This statement will return only the name and department columns for all rows in the employees table. The output should look similar to this:

     name      | department
---------------+------------
 Alice Smith   | Sales
 Bob Johnson   | Marketing
 Charlie Brown | Engineering
(3 rows)

Now, let's add a WHERE clause to filter the results. Suppose we want to retrieve only the employees who work in the 'Sales' department.

SELECT name, department FROM employees WHERE department = 'Sales';

The WHERE clause specifies a condition that must be met for a row to be included in the result set. In this case, we're only selecting rows where the department column is equal to 'Sales'. The output should be:

     name      | department
---------------+------------
 Alice Smith   | Sales
(1 row)

Finally, let's learn how to order the results using the ORDER BY clause. Suppose we want to retrieve all employees, ordered by their name in ascending order.

SELECT name, department FROM employees ORDER BY name;

This statement will return all rows from the employees table, but the results will be sorted alphabetically by the name column. The output should be:

     name      | department
---------------+------------
 Alice Smith   | Sales
 Bob Johnson   | Marketing
 Charlie Brown | Engineering
(3 rows)

To order the results in descending order, you can add the DESC keyword after the column name.

SELECT name, department FROM employees ORDER BY name DESC;

This will sort the results in reverse alphabetical order.

You have now learned how to query data from a PostgreSQL table using the SELECT statement, including how to select specific columns, filter results using the WHERE clause, and order results using the ORDER BY clause.

Illustration for querying data in PostgreSQL

Finally, exit the psql shell by typing:

\q

Update Existing Records

In this step, we will learn how to update existing records in a PostgreSQL table using the UPDATE statement. Updating data is essential for keeping your database information current and accurate. We'll cover the basic syntax and provide examples to help you understand the process.

First, ensure you are connected to the database. If you're not already connected, open your terminal and execute the following command:

sudo -u postgres psql

Let's update the department of 'Alice Smith' to 'Human Resources'.

UPDATE employees SET department = 'Human Resources' WHERE name = 'Alice Smith';

The UPDATE statement modifies existing rows in a table. The SET clause specifies which columns to update and their new values. The WHERE clause specifies which rows to update. In this case, we're updating the department column to 'Human Resources' for the row where the name column is equal to 'Alice Smith'.

To verify that the update was successful, we can query the employees table.

SELECT * FROM employees WHERE name = 'Alice Smith';

This will display the row for 'Alice Smith', and you should see that the department column has been updated to 'Human Resources'. The output should be similar to this:

 id |     name      |   department
----+---------------+------------------
  1 | Alice Smith   | Human Resources
(1 row)

You can also update multiple columns at once. For example, let's update Bob Johnson's department to 'Sales' and his name to 'Robert Johnson'.

UPDATE employees SET department = 'Sales', name = 'Robert Johnson' WHERE name = 'Bob Johnson';

This statement updates both the department and name columns for the row where the name column is equal to 'Bob Johnson'.

To verify the update, query the employees table again.

SELECT * FROM employees WHERE name = 'Robert Johnson';

The output should be:

 id |      name      | department
----+----------------+------------
  2 | Robert Johnson | Sales
(1 row)

It's important to be careful when using the UPDATE statement, especially when omitting the WHERE clause. If you omit the WHERE clause, the UPDATE statement will update all rows in the table. For example:

UPDATE employees SET department = 'Unknown';

This statement would set the department column to 'Unknown' for every row in the employees table. Be very cautious when executing such a statement.

Illustration for updating records in PostgreSQL

You have now learned how to update existing records in a PostgreSQL table using the UPDATE statement.

Finally, exit the psql shell by typing:

\q

Delete Records

In this step, we will learn how to delete records from a PostgreSQL table using the DELETE statement. Deleting data is an important operation for removing obsolete or incorrect information from your database. We'll cover the basic syntax and provide examples to help you understand the process.

First, ensure you are connected to the database. If you're not already connected, open your terminal and execute the following command:

sudo -u postgres psql

Let's delete the record for 'Charlie Brown' from the employees table.

DELETE FROM employees WHERE name = 'Charlie Brown';

The DELETE FROM statement removes rows from a table. The WHERE clause specifies which rows to delete. In this case, we're deleting the row where the name column is equal to 'Charlie Brown'.

To verify that the deletion was successful, we can query the employees table.

SELECT * FROM employees WHERE name = 'Charlie Brown';

This should return no rows, indicating that the record for 'Charlie Brown' has been deleted. The output should be:

 id | name | department
----+------+------------
(0 rows)

You can also delete multiple rows at once by using a more complex WHERE clause. For example, if you had multiple employees in the 'Marketing' department, you could delete all of them with a single statement:

DELETE FROM employees WHERE department = 'Marketing';

It's crucial to be extremely careful when using the DELETE statement, especially when omitting the WHERE clause. If you omit the WHERE clause, the DELETE statement will delete all rows from the table. For example:

DELETE FROM employees;

This statement would delete every row in the employees table, effectively emptying the table. Be absolutely certain you want to delete all rows before executing such a statement.

You have now learned how to delete records from a PostgreSQL table using the DELETE statement.

Illustration for record deletion in PostgreSQL

Check the employees table to verify that the deletion was successful.

SELECT * FROM employees;

The output should be:

 id | name | department
----+------+------------
(0 rows)

Finally, exit the psql shell by typing:

\q

Summary

In this lab, we covered fundamental data operations in PostgreSQL, focusing on inserting data into tables. We began by connecting to the database using the psql command-line tool. Then, we created an employees table with columns for id, name, and department, specifying id as the primary key with auto-incrementing functionality.

Finally, we inserted three employee records into the employees table using the INSERT INTO statement, specifying the table name and the columns to insert data into, along with the corresponding values for each record. This demonstrated the basic syntax for populating a PostgreSQL table with data.