Analyzing CREATE TABLE (PostgreSQL)

In the previous section, we wrote our first SQL query to create a table in PostgreSQL.

Now, let’s slow down a bit and understand what that SQL actually means.

We’ll break the query into parts and understand the syntax step by step.

---

The SQL We Wrote

create table cities (
  name varchar(50),
  country varchar(50),
  population integer,
  area integer
);

This single SQL statement created a table in our database.
Let’s understand how.

---

Keywords vs Identifiers

Every SQL statement is made up of different words.

These words fall into two main categories:

🔹 Keywords

• Special words defined by SQL

• They tell the database what action to perform

Examples:

• create

• table

• integer

• varchar

Keywords are usually written in uppercase (this is a convention, not a rule).

---

🔹 Identifiers

• Names that we choose

• Used to tell the database what to operate on

Examples:

• cities (table name)

• name, country, population, area (column names)

Identifiers are usually written in lowercase.

---

Breaking Down the Statement

1️⃣ create table cities

create table cities

This line tells PostgreSQL:

• create a new table

• name the table cities

At this point, PostgreSQL knows what we want to create.

---

2️⃣ Parentheses ( ... )

(
  name varchar(50),
  country varchar(50),
  population integer,
  area integer
)

Everything inside these parentheses defines the structure of the table.

Each line represents one column.

---

Understanding Columns and Data Types

Each column has two parts:

column_name data_type

Let’s go through them.

---

varchar(50)

varchar stands for variable character.

You can think of it as a string.

name varchar(50)
country varchar(50)

• Stores text

• 50 means the maximum allowed length is 50 characters

If we try to insert a string longer than 50 characters, PostgreSQL will throw an error.

This helps keep data clean and controlled.

---

integer

population integer
area integer

• Stores whole numbers

• No decimal values allowed

In PostgreSQL, an integer can store values roughly between:

• -2 billion

• +2 billion

---

Is integer Enough for Population?

In our case, we are storing city data.

The largest city population today is around 38 million.

So realistically:

• we do not expect a city population to cross 2 billion

• using integer is perfectly fine for this example

Choosing the right data type is an important part of database design.

---

Why Data Types Matter

Data types help PostgreSQL:

• validate data

• prevent invalid values

• store data efficiently

• improve performance

That’s why we must always think carefully before choosing a data type.

---

What We Have So Far

After running this SQL:

• a table named cities exists

• it has four columns

• each column has a defined data type

At this point, the table structure is ready — but it is still empty.