CREATE TABLE with PostgreSQL types

Introduction

We use CREATE TABLE to make a new table in a database. Tables store data in rows and columns.

When you want to save information like users, products, or orders.

When starting a new project that needs organized data storage.

When you need to define what kind of data each column will hold.

When you want to set rules for data, like making sure a number is always positive.

When you want to prepare a database to accept data from an app or website.

Syntax

PostgreSQL

CREATE TABLE table_name (
  column_name1 data_type1 [constraints],
  column_name2 data_type2 [constraints],
  ...
);

Replace table_name with your table's name.

Choose data_type based on the kind of data you want to store, like INTEGER for numbers or TEXT for words.

Examples

This creates an employees table with an auto-incrementing ID, a name that cannot be empty, a salary with two decimals, and a hire date.

PostgreSQL

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  salary NUMERIC(10,2),
  hire_date DATE
);

This table uses a unique UUID for product IDs, limits product names to 100 characters, stores price as money, and tracks if the product is in stock.

PostgreSQL

CREATE TABLE products (
  product_id UUID PRIMARY KEY,
  product_name VARCHAR(100) NOT NULL,
  price MONEY,
  in_stock BOOLEAN DEFAULT TRUE
);

Sample Program

This creates a books table with an auto-incrementing ID, title and author as text fields that cannot be empty, a year as a number, and a price with two decimals.

PostgreSQL

CREATE TABLE books (
  book_id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  author TEXT NOT NULL,
  published_year INTEGER,
  price NUMERIC(6,2)
);

Important Notes

SERIAL is a shortcut for auto-incrementing integers.

VARCHAR(n) limits text length to n characters.

Use NOT NULL to make sure a column always has a value.

Summary

CREATE TABLE makes a new table to store data.

Choose the right data_type for each column to match your data.

Use constraints like PRIMARY KEY and NOT NULL to keep data organized and valid.