Modifying Postgres Data

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In this tutorial we’ll be discussing modifying Postgres data. We’ll discuss the operations available and show some examples of how to use them.

Prerequisite

Ensure to install and configure the following for your OS:

Basic PostgreSQL is required to be able to follow along with the tutorial.

Modifying Postgres Data

In this article we will be showing you how to modify Postgres data via DML or also known as ‘Data Manipulation Language’. This is an ANSI standard that defines the following statements:

  • INSERT – This statement is used for adding new records into the table.
  • UPDATE – This statement is responsible for updating records that exists in the table.
  • DELETE – This statement is used for removing records from the table.
  • SELECT – This is used for retrieving and displaying or records from the table.

Creating Sample Table

We are now going to create a sample database and a table that we will be using for our demo purposes.

  • We create a database using the below command.
1
create database persondb;
  • Create tables for the database with the following structure
1
2
3
4
5
6
CREATE TABLE IF NOT EXISTS person (
    id SERIAL PRIMARY KEY,
    name CHARACTER VARYING(100),
    lastname CHARACTER VARYING(100),
    age INTEGER
);

Then we insert our sample database.

1
2
3
INSERT INTO person (id,name, lastname, age)
VALUES
   (1,'John','Burton',45);

Our person table now looks like this:

1
2
3
4
 id | name | lastname | age
----+------+----------+-----
  1 | John | Burton   |  45
(1 row)

INSERT Statement Example

In this section we will showing you the first DML type, the INSERT statement. Below is the basic syntax for the INSERT statement.

1
INSERT INTO TABLE_NAME [(column1 [, column2...])] VALUES (value1 [, value2...]);

Lets discuss the above syntax.

  • First, we call the INSERT INTO clause followed by the ‘table_name’ which is the table name.
  • Second, we specify the name of the column or fields that we will be inserting the records into.
  • Finally, we specify the values accordingly against the specified field.
1
2
3
INSERT INTO person (id,name, lastname, age)
VALUES
   (2,'Joseph','Clayton',22);

The above query will insert new record to our table, and after executing the query, our table should look something like the following.

1
2
3
4
5
 id |  name  | lastname | age
----+--------+----------+-----
  1 | John   | Burton   |  45
  2 | Joseph | Clayton  |  22
(2 rows)

UPDATE Statement Example

In this section we will showing you the second DML type, the UPDATE statement. Below is the basic syntax for the UPDATE statement.

1
UPDATE TABLE_NAME SET column0 = value0, column1 = value1, ... WHERE condition;

Let’s discuss the above code further.

  • First, we call the UPDATE clause followed by the name we want to update.
  • Second, we use the SET clause to change/modify/set the specified fields with corresponding values.
  • Finally, the WHERE clause is optional to further refine/limit to the update to specific rows only, omitting this will update all the rows in the specified table.
1
UPDATE person SET name = 'Greg', lastname = 'Lucas' WHERE id=1;

The above syntax will change the values to the row with a column id equal to one (1). After executing the query Postgres will prompt us with a confirmation text like this: UPDATE 1. The result should look something like the following.

1
2
3
4
5
 id |  name  | lastname | age
----+--------+----------+-----
  2 | Joseph | Clayton  |  22
  1 | Greg   | Lucas    |  45
(2 rows)

DELETE Statement Example

In this section we will be performing the DELETE statement, the syntax for this clause is as follows.

1
DELETE FROM TABLE_NAME WHERE condition;

The above syntax will do the the following.

  • First, it calls the DELETE FROM clause followed by the name of the target table.
  • Finally, the WHERE clause is optional but very play important role in the query as it will limit the row affected by the delete function, by removing this clause it will delete all the rows in the table.
1
DELETE FROM person WHERE id=2;

The above query will delete the row with a column of id that have a value of two (2). After executing the query, Postgres notifies us with the following: DELETE 1.

Our table should look something like this.

1
2
3
4
 id | name | lastname | age
----+------+----------+-----
  1 | Greg | Lucas    |  45
(1 row)

SELECT Statement Example

In this section we will be showing you the most basic among the type is the SELECT statement. Below is the syntax of SELECT statement.

1
2
3
SELECT list_of_column
FROM TABLE_NAME
WHERE condition;
  • First, we call the SELECT statement and specify the target column or name of the field we want to retrieve records from.
  • Then we specify the table name.
  • The WHERE condition further refines on how and what manner the records will be displayed.

Let’s consider the following table.

1
2
3
4
5
6
 id |  name  | lastname | age
----+--------+----------+-----
  1 | Don    | Wallace  |  20
  2 | Joseph | Clayton  |  22
  3 | Jason  | Flemming |  21
(3 rows)

We will retrieving a record with an id equal to 3.

1
SELECT id, name, lastname FROM person WHERE id = 3;

Below is the output:

1
2
3
4
 id | name  | lastname
----+-------+----------
  3 | Jason | Flemming
(1 row)

Conclusion

In this tutorial we talked about modifying Postgres data, the operations available, and how to execute them. We hope you found this useful and are able to apply what you’ve learned to solve your database problem.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.