DEV Community

Cover image for Play with psql part 2 - INSERT, UPDATE and DELETE Data (for Beginners)
koshirok096
koshirok096

Posted on

Play with psql part 2 - INSERT, UPDATE and DELETE Data (for Beginners)

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.

Image description


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
Enter fullscreen mode Exit fullscreen mode

And run below code to connect to "Apple Records"

\c “Apple Records”
#C means connect
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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');
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode
 id |   name    | height | weight |  instrument   |    birth_place     | active_years 
----+-----------+--------+--------+---------------+--------------------+--------------
  5 | Pete Best |    175 |     68 | Vocals, Drums | Liverpool, England | 1960-1962
(1 row)
Enter fullscreen mode Exit fullscreen mode

Image description

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!
Enter fullscreen mode Exit fullscreen mode

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...
Enter fullscreen mode Exit fullscreen mode

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

SELECT * FROM beatles;
Enter fullscreen mode Exit fullscreen mode
 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
Enter fullscreen mode Exit fullscreen mode

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

Image description

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!

Top comments (0)