Creating Tables

Up until now, the tables you’ve been querying magically existed. But when you build an application from scratch, you have to design and build the database architecture yourself.

This is done using Data Definition Language (DDL). The most fundamental DDL command is CREATE TABLE.

The CREATE TABLE Syntax

To create a table, you must specify the table’s name, and then provide a comma-separated list of column names along with their Data Types.

CREATE TABLE products (
  id INTEGER,
  name TEXT,
  price DECIMAL(10, 2),
  is_active BOOLEAN
);

Let’s break down these Data Types. When you define a column, you must explicitly state what kind of data is allowed to live in it. The database engine enforces this to prevent bugs (e.g., trying to insert the word “Hello” into a price column).

Common SQL Data Types

While exact data types vary slightly between PostgreSQL, MySQL, and SQL Server, the core concepts are universal:

INTEGER / INT: Whole numbers without decimals (e.g., 1, 42, -500). Used for IDs, quantities, counts.
DECIMAL(p, s) / NUMERIC: Exact decimal numbers. The p stands for precision (total digits), and s stands for scale (digits after the decimal). DECIMAL(10,2) is perfect for money: it can store up to 10 total digits, with exactly 2 digits after the decimal point (e.g., 99999999.99).
TEXT / VARCHAR(n): Text strings. VARCHAR(255) restricts the string to a maximum of 255 characters, while TEXT allows virtually unlimited length.
BOOLEAN: True or False values.
DATE / TIMESTAMP: Used to store calendar dates (YYYY-MM-DD) or exact timestamps down to the millisecond.

[!CAUTION] If you create a table and later realize you forgot a column, you shouldn’t use CREATE TABLE again (it will fail because the table already exists). Instead, you use the ALTER TABLE command (e.g., ALTER TABLE products ADD COLUMN description TEXT;).

Try building your very first database table from scratch in the interactive lab!