Creating tables

Creating PostgreSQL Databases

Darryl Reeves

Industry Assistant Professor, New York University

The database table

Variable number of rows
Fixed number of columns (structure can be altered)
Columns have specific data type
Each row is a record

The CREATE TABLE command

CREATE TABLE table_name (
    column1_name column1_datatype [col1_constraints],
    column2_name column2_datatype [col2_constraints],
    ...
    columnN_name columnN_datatype [colN_constraints]
);

Name Restrictions

maximum length of 31 characters
must begin with letter or underscore ("_")

Example table 1

CREATE TABLE school (
    id serial PRIMARY KEY,
    name TEXT NOT NULL,
    mascot_name TEXT
);

Example table 2

CREATE TABLE topic (
    id SERIAL PRIMARY KEY,
    description TEXT NOT NULL
);

Table organization

Which fields should I use?
How many tables should I add?
Which data types are best to use for the fields of my table?

Let's practice!

Creating PostgreSQL Databases