Working with Create Table in Postgres

Intro

The CREATE TABLE statement is a fundamental statement in SQL that allows you to create databases in SQL. When using this statement, you will be able to detail columns, constraints and other details about your table. These can be changed later as well. We wont be able to cover everything you can do with CREATE TABLE, but we will cover the basics.

The Syntax

The basic syntax of CREATE TABLE is as follows:

CREATE TABLE [table_name] (
  [columns]
  ...
  [constraints]
)

If you want the answer, here is the quick solution.

CREATE TABLE employees (
  emp_no SERIAL PRIMARY KEY,
  birth_date DATE NOT NULL
);

Getting Setup

We will be using docker in this article, but feel free to install your database locally instead. Once you have docker installed, create a new file called docker-compose.yml and add the following.

version: '3'
 
services:
  db:
    image: 'postgres:latest'
    ports:
      - 5432:5432
    environment:
      POSTGRES_USER: username
      POSTGRES_PASSWORD: password
      POSTGRES_DB: default_database
    volumes:
      - psqldata:/var/lib/postgresql

  phpmyadmin:
    image: phpmyadmin/phpmyadmin
    links:
      - db
    environment:
      PMA_HOST: db
      PMA_PORT: 3306
      PMA_ARBITRARY: 1
    restart: always
    ports:
      - 8081:80

volumes:
  psqldata:

Next, run docker-compose up.

Now, navigate to http://localhost:8081/ to access phpMyAdmin. Then log in with the username root and pass root_pass.

Click the SQL tab and you are ready to go.

An Example

When create tables there are many things to consider. A few items are the following:

• the table name
• the column names
• the columns types
• the column "nullability"

Let's start by creating an employees table. Here is the structure.

CREATE TABLE employees (
);

Now, we want to add a emp_no which will be an ID. And birth_date which is Date.

When choosing types, you can use the official doc here: https://www.postgresql.org/docs/9.5/datatype.html.

We also want to determine if the columns are null or not null, i.e. required or optional. Let's fill in the results for these columns.

CREATE TABLE employees (
  emp_no INT NOT NULL,
  birth_date DATE NOT NULL
);

PostgreSQL allows us to defined a Primary Key and we can also Auto Increment this. This feature will tell PostgreSQL to assign an ID and index that ID so you can easily search. This is common so that each row in our database will have a unique identifier.

To continue with with our basic table design, let's convert emp_no into a primary key. Notice that we change emp_no to be the SERIAL time which is short for a integer with auto incrementing index.

CREATE TABLE employees (
  emp_no SERIAL PRIMARY KEY,
  birth_date DATE NOT NULL
);

There is much more to uncover about table design, but that is all we will cover here.