miku86

PostgreSQL: How To Create Our First Table

2019-09-06

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

Questions

  • What's your favorite SQL database?