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