PostgreSQL: How To Create Our First Table

Intro

So we installed and setup PostgreSQL on our machine.

Now we want to learn how to create our first table.

---

Connect to the Database Shell

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:

miku86-db=#

psql is a terminal-based front-end to PostgreSQL, you can type in queries, issue them to PostgreSQL, and see the query results.

---

Basic Commands

• psql help: help
• psql commands: \?
• SQL commands: \h

• quit psql: \q

• current connection info: \conninfo

• list of databases: \l

• connect to a database: \c [dbname]

• create database: CREATE DATABASE [db];

• delete database: DROP DATABASE [db];

• display tables of database: \dt

• display overview of table: \d [table]

• create table: CREATE TABLE [table]([col][datatype] [constraints])

---

Create Our First Table

• 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 SERIAL (= an integer, that always gets increased by 1) and that is a PRIMARY KEY, meaning that id is 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 nickname can'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)

---

Next Part

We will read & write from/to our table.

---

Further Reading

PostgreSQL Homepage
PostgreSQL Docs
SQL Syntax
PostgreSQL Data Types
PostgreSQL Constraints

---

Questions

• What's your favorite SQL database?