DEV Community

miku86
miku86

Posted on

PostgreSQL: How To Update & Delete Data

#beginners #tutorial #postgres

Intro

So we learned how to create simple queries to read & write some data.

We also learned how to filter & order our data.

Now we want to learn how to update & delete data.

Connect to the Database Shell

First, I connect to my created database shell:

psql -d miku86-db
Enter fullscreen mode Exit fullscreen mode

Read: Show All Data In person-table

Then, I check my current table:

miku86-db=# SELECT * FROM person;
 id | nickname | country | available 
---------+----------+---------+-----------
  1 | miku86   | germany | t
  2 | heidi    | austria | t
  3 | tom      | usa     | f
  4 | pavel    | russia  | f
(4 rows)
Enter fullscreen mode Exit fullscreen mode

I SELECT all (* = wildcard) rows FROM the person-table.
There are 4 rows in it.

Delete A Row

Simplified Syntax:

DELETE FROM [table] WHERE [expression];
Enter fullscreen mode Exit fullscreen mode

Example #1: Delete the person(s) with the id of 1: 

miku86-db=# DELETE FROM person WHERE id = 1;
DELETE 1
Enter fullscreen mode Exit fullscreen mode

You can see DELETE 1 below the DELETE-query.
The command executed successfully, 1 row was deleted.

miku86-db=# SELECT * FROM person;
 id | nickname | country | available 
---------+----------+---------+-----------
  2 | heidi    | austria | t
  3 | tom      | usa     | f
  4 | pavel    | russia  | f
(3 rows)
Enter fullscreen mode Exit fullscreen mode

The person with the id of 1 was deleted.

Example #2: Delete the person(s) that are not available (=> available = false): 

miku86-db=# DELETE FROM person WHERE available = false;
DELETE 2
Enter fullscreen mode Exit fullscreen mode

You can see DELETE 2 below the DELETE-query.
The command executed successfully, 2 rows were deleted.

miku86-db=# SELECT * FROM person;
 id | nickname | country | available 
---------+----------+---------+-----------
  2 | heidi    | austria | t
(1 row)
Enter fullscreen mode Exit fullscreen mode

The person with the id of 1 was deleted in example 1.
The persons with available of f (= false) were deleted in this example.

Update A Row

Simplified Syntax:

UPDATE [table] SET [col] = [newvalue] WHERE [expression];
Enter fullscreen mode Exit fullscreen mode

Example #1: Update the person(s) with country of austria to country of germany: 

miku86-db=# UPDATE person SET country = 'germany' WHERE country = 'austria';
UPDATE 1
Enter fullscreen mode Exit fullscreen mode

You can see UPDATE 1 below the UPDATE-query.
The command executed successfully, 1 row was updated.

miku86-db=# SELECT * FROM person;
 id | nickname | country | available 
---------+----------+---------+-----------
  2 | heidi    | germany | t
(1 row)
Enter fullscreen mode Exit fullscreen mode

The person with the country of austria was updated to the country of germany.

Next Part

We will learn how to connect two tables.

Further Reading

PostgreSQL Docs
SQL Syntax
Delete Docs
Update Docs

Top comments (0)