PostgreSQL: How To Read From & Write To Our Table
Tags: beginners, tutorial, postgres
Published: 2019-09-08 12:34:34

---
title: PostgreSQL: How To Read From & Write To Our Table
published: true
description: PostgreSQL: How To Read From & Write To Our Table
tags: beginners, tutorial, postgres
series: Intro to PostgreSQL (for NodeJS & Express)
---

## Intro

So we [installed](https://dev.to/miku86/postgresql-what-is-it-how-to-install-it-16hk) and [setup](https://dev.to/miku86/postgresql-how-to-setup-our-installed-postgresql-39hf) PostgreSQL on our machine.

We also learned [how to create a table](https://dev.to/miku86/postgresql-how-to-create-our-first-table-1j82).

Now we want to learn how to read from & write to our table.

---

## Connect to the Database Shell

First, I connect to my created database shell:

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

Result:

```sql
miku86-db=#
```

---

## Check Table

- Display tables of database:

```sql
miku86-db=# \dt
         List of relations
 Schema |  Name  | Type  |  Owner   
--------+--------+-------+----------
 public | person | table | miku86
(1 row)
```

- Display overview of `person`-table:

```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 | 
Indexes:
    "person_pkey" PRIMARY KEY, btree (id)
```

---

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

```sql
miku86-db=# SELECT * FROM person;
 id | nickname 
----+----------
(0 rows)
```

I `SELECT` all (`*` = wildcard) rows `FROM` the `person`-table.
Because we didn't add any data yet, there is nothing to show (= `0 rows`).

---

## Write: Add Data To `person`-table:

- Check which columns exist and which data types they need:

```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 | 
Indexes:
    "person_pkey" PRIMARY KEY, btree (id)
```

Column `id` needs an `integer`, column `nickname` needs a `text`.
The `primary key` of the `person`-table is `id`, therefore it has to be unique and not null.

Read the [docs about all data types](https://www.postgresql.org/docs/current/datatype.html).


- Add a new entry (=row) to the table:


```sql
miku86-db=# INSERT INTO person (id, nickname) VALUES (1, 'miku86');
INSERT 0 1
```

`INSERT` `INTO` the `person`-table to column `id` value `1` and to column `nickname` value `miku86`.


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

```sql
miku86-db=# SELECT * FROM person;
 id | nickname 
----+----------
  1 | miku86
(1 row)
```

I `SELECT` all (`*` = wildcard) rows `FROM` the `person`-table.
There is `1 row` in it.


## Next Part

We will do some more reading from our table.

---

## 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)

---

## Questions

- What happens when you insert a new row with an existing `id`?
- Do you always need to write down all columns?