Now we want to learn how to create our first table.
We’ve just created a user and a database in the setup article.
Now we can connect to the database shell:
psql -d [db]
My result, because I created a database “miku86-db” earlier:
psql is a terminal-based front-end to PostgreSQL, you can type in queries, issue them to PostgreSQL, and see the query results.
- psql help:
- psql commands:
- SQL commands:
- quit psql:
- current connection info:
- list of databases:
- connect to a database:
- create database:
CREATE DATABASE [db];
- delete database:
DROP DATABASE [db];
- display tables of database:
- display overview of table:
- create table:
CREATE TABLE [table]([col][datatype] [constraints])
- Check my current connection:
miku86-db=# \conninfo You are connected to database "miku86-db" as user "miku86" via socket in "/run/postgresql" at port "5432".
- Display tables of database:
miku86-db=# \dt Did not find any relations.
- Create table:
miku86-db=# CREATE TABLE person (id SERIAL PRIMARY KEY, nickname TEXT NOT NULL); CREATE TABLE
I just created a table in my database with two columns:
- column #1: an
id, that is a
integer, that always gets increased by
1) and that is a
PRIMARY KEY, meaning that
idis a unique identifier for a row in the table, therefore the value is unique and not null
- column #2: a
nickname, that is a
TEXT(= variable and unlimited in its length) and that is
NOT NULL, meaning that
nicknamecan’t be missing.
- Display overview of 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)
We will read & write from/to our table.
- What’s your favorite SQL database?