miku86

PostgreSQL: How To Create Some Basic Queries

2019-09-09

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