Module F-2·16 min read

Data types, CREATE TABLE, INSERT, SELECT, UPDATE, DELETE — every fundamental operation from first principles.

Written by Jatin Jain Saraf · Architect of SupraScan

F-2 — Tables, Rows, and the Relational Mental Model

Q: A Senior Principal Software Engineer is designing a global e-commerce platform where order creation times must be stored and consistently interpreted across various user timezones. Which PostgreSQL data type is the most appropriate choice for the 'created_at' column, and why?

B. TIMESTAMPTZ, because it stores the time internally in UTC and transparently converts it to the session's timezone, ensuring consistent interpretation regardless of the client's location. — Option B is correct. TIMESTAMPTZ (timestamp with time zone) is explicitly recommended for storing dates and times in PostgreSQL when timezone context is important. It stores the time internally as UTC and then converts it to the timezone of the current database session when retrieved, ensuring that the time is always interpreted correctly for the user or application's context. Option A is incorrect because bare TIMESTAMP stores a date and time without any timezone context, leading to ambiguity and potential data corruption if server timezones change or if data is accessed from different timezones. Option C is incorrect because DATE only stores the calendar date, losing crucial time information for order creation. Option D, while a valid approach in some systems, is not the idiomatic PostgreSQL solution and introduces manual conversion complexities that TIMESTAMPTZ handles automatically and robustly.

Q: An operations team needs to regularly clear a large, temporary staging table containing millions of rows before importing new data. The table has no foreign key constraints and no row-level triggers. Which SQL command is the most efficient and appropriate for this task, and what are its key advantages over alternatives?

C. TRUNCATE TABLE staging_table; because it removes all rows instantly without scanning them individually or firing row-level triggers, making it significantly faster for emptying an entire table. — Option C is correct. TRUNCATE TABLE is designed for quickly removing all rows from a table. It achieves this by deallocating the entire table's storage, which is much faster than DELETE's row-by-row removal process. Crucially, as stated in the lesson, it does not scan individual rows and does not fire row-level triggers, making it highly efficient for large tables. Option A is incorrect because while DELETE allows WHERE clauses and rollback, it is much slower for emptying an entire table as it processes each row. Option B is incorrect because dropping and recreating the table is an extreme measure that is generally slower than TRUNCATE and might involve re-establishing permissions or other table-level configurations. Option D is incorrect as setting columns to NULL does not remove rows and is a highly inefficient way to 'clear' a table, consuming storage and not resetting auto-incrementing sequences.

Q: A microservice in a production environment retrieves user profile data using `SELECT * FROM users WHERE id = :userId;`. From a Senior Principal Software Engineer's perspective, why is using `SELECT *` in this context considered a poor practice, particularly for long-term maintainability and performance?

B. It can lead to application breakage if new columns are added to the table (e.g., sensitive data exposure, unexpected data types), prevents optimal query plan optimization, and increases network traffic by transferring unnecessary data. — Option B is correct. Using `SELECT *` in production code is problematic because it couples the application tightly to the table's schema. If new columns are added (especially sensitive ones), the application might inadvertently receive and process them, leading to security vulnerabilities or unexpected behavior. If column order changes (though less common in modern PostgreSQL), it could break applications relying on ordinal positions. Furthermore, the database cannot optimize the query plan as effectively when it doesn't know which specific columns are required, and it increases network bandwidth usage by transferring potentially unnecessary data. Option A is partially true regarding metadata but the primary concerns are maintainability, security, and network efficiency, not just metadata fetching. Option C is incorrect because the issue applies to any column, not just large text/JSONB types, though larger types exacerbate the network traffic problem. Option D is incorrect; databases do not automatically prune unused columns from the result set when `SELECT *` is used; they return all columns, and it's up to the application to ignore them.

Who this module is for: You completed F-1, have PostgreSQL installed, and can connect with psql. Now you need to understand what a table actually is, what data types exist and which to use, and how to perform every fundamental SQL operation — creating tables, inserting data, reading it, modifying it, and deleting it. No assumed knowledge of SQL.

The Relational Mental Model

A relational database stores data in tables. A table is the simplest structure to grasp: it is a spreadsheet.

Table: users
┌────┬───────────┬──────────────────────┬─────────────────────────────┐
│ id │   name    │        email         │         created_at          │
├────┼───────────┼──────────────────────┼─────────────────────────────┤
│  1 │ Alice     │ alice@example.com    │ 2026-05-01 09:00:00+00      │
│  2 │ Bob       │ bob@example.com      │ 2026-05-02 14:30:00+00      │
│  3 │ Carol     │ carol@example.com    │ 2026-05-03 11:15:00+00      │
└────┴───────────┴──────────────────────┴─────────────────────────────┘

