miku86

PostgreSQL: How To Read From & Write To Our Table

2019-09-08

Intro

So we installed and setup PostgreSQL on our machine.

We also learned how to create a table.

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:

psql -d miku86-db

Result:

miku86-db=#

Check Table

  • Display tables of database:
miku86-db=# \dt
         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | person | table | miku86
(1 row)
  • Display overview of person-table:
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:

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:
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.

  • Add a new entry (=row) to the table:
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:

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

Questions

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