DEV Community

miku86
miku86

Posted on

PostgreSQL: How To Read From & Write To Our Table

#beginners #tutorial #postgres

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

Result:

miku86-db=#
Enter fullscreen mode Exit fullscreen mode

Check Table

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

Read: Show All Data In person-table: 

miku86-db=# SELECT * FROM person;
 id | nickname 
---------+----------
(0 rows)
Enter fullscreen mode Exit fullscreen mode

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

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

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

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
SQL Syntax
PostgreSQL Data Types

Questions

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

Top comments (0)