A column (also called a field or attribute) defines a piece of data every row will have — its name and type
A row (also called a record or tuple) is one complete entity — one user, one order, one product
A cell is the intersection of a row and column — one specific value

The critical difference from a spreadsheet: in PostgreSQL, every value in a column must match that column's declared type. You cannot store the number 42 in a column declared as TEXT, and you cannot store the string "hello" in a column declared as INTEGER. The database enforces this for you.

Data Types — The Foundation of Every Column

Choosing the right data type for each column is one of the most important decisions you make. The wrong type causes subtle bugs, wastes storage, and breaks operations you will want to do later.

Numbers

SMALLINT          -- -32,768 to 32,767 (2 bytes) — rarely needed
INTEGER           -- -2,147,483,648 to 2,147,483,647 (4 bytes) — counts, quantities
BIGINT            -- -9.2 quintillion to 9.2 quintillion (8 bytes) — IDs, large counts
NUMERIC(p, s)     -- exact decimal, p digits total, s after decimal point — MONEY, percentages
FLOAT / DOUBLE    -- approximate decimal (DO NOT use for money — loses precision)

When to use which:

INTEGER — counts, ages, quantities, foreign key references (if table will stay under 2 billion rows)
BIGINT — auto-incrementing IDs in high-traffic systems, timestamps as milliseconds
NUMERIC(10, 2) — prices ($999,999.99), percentages, anything where exact decimal matters
Never use FLOAT or DOUBLE PRECISION for financial data — 0.1 + 0.2 is not exactly 0.3 in floating point

Text

TEXT              -- variable-length string, no size limit — use this almost always
VARCHAR(n)        -- variable-length string, max n characters — use only when you need to enforce a limit
CHAR(n)           -- fixed-length string, padded with spaces to n characters — almost never useful

The rule: use TEXT for everything unless you have a specific reason to enforce a maximum length. VARCHAR(255) is a habit from older databases — in PostgreSQL, TEXT is just as efficient and more flexible.

True/False

BOOLEAN           -- true, false, or NULL

PostgreSQL accepts TRUE, FALSE, 't', 'f', 'yes', 'no', '1', '0' when inserting.

Dates and Times

DATE              -- calendar date only (2026-05-17) — no time, no timezone
TIME              -- time of day only (14:30:00) — no date, no timezone
TIMESTAMP         -- date + time, NO timezone (dangerous — avoid this)
TIMESTAMPTZ       -- date + time WITH timezone (always use this)
INTERVAL          -- a duration ('3 days', '2 hours 30 minutes')

The most important rule in this module: always use TIMESTAMPTZ, never bare TIMESTAMP. A bare TIMESTAMP stores a date and time with no timezone context — if your server's timezone changes, or if you have users in multiple timezones, the stored values become ambiguous. TIMESTAMPTZ stores UTC internally and converts to/from the session timezone transparently.

Unique Identifiers

