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?