DEV Community

miku86
miku86

Posted on

PostgreSQL: How To Create Some Basic Queries

#beginners #tutorial #postgres

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

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

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

WHERE Clause

Sometimes we want to show only specific results.
That's where the WHERE clause comes in handy.

SELECT * FROM [table] WHERE [expression];
Enter fullscreen mode Exit fullscreen mode

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

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

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

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

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)