SERIAL            -- auto-incrementing INTEGER (shorthand for INTEGER DEFAULT nextval())
BIGSERIAL         -- auto-incrementing BIGINT — use this for IDs
UUID              -- 128-bit universally unique identifier (e.g. 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11')

For primary keys: use BIGSERIAL for most tables. Use UUID when you need IDs generated client-side or across distributed systems.

`CREATE TABLE`

CREATE TABLE table_name (
  column_name  data_type  [constraints],
  column_name  data_type  [constraints],
  ...
);

A complete example:

CREATE TABLE products (
  id          BIGSERIAL    PRIMARY KEY,
  name        TEXT         NOT NULL,
  description TEXT,
  price       NUMERIC(10, 2) NOT NULL,
  in_stock    BOOLEAN      NOT NULL DEFAULT true,
  created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

Breaking this down:

id BIGSERIAL PRIMARY KEY — auto-incrementing integer, unique identifier for each row
name TEXT NOT NULL — required text field (cannot be left empty)
description TEXT — optional (can be NULL — the absence of a value)
price NUMERIC(10,2) NOT NULL — exact decimal, required
in_stock BOOLEAN NOT NULL DEFAULT true — required, defaults to true if not provided
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() — automatically set to the current time

Naming conventions: use snake_case (lowercase with underscores). Do not use spaces, capital letters, or reserved words (order, user, table need to be quoted if used as names — avoid them).

Verify the table was created

\d products
 
-- Output:
--                            Table "public.products"
--    Column    |          Type          | Collation | Nullable |      Default
-- -------------+------------------------+-----------+----------+-------------------
--  id          | bigint                 |           | not null | nextval('products_id_seq'::regclass)
--  name        | text                   |           | not null |
--  description | text                   |           |          |
--  price       | numeric(10,2)          |           | not null |
--  in_stock    | boolean                |           | not null | true
--  created_at  | timestamp with time zone |         | not null | now()

`INSERT` — Adding Rows

-- Insert a single row, specifying all columns
INSERT INTO products (name, description, price, in_stock)
VALUES ('Mechanical Keyboard', 'TKL, Cherry MX Blue switches', 129.99, true);
 
-- Insert a single row, letting defaults apply (id and created_at auto-fill)
INSERT INTO products (name, price)
VALUES ('USB-C Cable', 12.99);
-- description will be NULL, in_stock will be true (default), created_at will be NOW()
 
-- Insert multiple rows in one statement (more efficient than separate INSERTs)
INSERT INTO products (name, price, in_stock) VALUES
  ('Wireless Mouse', 49.99, true),
  ('Monitor Stand', 39.99, true),
  ('Laptop Sleeve', 24.99, false);

RETURNING — get the generated ID back:

INSERT INTO products (name, price)
VALUES ('Headphones', 89.99)
RETURNING id, created_at;
 
-- Output:
--  id |          created_at
-- ----+-------------------------------
--   6 | 2026-05-17 10:30:00.123456+00

This is essential in application code — you insert a row and immediately get back the auto-generated ID to use in subsequent operations.

`SELECT` — Reading Data

-- Read all columns from all rows
SELECT * FROM products;
 
-- Read specific columns only (always prefer this over SELECT *)
SELECT id, name, price FROM products;
 
-- Alias a column in the output
SELECT name, price, price * 1.2 AS price_with_tax FROM products;

Why avoid SELECT * in production code:

If you add a column later, SELECT * returns it automatically — potentially including sensitive data
The query plan cannot be optimised as well without knowing which columns are needed
Your application code breaks when column order changes

LIMIT — get a subset of rows:

-- Get only the first 5 rows
SELECT id, name, price FROM products LIMIT 5;
 
-- Skip the first 5 rows, then get the next 5
SELECT id, name, price FROM products LIMIT 5 OFFSET 5;

`UPDATE` — Modifying Existing Rows

-- Update the price of a specific product
UPDATE products
SET price = 119.99
WHERE id = 1;
 
-- Update multiple columns at once
UPDATE products
SET price = 44.99, in_stock = false
WHERE id = 4;
 
-- Update all rows that match a condition
UPDATE products
SET in_stock = true
WHERE in_stock = false AND price < 50.00;

⚠️ The most dangerous mistake with UPDATE: forgetting the WHERE clause.

-- THIS UPDATES EVERY ROW IN THE TABLE
UPDATE products SET price = 0;

Always write the WHERE clause before running an UPDATE. Always. In psql, you can add LIMIT 1 to test your condition first:

-- Check what you're about to update before doing it
SELECT * FROM products WHERE price > 100;
-- If this looks right, then run:
UPDATE products SET in_stock = false WHERE price > 100;

RETURNING works with UPDATE too:

UPDATE products
SET price = price * 0.9
WHERE in_stock = false
RETURNING id, name, price;
-- Shows you what the new prices are after the update

`DELETE` — Removing Rows

-- Delete a specific row
DELETE FROM products WHERE id = 6;
 
-- Delete all rows matching a condition
DELETE FROM products WHERE in_stock = false AND price < 10;
 
-- Get back what you deleted
DELETE FROM products WHERE price > 200
RETURNING id, name, price;

⚠️ Same danger as UPDATE: DELETE FROM products without a WHERE clause deletes every row.

-- DELETES EVERYTHING
DELETE FROM products;

TRUNCATE — faster than DELETE for removing all rows:

TRUNCATE TABLE products;
-- Removes all rows instantly (does not scan each row, so much faster than DELETE)
-- Cannot be used with a WHERE clause — it always removes everything
-- Does not fire row-level triggers (important to know in complex schemas)

Use TRUNCATE when you want to empty a table completely. Use DELETE when you need to remove specific rows or want to use RETURNING.

`DROP TABLE` — Removing the Table Itself

-- Remove the table and all its data
DROP TABLE products;
 
-- Remove only if it exists (prevents error if table doesn't exist)
DROP TABLE IF EXISTS products;

DROP TABLE is permanent. Unlike DELETE (which removes rows but keeps the table structure), DROP TABLE removes everything — the structure, the data, the indexes, the constraints. There is no undo — unless it's still inside an open transaction: BEGIN; DROP TABLE products; ROLLBACK; genuinely undoes it, because PostgreSQL (unlike MySQL) supports transactional DDL. Once that transaction commits, though, it's gone for good.

Practical Exercise: A Complete Products Workflow

Work through this from top to bottom in psql:

-- Step 1: Create the database and connect
CREATE DATABASE shop;
\c shop
 
-- Step 2: Create the table
CREATE TABLE products (
  id          BIGSERIAL    PRIMARY KEY,
  name        TEXT         NOT NULL,
  description TEXT,
  price       NUMERIC(10,2) NOT NULL,
  category    TEXT         NOT NULL DEFAULT 'general',
  in_stock    BOOLEAN      NOT NULL DEFAULT true,
  created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
 
-- Step 3: Insert products
INSERT INTO products (name, description, price, category) VALUES
  ('Mechanical Keyboard', 'TKL, Cherry MX Blue', 129.99, 'peripherals'),
  ('Wireless Mouse', '2.4GHz, 1600 DPI', 49.99, 'peripherals'),
  ('Monitor Stand', 'Adjustable height', 39.99, 'accessories'),
  ('USB-C Hub', '7-in-1 hub', 64.99, 'accessories'),
  ('Laptop Sleeve', '15-inch, water resistant', 24.99, 'accessories'),
  ('Webcam HD', '1080p, built-in mic', 89.99, 'peripherals');
 
-- Step 4: Read all products
SELECT * FROM products;
 
-- Step 5: Read only specific columns
SELECT id, name, price, category FROM products;
 
-- Step 6: Update a price and see the change
UPDATE products SET price = 119.99 WHERE id = 1 RETURNING id, name, price;
 
-- Step 7: Mark one product as out of stock
UPDATE products SET in_stock = false WHERE id = 5 RETURNING id, name, in_stock;
 
-- Step 8: Delete the out-of-stock product
DELETE FROM products WHERE in_stock = false RETURNING id, name;
 
-- Step 9: Verify what's left
SELECT id, name, price, in_stock FROM products;
 
-- Step 10: Check your table structure
\d products

NULL: The Absence of a Value

NULL means "no value" — it is not zero, it is not an empty string, it is the absence of any value. This trips up almost every beginner.

-- NULL is not equal to anything, including itself
SELECT NULL = NULL;    -- returns NULL, not true
SELECT NULL = 0;       -- returns NULL, not false
SELECT NULL = '';      -- returns NULL, not false
 
-- Check for NULL with IS NULL / IS NOT NULL
SELECT * FROM products WHERE description IS NULL;
SELECT * FROM products WHERE description IS NOT NULL;
 
-- COALESCE: return the first non-NULL value
SELECT name, COALESCE(description, 'No description') FROM products;
-- Returns 'No description' for rows where description is NULL

Summary

Operation	SQL	Risk if you forget WHERE
Create table	`CREATE TABLE name (cols...)`	N/A
Add rows	`INSERT INTO name (cols) VALUES (...)`	N/A
Read rows	`SELECT cols FROM name`	No risk
Modify rows	`UPDATE name SET col = val WHERE ...`	Modifies every row
Remove rows	`DELETE FROM name WHERE ...`	Deletes every row
Remove table	`DROP TABLE name`	N/A (always total)

The data types you will use 90% of the time:

BIGSERIAL for primary keys
TEXT for strings
INTEGER or BIGINT for whole numbers
NUMERIC(p,s) for exact decimals (especially money)
BOOLEAN for true/false
TIMESTAMPTZ for dates and times (always, never bare TIMESTAMP)

Module F-3 covers filtering and finding data — the WHERE clause in depth, sorting with ORDER BY, limiting results with LIMIT, and the string and date functions you will use constantly.

Next: F-3 — Filtering, Sorting, and Finding What You Need →

Knowledge Check

A Senior Principal Software Engineer is designing a global e-commerce platform where order creation times must be stored and consistently interpreted across various user timezones. Which PostgreSQL data type is the most appropriate choice for the 'created_at' column, and why?

An operations team needs to regularly clear a large, temporary staging table containing millions of rows before importing new data. The table has no foreign key constraints and no row-level triggers. Which SQL command is the most efficient and appropriate for this task, and what are its key advantages over alternatives?

A microservice in a production environment retrieves user profile data using SELECT * FROM users WHERE id = :userId;. From a Senior Principal Software Engineer's perspective, why is using SELECT * in this context considered a poor practice, particularly for long-term maintainability and performance?

Test your knowledge with more question sets

Sign in to keep reading

The rest of this module is free — sign in with Google to unlock it and track your progress.

PreviousModule F-1: What Is PostgreSQL and Why Does It Exist?Next Module F-3: Filtering, Sorting, and Finding What You Need

Discussion

Join the discussion

Loading comments...