INTRO

Hello guys, today I will write about psql again!

So far, I have written articles about setting up psql, and creating tables, inserting data.

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.

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.

sudo -u postgres psql
# Login
# I use super user account postgres

And run below code to connect to "Apple Records"

\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:

SELECT * FROM beatles;

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

 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.

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.

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

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

UPDATE : Change member's infomation

Next, let's update some information of member!

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.

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.

SELECT * FROM beatles;

 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!

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!