DennisVN

Posted on Nov 27, 2021

PostgreSQL terminal: a CRUD beginner guide (Windows 10).

#webdev #crud #postgres #database

For all my beginners out there, i began using PostgreSQL this morning. This article will help you with initiating and editing in the SQL shell. This article will not cover installation, and won't cover the GUI. This means that i expect you to have completed installation, and allready set a password throught the GUI.
During my webdev bootcamp we learned working with MySQL and MAMP, but i find them very different, so i hope this article helps you to save some time at researching. Let's get started !

Getting inside SQL through the terminal

i still find working through terminal quite tedious as i always get classic errors like "wrong host", "wrong password", "access denied" and all them classic shenanigans. It's pretty straightforward in this case :

Start > PostgreSQL 14 > SQL shell (psql)

the terminal opens and this is the one line you see :

Server [localhost]:

And here is where my confusion started, but basically, all you have to do is leave it blank & press enter. Same for the following fields that pop up :

Server [localhost]: (leave empty, press the ENTER key)
Database [postgres]: (leave empty,press the ENTER key)
Port [5432]: (leave empty,press the ENTER key)
Username [postgres]: (leave empty,press the ENTER key)

Now hold on, because the next one is PASSWORD. Here you have to insert the password that you set with the installation. Once you passed that, you are in ! well done.

Let's go over the commands to get you started. Here's your first one :

help
this will give you :

\copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

--> press q to quit list 


\l to see an overview of all databases 
(offcourse it is empty for now)

So let us create a new database called 'test' :

The command for this is :

CREATE DATABASE test;

Please not the importance of putting the semicolon at the end. Later on we will see that the terminal only executes after a semicolon. If you press enter without ending with a semicolon, the terminal expects you to continue typing.
Let's check if worked with :

\l
to check our list again, you will see it has been added !

Congratulations, we just opened the terminal, did our first basic commands, and we allready created a brand new list without touching the GUI.

But how to check out a specific database ?

close your terminal, and let's start from scratch.

When you open the Shell again (pro-tip: put a shortcut on your desktop to save time) and repeat previous steps. The only difference here will be that we going to tell postgres that we want to go to our newly created 'test' database. So don't go to fast, database is the second prompt you'll get ;).

Server [localhost]:
Database [postgres]:test 
Port [5432]:
Username [postgres]:
Password for user postgres:(enter the one you created).

you will get a warning message, just ignore it.
Next up use the following command to see what's in our 'test' database :
\d
If you are good at guessing, you can allready tell it's going to be empy. If not, this is what will appear in the terminal :

Did not find any relations.

And this is where the fun begins !

Manipulating our database through the terminal

In this part we go over basic CRUD commands to add, read, update and delete the information in our 'test' database.

ADD & READ NEW TABLE :
In our 'test' db, we will create a table called person. That person wil have following properties :

id ( number (int), a primary key, autoincrementing.)
name ( letters (characters), 100 max )
country ( letters (characters), 50 max )

We will have to define the type of data the properties get. Our id wil be an integer for example, but with an autoincrement function. This so our id will be automaticaly sequenced by postgres. Let me show you what i mean. This is how we do it :

CREATE TABLE person (
id BIGSERIAL NOT NULL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
country VARCHAR(50) NOT NULL );

! Note how there is no semicolon until the fourth line.
We can press ENTER after
CREATE TABLE person (
and start the new line. Once you're finished, put a semicolon ; and press enter, only then terminal will execute those commands.
The terminal then confirms your creation and tells you:

CREATE TABLE

Now check again to see what's in our 'test' with :
\d
and terminal will show you :

test=# \d
              List of relations
 Schema |     Name      |   Type   |  Owner
--------+---------------+----------+----------
 public | person        | table    | postgres
 public | person_id_seq | sequence | postgres
(2 rows)

Here you'll notice how the second row has

public | person_id_seq | sequence | postgres

This is the built-in sequencer from postgres, that'll make sure to check and auto increment our id property 24/7. Pretty awesome right ?

We have just checked our database, now let's check out our table with the command:
\dt
Then terminal will show you :

test=# \dt
         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | person | table | postgres
(1 row)

ADD NEW INFORMATION IN OUR TABLES :
Now that we have created our new person table, let's populate it. AKA let's INSERT the VALUEs for a new person. You can do this with :
INSERT INTO person (name, country) VALUES ('Jean-Baptiste', 'BEL');

press ENTER and you'll see :

test=# INSERT INTO person (name, country) VALUES ('Jean-Baptiste', 'BEL');
INSERT 0 1

let's add two more :
INSERT INTO person (name, country) VALUES ('Jev', 'US');
INSERT INTO person (name, country) VALUES ('Rudy', 'UK');

Then it should look like this :

test=# INSERT INTO person (name, country) VALUES ('Jev', 'US');
INSERT 0 1
test=# INSERT INTO person (name, country) VALUES ('Rudy', 'UK');
INSERT 0 1

A way to see everything from person is :
SELECT * FROM person;

* means EVERYTHING / ALL

test=# SELECT * FROM person;
 id |      name       | country
----+-----------------+---------
  1 | Jean-Baptiste   | BEL
  2 | jev             | US
  3 | Rudy            | UK
(3 rows)

extra: let's say you want the count of all your persons :
SELECT COUNT(id) FROM person;

test=# SELECT COUNT(id) FROM person;
 count
-------
     3
(1 row)

Well done, we've just added three people in our person database ! We were also able to read it, and to count how many people are in our database. If this were thousands of people, this was very welcome.

UPDATE THE INFORMATION IN OUR TABLES :
What if we have to change a name that we have in the database ? No need to delete and re-enter, you can use the following command :
UPDATE person SET name = 'Tapis-Plain' WHERE id = 1;

for my OG readers, my French is still lame ... We select here based on ID with the id of '1', in our example this is Jean-Baptiste. Let's check again. The console will return

UPDATE 1

Now let's chek again with
SELECT * FROM person;

test=# SELECT * FROM person;
 id |      name       | country
----+-----------------+---------
  2 | jev             | US
  3 | Rudy            | UK
  1 | Tapis-Plain     | BEL
(3 rows)

Very cool and good but ... 'Tapis-Plain' is a fabric and not a person's name. That's just silly ! So let's delete it, OK ? Last step incoming, you're almost there !

DELETE INFORMATION IN OUR TABLES :
Pretty straightforward. With the previous knowledge, we apply it to the DELETE command and you're good to go :
DELETE FROM person WHERE id = 1;
SELECT * FROM person;
The result :

test=# SELECT * FROM person;
 id |      name       | country
----+-----------------+---------
  2 | jev             | US
  3 | Rudy            | UK
(2 rows)

YOU DID IT ! Create, Read, Update and Delete (aka CRUD) in the Postgres SQL terminal.
Well done ! You just applied all needed techniques for a real world crud database. Before we end this rather long read, i'll leave you with a last command, handy when you want a more detailed overview of your database. This is called the expanded view :
\x
Terminal will show you :

test=# \x
Expanded display is on.
test=# SELECT * FROM person;
-[ RECORD 1 ]------------
id      | 2
name    | Beaucoupdargent
country | BEL
-[ RECORD 2 ]------------
id      | 3
name    | Magere
country | BEL

To leave this view press
q or \x once more.