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)