PostgreSQL Create Table

PostgreSQL CREATE TABLE

The CREATE TABLE statement is used to create a new table in a database. Tables are the fundamental building blocks of a relational database system.

Without tables, you have absolutely nowhere to store your application's data. They organize data logically into structured rows and columns.

Basic Syntax

To create a table, you must provide a unique, descriptive name for the table. You must also define the names and data types of its internal columns.

The syntax starts with the CREATE TABLE keywords. It is followed by the table name and a set of parentheses containing the column definitions.

CREATE TABLE table_name (
  column1 datatype constraint,
  column2 datatype constraint,
  column3 datatype constraint
);

Common Data Types

Every column in a PostgreSQL table must have a strict, predefined data type. The data type strictly dictates what kind of data can be stored in that specific column.

INTEGER is used for whole numbers, like user IDs, ages, or product quantities. VARCHAR(n) is used for variable-length text, like names, addresses, or emails.

BOOLEAN stores simple true or false logical values natively. DATE safely stores calendar dates without any associated time zones.

Primary Keys

A primary key uniquely identifies each individual record in a database table. It ensures that no two rows can ever have the exact same primary key value.

Primary keys are incredibly important for establishing reliable database relationships. They must contain UNIQUE values, and absolutely cannot contain NULL values.

CREATE TABLE Users (
  UserID SERIAL PRIMARY KEY,
  Username VARCHAR(50),
  Email VARCHAR(100)
);

The SERIAL Type

In the example code above, we uniquely utilized the SERIAL data type. SERIAL is a special PostgreSQL feature designed for auto-incrementing numbers.

It automatically generates a unique ID for every brand new row inserted. This saves you from manually calculating the next ID every time you add data.

Using IF NOT EXISTS

If you try to create a table that already exists, a database error will occur. To explicitly prevent this crash, you can use the IF NOT EXISTS clause.

This tells PostgreSQL to only create the table if it is completely new. If it already exists, the database will safely ignore the command entirely.

CREATE TABLE IF NOT EXISTS Users (
  UserID SERIAL PRIMARY KEY,
  Username VARCHAR(50)
);

Summary

Creating tables is the absolute first step to building a relational database schema. Always proactively define a primary key to ensure strict data uniqueness.

Use appropriate, lightweight data types to heavily optimize database storage and speed.