SQL, creating a table

A database is composed by one or more tables.

A table is where your data actually lives. Each table has a name, a set of columns, and zero or more rows. Before you can store anything, you need to create the table and tell the database what shape the data has.

Creating a table in SQL is done using the CREATE TABLE command.

At creation time you need to specify the table columns names, and the type of data they are going to hold.

Data types

SQL defines several kinds of data.

The most important text and date types, and the ones you’ll see more often, are:

• CHAR
• TEXT
• VARCHAR
• DATE
• TIME
• DATETIME
• TIMESTAMP

Numeric types include

• TINYINT 1 byte
• INT 4 bytes
• BIGINT 8 bytes
• SMALLINT 2 bytes
• DECIMAL
• FLOAT

They all hold numbers. What changes is the size that this number can be.

A TINYINT goes goes 0 to 255. An INT from -2^31 to +2^31.

The bigger the size in bytes, the more space will be needed in storage.

The difference between CHAR and VARCHAR? CHAR(20) always reserves 20 characters and pads shorter values. VARCHAR(20) stores up to 20 characters and only uses the space it needs. My advice is to reach for VARCHAR for names, emails, and anything with a variable length.

Create the table

This is the syntax to create a people table with 2 columns, one an integer and the other a variable length string:

CREATE TABLE people (
  age INT,
  name CHAR(20)
);

The database answers with something like CREATE TABLE (PostgreSQL) or Query OK, 0 rows affected (MySQL). That confirms the table exists.

You can verify by asking for its structure. In PostgreSQL’s psql shell:

\d people

In MySQL, DESCRIBE people; does the same job. Both list each column with its type.

One thing to watch out for: running the same CREATE TABLE twice fails with an error like relation "people" already exists. When you write setup scripts that may run more than once, use:

CREATE TABLE IF NOT EXISTS people (
  age INT,
  name CHAR(20)
);

The columns in this table accept NULL values, because we did not say otherwise. We’ll tighten that with constraints like NOT NULL in the next posts of this series.

If you use an ORM, I built a free schema converter that turns CREATE TABLE statements into Prisma or Drizzle schemas (and back).

Related posts about database: