Creating tables via SQL editor in Supabase - Visual Walkthrough

Process Flow - Creating tables via SQL editor

Open SQL Editor

↓

Write CREATE TABLE statement

↓

Run the SQL command

↓

Database processes command

↓

Table created in database

↓

Verify table exists

↓

END

This flow shows how you open the SQL editor, write a CREATE TABLE command, run it, and the database creates the table.

Execution Sample

Supabase

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100) UNIQUE
);

This SQL command creates a 'users' table with id, name, and email columns.

Process Table

Step	Action	SQL Command	Database Response	Result
1	Open SQL editor		SQL editor ready	Ready to write SQL
2	Write CREATE TABLE	CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(100), email VARCHAR(100) UNIQUE);	SQL command entered	Command ready to run
3	Run SQL command	CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(100), email VARCHAR(100) UNIQUE);	Command executed successfully	Table 'users' created
4	Verify table	SELECT * FROM users;	Empty result set	Table exists and is empty

💡 Table 'users' created successfully and verified by empty SELECT query

Status Tracker

Variable	Start	After Step 2	After Step 3	After Step 4
SQL Editor State	Closed	Open with command typed	Command executed	Table verified
Table 'users' Exists	No	No	Yes	Yes
Table 'users' Rows	N/A	N/A	0	0

Key Moments - 3 Insights

Why does the table not show any rows immediately after creation?

Because the table is just created and no data has been inserted yet, as shown in execution_table step 4 where SELECT returns empty.

What happens if you run the CREATE TABLE command twice without changes?

The database will return an error because the table already exists. This is not shown in the current execution but is important to avoid.

Why do we use SERIAL for the id column?

SERIAL automatically creates a unique number for each new row, acting as a primary key, ensuring each row can be uniquely identified.

Visual Quiz - 3 Questions

Look at the execution table, what is the database response after running the CREATE TABLE command?

A Empty result set

B SQL editor ready

C Command executed successfully

D Command entered but not run

💡 Hint

Check the 'Database Response' column at Step 3 in the execution_table

At which step does the table 'users' first exist in the database?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Look at the 'Result' column in execution_table where the table creation is confirmed

If you add a new column after creation, how would the execution table change?

A A new step with ALTER TABLE command and success response

B The table would be created twice

C The SQL editor would close automatically

D The table would be deleted

💡 Hint

Think about how SQL commands modify tables after creation, not shown but logically following the flow

Concept Snapshot

CREATE TABLE syntax:
CREATE TABLE table_name (
  column_name data_type constraints,
  ...
);

Run this in SQL editor to create a new table.
Use SERIAL for auto-increment IDs.
Verify creation with SELECT query.

Full Transcript

To create a table using the SQL editor, first open the editor. Then write a CREATE TABLE statement defining the table name and columns with their data types and constraints. Run the command. The database processes it and creates the table. You can verify the table exists by running a SELECT query which will show no rows initially since the table is empty. The id column often uses SERIAL to auto-generate unique IDs. Running the CREATE TABLE command twice without changes causes an error because the table already exists.

Practice

1. What is the main purpose of the CREATE TABLE statement in Supabase SQL editor?

easy

A. To insert data into an existing table

B. To delete an existing table from the database

C. To update data in an existing table

D. To create a new table with specified columns and data types

Solution

1. Step 1: Understand the purpose of CREATE TABLE

   The CREATE TABLE command is used to make a new table structure in the database.

2. Step 2: Differentiate from other SQL commands

   Deleting, inserting, or updating data uses different commands like DROP TABLE, INSERT INTO, or UPDATE.

3. Final Answer:

   To create a new table with specified columns and data types -> Option D

4. Quick Check:

   CREATE TABLE means create table [OK]

Hint: Remember: CREATE TABLE makes new tables, not data changes [OK]

Common Mistakes:

• Confusing CREATE TABLE with INSERT or UPDATE commands
• Thinking CREATE TABLE deletes tables
• Assuming CREATE TABLE modifies existing data

2. Which of the following is the correct syntax to create a table named users with columns id (integer) and email (text) in Supabase SQL editor?

easy

A. CREATE users TABLE (id INT, email STRING);

B. CREATE TABLE users (id INTEGER, email TEXT);

C. TABLE CREATE users (id INT, email TEXT);

D. CREATE TABLE users id INTEGER, email TEXT;

Solution

1. Step 1: Check the correct SQL syntax for table creation

   The correct syntax is CREATE TABLE table_name (column_name data_type, ...); with parentheses around columns.

2. Step 2: Validate data types and order

   Use INTEGER and TEXT as valid data types. The statement must start with CREATE TABLE followed by the table name.

3. Final Answer:

   CREATE TABLE users (id INTEGER, email TEXT); -> Option B

4. Quick Check:

   Correct SQL syntax = CREATE TABLE users (id INTEGER, email TEXT); [OK]

Hint: Use parentheses and correct data types for columns [OK]

Common Mistakes:

• Missing parentheses around columns
• Wrong order of keywords
• Using invalid data types like STRING instead of TEXT

3. Given the SQL command:

CREATE TABLE products (product_id SERIAL PRIMARY KEY, name TEXT NOT NULL, price NUMERIC);

What will be the behavior of the product_id column?

medium

A. It stores text values

B. It must be manually entered for each new product

C. It will automatically generate a unique number for each new row

D. It allows duplicate values

Solution

1. Step 1: Understand the SERIAL PRIMARY KEY usage

   The SERIAL type auto-increments numbers for new rows, and PRIMARY KEY ensures uniqueness.

2. Step 2: Analyze column behavior

   Because of SERIAL PRIMARY KEY, product_id auto-generates unique numbers; manual entry is not required.

3. Final Answer:

   It will automatically generate a unique number for each new row -> Option C

4. Quick Check:

   SERIAL PRIMARY KEY = auto unique number [OK]

Hint: PRIMARY KEY with SERIAL auto-increments IDs [OK]

Common Mistakes:

• Thinking SERIAL requires manual input
• Assuming duplicates are allowed in PRIMARY KEY
• Confusing data types for this column

4. You wrote this SQL to create a table:

CREATE TABLE orders (order_id INT PRIMARY KEY, order_date DATE, customer_id INT);

The table creates successfully, but when inserting rows without specifying order_id, Supabase gives an error. What is the most likely cause?

medium

A. No SERIAL type for auto-incrementing primary key

B. Missing semicolon at the end of the statement

C. No column defined as UNIQUE

D. Using INT instead of INTEGER for data type

Solution

1. Step 1: Check the primary key column definition

   The order_id is INT PRIMARY KEY but lacks auto-increment, so inserting rows without specifying order_id causes errors.

2. Step 2: Understand SERIAL usage for auto-increment

   Using SERIAL instead of INT for primary key allows automatic unique number generation, preventing insert errors.

3. Final Answer:

   No SERIAL type for auto-incrementing primary key -> Option A

4. Quick Check:

   Primary key needs SERIAL for auto-increment [OK]

Hint: Use SERIAL for auto-increment primary keys [OK]

Common Mistakes:

• Assuming INT auto-increments automatically
• Ignoring missing semicolon errors
• Confusing UNIQUE with PRIMARY KEY requirements

5. You want to create a table employees with columns id (auto-incrementing primary key), name (text, required), and email (text, unique). Which SQL command correctly achieves this in Supabase?

hard

A. CREATE TABLE employees (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE);

B. CREATE TABLE employees (id INT PRIMARY KEY, name TEXT, email TEXT UNIQUE NOT NULL);

C. CREATE TABLE employees (id SERIAL, name TEXT NOT NULL, email TEXT);

D. CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE);

Solution

1. Step 1: Define auto-incrementing primary key

   Use SERIAL PRIMARY KEY for id to auto-generate unique IDs.

2. Step 2: Set constraints for name and email

   name must be NOT NULL to be required; email must be UNIQUE to avoid duplicates.

3. Step 3: Verify all constraints and syntax

   CREATE TABLE employees (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE); correctly includes all required constraints and syntax.

4. Final Answer:

   CREATE TABLE employees (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE); -> Option A

5. Quick Check:

   Auto-increment + NOT NULL + UNIQUE = CREATE TABLE employees (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE); [OK]

Hint: Combine SERIAL, NOT NULL, and UNIQUE for correct constraints [OK]

Common Mistakes:

• Forgetting NOT NULL on required columns
• Using INT without SERIAL for auto-increment
• Missing UNIQUE constraint on email