PostgreSQL: How To Create Some Basic Queries
Tags: beginners, tutorial, postgres
Published: 2019-09-09 09:20:49

---
title: PostgreSQL: How To Create Some Basic Queries
published: true
description: PostgreSQL: How To Create Some Basic Queries
tags: beginners, tutorial, postgres
series: Intro to PostgreSQL (for NodeJS & Express)
---

## Intro

So we learned [how to create simple queries](https://dev.to/miku86/postgresql-how-to-read-from-write-to-our-table-48kg) 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:

```sql
psql -d miku86-db
```

---

## Read: Show All Data In `person`-table again:

- I created a new `person`-table to have some more columns:

```sql
miku86-db=# CREATE TABLE person (id SERIAL PRIMARY KEY, nickname TEXT NOT NULL, country TEXT, available BOOLEAN NOT NULL);
```

```sql
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:

```sql
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:

```sql
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`:

```sql
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](https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-WHERE) comes in handy.

```sql
SELECT * FROM [table] WHERE [expression];
```

### Example #1: Show persons with the `id` of `1`:

```sql
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):

```sql
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](https://www.postgresql.org/docs/current/datatype-boolean.html).

---

## `LIMIT` Clause

Sometimes we want to limit the results.
That's where the [`LIMIT` clause](https://www.postgresql.org/docs/current/sql-select.html#SQL-LIMIT) comes in handy.

### Example #1: Show all persons, but at maximum 2:

```sql
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](https://www.postgresql.org/docs/current/queries-order.html) comes in handy.

### Example #1: Show all persons and order them by their country (ascending):

```sql
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](https://www.postgresql.org/docs/current/index.html)
[SQL Syntax](https://en.wikipedia.org/wiki/SQL_syntax)
[PostgreSQL Data Types](https://www.postgresql.org/docs/current/datatype.html)
[Select-List Items Docs](https://www.postgresql.org/docs/current/queries-select-lists.html#QUERIES-SELECT-LIST-ITEMS)
[`WHERE` Docs](https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-WHERE)
[`LIMIT` Docs](https://www.postgresql.org/docs/current/sql-select.html#SQL-LIMIT)
[`ORDER BY` Docs](https://www.postgresql.org/docs/current/queries-order.html)