Intro
So we learned how to create simple queries to read & write some data.
Now we want to have a look at some of the additional methods to create some basic queries.
Connect to the Database Shell
First, I connect to my created database shell:
psql -d miku86-db
Read: Show All Data In person-table again:
- I created a new
person-table to have some more columns:
miku86-db=# CREATE TABLE person (id SERIAL PRIMARY KEY, nickname TEXT NOT NULL, country TEXT, available BOOLEAN NOT NULL);
miku86-db=# \d person
Table "public.person"
Column | Type | Collation | Nullable | Default
----------------+---------+-----------+----------+------------------------------------
id | integer | | not null | nextval('person_id_seq'::regclass)
nickname | text | | not null |
country | text | | |
available | boolean | | not null |
Indexes:
"person_pkey" PRIMARY KEY, btree (id)
- And I added some rows:
miku86-db=# INSERT INTO person(nickname, country, available) VALUES ('miku86', 'germany', true);
miku86-db=# INSERT INTO person(nickname, country, available) VALUES ('heidi', 'austria', true);
miku86-db=# INSERT INTO person(nickname, country, available) VALUES ('tom', 'usa', false);
miku86-db=# INSERT INTO person(nickname, country, available) VALUES ('pavel', 'russia', false);
- Showing all rows:
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)
I SELECT all (* = wildcard) rows FROM the person-table.
There are 4 rows in it.
Show Specific Columns
Sometimes we want to show only specific columns.
Example #1: Show all persons, but only the columns for nickname and country:
miku86-db=# SELECT nickname, country FROM person;
nickname | country
---------------+---------
miku86 | germany
heidi | austria
tom | usa
pavel | russia
(4 rows)
WHERE Clause
Sometimes we want to show only specific results.
That's where the WHERE clause comes in handy.
SELECT * FROM [table] WHERE [expression];
Example #1: Show persons with the id of 1:
miku86-db=# SELECT * FROM person WHERE id = 1;
id | nickname | country | available
---------+----------+---------+-----------
1 | miku86 | germany | t
(1 row)
In this case, the expression is id = 1.
Example #2: Show persons that are not available (=> available = false):
miku86-db=# SELECT * FROM person WHERE available = false;
id | nickname | country | available
---------+----------+---------+-----------
3 | tom | usa | f
4 | pavel | russia | f
Note: Booleans accepts multiple representations, e.g. 'f', 'no', '0'. Read the docs.
LIMIT Clause
Sometimes we want to limit the results.
That's where the LIMIT clause comes in handy.
Example #1: Show all persons, but at maximum 2:
miku86-db=# SELECT * FROM person LIMIT 2;
id | nickname | country | available
---------+----------+---------+-----------
1 | miku86 | germany | t
2 | heidi | austria | t
(2 rows)
Without LIMIT 2, we would see 4 rows, but we limit it to 2 rows.
ORDER BY
Sometimes we want to order/sort the results.
That's where the ORDER BY Docs comes in handy.
Example #1: Show all persons and order them by their country (ascending):
miku86-db=# SELECT * FROM person ORDER BY country;
id | nickname | country | available
---------+----------+---------+-----------
2 | heidi | austria | t
1 | miku86 | germany | t
4 | pavel | russia | f
3 | tom | usa | f
(4 rows)
Next Part
We will learn how to update and delete data.
Further Reading
PostgreSQL Docs
SQL Syntax
PostgreSQL Data Types
Select-List Items Docs
WHERE Docs
LIMIT Docs
ORDER BY Docs
Top comments (0)