Play with psql part 2 - INSERT, UPDATE and DELETE Data (for Beginners)
Tags: postgres, beginners, database
Published: 2023-09-16 23:48:18


#INTRO

Hello guys, today I will write about psql again!

So far, I have written articles about [setting up psql](https://dev.to/koshirok096/how-to-start-using-psql-in-terminal-for-beginners-2ok3), and [creating tables, inserting data](https://dev.to/koshirok096/play-with-psql-create-table-and-insert-data-for-beginners-27oe).

This article is a continuation of the previous articles. At this time, I'll write about how to **add**, **update**, and **delete** elements in a table, that was created in the previous articles.

As same as previous articles this will be basic content for beginners, but if you are interested, please read on!

#INSERT : Add new member to existing table

_In the previous article, I used the superuser "postgres" to create a database named "Apple Records" and a table named "beatles." If you want to use a different user account or database, please make adjustments as needed._

_This instruction is based on the flow in my environment. While following the further steps as outlined, please be aware that there might be differences in errors or behaviors in your environment compared to mine._

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/akpes781elrwtvskjefj.png)

---

Last time we created database called "**Apple Records**" and it has a table "**beatles**" in the Postgres user.

Let's login to account and check it. Let's run this code below.

```shell
sudo -u postgres psql
# Login
# I use super user account postgres
```
And run below code to connect to "Apple Records"

```shell
\c “Apple Records”
#C means connect
```

Now you get a message _You are now connected to database "Apple Records" as user yourusername._

And at the beginning of the Terminal line, you should see the words "**Apple Records=#**" and be able to enter commands beyond that.

Then run this code:

```shell
SELECT * FROM beatles;
```

Then, you can see the table that you created last time!

```shell
 id |      name       | height | weight |   instrument   |    birth_place     | active_years 
----+-----------------+--------+--------+----------------+--------------------+--------------
  1 | John Lennon     |    180 |     70 | Vocals, Guitar | Liverpool, England | 1960-1970
  2 | Paul McCartney  |    175 |     68 | Vocals, Bass   | Liverpool, England | 1960-1970
  3 | George Harrison |    178 |     68 | Vocals, Guitar | Liverpool, England | 1960-1970
  4 | Ringo Starr     |    173 |     65 | Vocals, Drums  | Liverpool, England | 1962-1970
```

---

From here, let's add a new member using the `INSERT` command.

```shell
INSERT INTO beatles (name, height, weight, instrument, birth_place, active_years)
VALUES ('Pete Best', 175, 68, 'Vocals, Drums', 'Liverpool, England', '1960-1962');
```
This query inserts new rows into the "beatles" table, specifying information about "Pete Best" in each column.

Let's check if this `INSERT` was successfully done.

```shell
SELECT * FROM beatles WHERE name = 'Pete Best';
#Or you can check all members by: SELECT * FROM beatles;
```

```shell
 id |   name    | height | weight |  instrument   |    birth_place     | active_years 
----+-----------+--------+--------+---------------+--------------------+--------------
  5 | Pete Best |    175 |     68 | Vocals, Drums | Liverpool, England | 1960-1962
(1 row)
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8s0cyup3i5858mtlhc80.png)

#UPDATE : Change member's infomation
Next, let's update some information of member!

```shell
UPDATE beatles
SET height = 180, weight = 70
WHERE name = 'Pete Best';
#Oh, he's taller and bigger now!
```

Now, this new member "Pete Best" has updated.

As same as we did on previous chapter, we can check by using `SELECT` method. And of course, you can use `SELECT` method to different member to change some information on it, as needed. If you want to play with, let's try.

#DELETE : Delete member
Finally, here is a how to delete the member.

```shell
DELETE FROM beatles WHERE name = 'Pete Best';
#there just was something...
```

Again, as same as we did on previous chapters, let's check the table at last.

```shell
SELECT * FROM beatles;
```

```shell
 id |      name       | height | weight |   instrument   |    birth_place     | active_years 
----+-----------------+--------+--------+----------------+--------------------+--------------
  1 | John Lennon     |    180 |     70 | Vocals, Guitar | Liverpool, England | 1960-1970
  2 | Paul McCartney  |    175 |     68 | Vocals, Bass   | Liverpool, England | 1960-1970
  3 | George Harrison |    178 |     68 | Vocals, Guitar | Liverpool, England | 1960-1970
  4 | Ringo Starr     |    173 |     65 | Vocals, Drums  | Liverpool, England | 1962-1970
```

(Sorry to Pete, but) the table is back in order now!


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yym3msk7la3y4eaa83ku.png)



#OUTRO
As same as previous articles, this article is also somewhat foundational, but I hope it can be helpful to someone who's learning.

Personally, I don't have many opportunities to use psql on CLI in my work or projects, but it's enjoyable! Especially when tables are displayed in the Terminal, it gets me excited (am I the only one?).

Thank you for reading, happy coding!