PostgreSQL: How To Create Our First Table
Tags: beginners, tutorial, postgres
Published: 2019-09-06 13:10:29

---
title: PostgreSQL: How To Create Our First Table
published: true
description: PostgreSQL: How To Create Our First Table
tags: beginners, tutorial, postgres
series: Intro to PostgreSQL (for NodeJS & Express)
---

## Intro

So we [installed](https://dev.to/miku86/postgresql-what-is-it-how-to-install-it-16hk) and [setup](https://dev.to/miku86/postgresql-how-to-setup-our-installed-postgresql-39hf) 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](https://dev.to/miku86/postgresql-how-to-setup-our-installed-postgresql-39hf).

Now we can connect to the database shell:
```
psql -d [db]
```

My result, because I created a database "miku86-db" earlier:
```
miku86-db=#
```

[psql](https://www.postgresql.org/docs/current/app-psql.html) 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`](https://www.postgresql.org/docs/current/datatype-numeric.html) (= an `integer`, that always gets increased by `1`) and that is a [`PRIMARY KEY`](https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-PRIMARY-KEYS), 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`](https://www.postgresql.org/docs/current/datatype-character.html) (= variable and unlimited in its length) and that is [`NOT NULL`](https://www.postgresql.org/docs/current/ddl-constraints.html#id-1.5.4.5.6), 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](https://www.postgresql.org/)
[PostgreSQL Docs](https://www.postgresql.org/docs/current/index.html)
[SQL Syntax](https://en.wikipedia.org/wiki/SQL_syntax)
[PostgreSQL Data Types](https://www.postgresql.org/docs/current/datatype.html)
[PostgreSQL Constraints](https://www.postgresql.org/docs/current/ddl-constraints.html)

---

## Questions

- What's your favorite SQL database?