SQLDatabase Fundamentalsbeginner40 min read

Creating Tables with CREATE TABLE

Master PostgreSQL CREATE TABLE syntax, define columns and constraints, configure identity columns and default values, inspect table structures, and safely modify or remove tables.

In this lesson, you will learn

✓Write syntactically correct CREATE TABLE statements.
✓Define columns using appropriate SQL data types.
✓Apply PRIMARY KEY, NOT NULL, UNIQUE, CHECK, and DEFAULT constraints.
✓Configure automatically generated IDs with GENERATED ALWAYS AS IDENTITY and GENERATED BY DEFAULT AS IDENTITY.
✓Understand the differences between identity columns and SERIAL.
✓Inspect table structures using psql and information_schema.
✓Modify table structures with ALTER TABLE.
✓Understand the limitations of IF NOT EXISTS and use DROP TABLE safely.

1. What Does CREATE TABLE Do?

A table is a structure that stores related records in rows and organizes information into columns.

For example, an online store may have a products table containing product IDs, names, prices, and stock quantities.

The CREATE TABLE statement defines the structure of a new table. It specifies:

- The table name.
- The column names.
- The data type of each column.
- Optional constraints that control which values are allowed.
- Optional default values and identity-generation behavior.

A table definition describes the structure, not the actual product records. You insert records later using INSERT.

In PostgreSQL, you normally create the table inside the database to which your session is connected.

Create a simple products table

sql

1
2
3
4
5
CREATE TABLE products (
    product_id INTEGER,
    product_name VARCHAR(100),
    price NUMERIC(10, 2)
);

Output

CREATE TABLE

The statement creates a table with three columns. product_id stores an integer, product_name stores a string of up to 100 characters, and price stores an exact decimal value with a declared precision and scale. No constraints have been added yet.

Note

CREATE TABLE defines the structure. INSERT adds records. SELECT retrieves records. These are separate operations.

2. Understand the Structure of CREATE TABLE

The general PostgreSQL syntax is:

CREATE TABLE table_name (
column_name data_type [column_constraint],
another_column data_type [column_constraint]
);

Each column definition contains a name and a data type. You can add constraints to restrict the values that the column accepts.

Separate column definitions with commas. Do not place a comma after the final column definition.

Table names and column names are identifiers. Lowercase snake_case is a useful convention because it avoids unnecessary quoting and keeps database objects consistent.

Read the parts of a CREATE TABLE statement

sql

1
2
3
4
5
CREATE TABLE students (
    student_id INTEGER,
    student_name VARCHAR(100),
    age SMALLINT
);

students is the table name. student_id, student_name, and age are column names. INTEGER, VARCHAR(100), and SMALLINT are their data types.

Note

The SQL syntax shown here is PostgreSQL-compatible. Other database systems may support different data types, identity syntax, constraints, and table options.

3. Choose Appropriate Column Data Types

Choose each column's data type according to the meaning and valid range of the information.

Common choices include:

- INTEGER: whole numbers such as ordinary numeric identifiers and quantities.
- BIGINT: whole numbers that may exceed the INTEGER range.
- VARCHAR(n): variable-length strings with a maximum character length.
- TEXT: variable-length text without a declared character-length limit.
- NUMERIC(p, s): exact decimal values, such as prices.
- BOOLEAN: TRUE, FALSE, or NULL.
- DATE: a calendar date.
- TIMESTAMPTZ: an instant in time.
- UUID: a 128-bit identifier.
- JSONB: structured JSON data that can be queried.

Avoid choosing a type just because it is familiar. For example, a price is not a string, and a date should not normally be stored as arbitrary text.

Selecting an appropriate type helps the database validate values and perform operations correctly.

Use different data types

sql

1
2
3
4
5
6
7
8
CREATE TABLE employees (
    employee_id INTEGER,
    employee_name VARCHAR(100),
    salary NUMERIC(10, 2),
    joining_date DATE,
    is_active BOOLEAN,
    created_at TIMESTAMPTZ
);

Each column uses a type appropriate to its meaning. This example demonstrates types only; the next sections add constraints and defaults.

4. Add a Primary Key

A primary key identifies each row in a table.

A primary key must satisfy two requirements:

- Its value must be unique.
- It cannot be NULL.

A table can have only one primary key constraint, although that key can consist of more than one column.

For a products table, product_id is a natural choice for a primary key when each product record needs its own identifier.

Declaring a column as INTEGER does not make it unique automatically. You must explicitly add PRIMARY KEY or another suitable uniqueness constraint.

Create a table with a primary key

sql

1
2
3
4
CREATE TABLE categories (
    category_id INTEGER PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL
);

Output

CREATE TABLE

category_id uniquely identifies each row and cannot be NULL. category_name is required because it has a NOT NULL constraint.

Note

Primary keys are commonly used as the target of foreign keys in related tables. They help establish relationships between records.

5. Understand NOT NULL and UNIQUE

NOT NULL and UNIQUE enforce different rules.

**NOT NULL**

Requires a column to contain a value rather than NULL.

**UNIQUE**

Prevents duplicate values under the constraint's comparison rules. For an ordinary PostgreSQL UNIQUE constraint, multiple NULL values are allowed by default because NULL values are not treated as equal for this purpose.

A primary key combines uniqueness and non-nullability.

For example, a product SKU should often be required and unique because it identifies a product within the business system.

Use NOT NULL and UNIQUE

sql

1
2
3
4
5
CREATE TABLE inventory_items (
    item_id INTEGER PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    item_name VARCHAR(100) NOT NULL
);

Every row needs an item ID, SKU, and item name. No two rows can use the same non-NULL SKU. PostgreSQL enforces these rules when rows are inserted or updated.

Note

UNIQUE is a database constraint, not just a convention. PostgreSQL checks it even when data is inserted by a different application or script.

6. Use CHECK to Enforce Rules

A CHECK constraint requires a specified condition to be satisfied by each inserted or updated row.

For example, a product price should not be negative, and stock quantity should not be below zero.

The database is a good place to enforce basic data-integrity rules that must apply regardless of which application writes the data.

A CHECK constraint does not replace every form of business validation. More complicated rules involving multiple rows or tables may need other database features or application logic.

Enforce valid product values

sql

1
2
3
4
5
6
7
8
CREATE TABLE stock_items (
    item_id INTEGER PRIMARY KEY,
    item_name TEXT NOT NULL,
    price NUMERIC(10, 2) NOT NULL
        CHECK (price >= 0),
    stock_quantity INTEGER NOT NULL
        CHECK (stock_quantity >= 0)
);

PostgreSQL rejects a row when its price or stock quantity violates the relevant CHECK constraint. Because the columns are also NOT NULL, missing values are rejected as well.

Note

In PostgreSQL, a CHECK condition that evaluates to TRUE or NULL does not violate the constraint. Combine CHECK with NOT NULL when a missing value must also be rejected.

7. Configure Automatically Generated IDs

A table often needs a unique numeric identifier for every row.

One way to generate these IDs is with an identity column.

PostgreSQL supports two identity-generation modes:

**GENERATED ALWAYS AS IDENTITY**

PostgreSQL generates the value automatically. Explicitly inserting a value into this column is normally rejected unless the statement includes OVERRIDING SYSTEM VALUE.

**GENERATED BY DEFAULT AS IDENTITY**

PostgreSQL generates a value when the INSERT omits the column or specifies DEFAULT. An explicitly supplied value can be accepted.

Identity columns use an underlying sequence to generate values. They do not, by themselves, guarantee that values will be consecutive with no gaps. Failed inserts, rollbacks, and other sequence behavior can produce gaps.

The identity declaration also does not automatically make a column a primary key. Add PRIMARY KEY when the column must uniquely identify each row.

Generate IDs automatically

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE customers (
    customer_id BIGINT
        GENERATED ALWAYS AS IDENTITY
        PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);
 
INSERT INTO customers (customer_name)
VALUES ('Asha'), ('Ravi');
 
SELECT customer_id, customer_name
FROM customers
ORDER BY customer_id;

Output

customer_id | customer_name -------------+-------------- 1 | Asha 2 | Ravi

PostgreSQL generates the customer IDs. The example output illustrates a typical fresh sequence; actual generated numbers depend on the table's sequence state.

Note

For most new PostgreSQL tables that need automatically generated numeric identifiers, GENERATED ALWAYS AS IDENTITY is a strong default.

8. GENERATED ALWAYS vs GENERATED BY DEFAULT

Both identity modes generate values when the INSERT does not provide an explicit value.

The difference is how they handle explicit values.

With GENERATED ALWAYS AS IDENTITY, an explicit value is normally rejected. PostgreSQL supports OVERRIDING SYSTEM VALUE when an explicit value must be inserted intentionally.

With GENERATED BY DEFAULT AS IDENTITY, an explicit value can override the generated value.

Use ALWAYS when the database should normally control ID generation. Use BY DEFAULT when controlled imports or other workflows need to supply IDs directly.

Explicitly inserting values into an identity column does not necessarily advance its underlying sequence to match those values. Plan sequence synchronization carefully when importing records.

Allow explicitly supplied identity values

sql

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE imported_customers (
    customer_id BIGINT
        GENERATED BY DEFAULT AS IDENTITY
        PRIMARY KEY,
    customer_name TEXT NOT NULL
);
 
INSERT INTO imported_customers (
    customer_id,
    customer_name
)
VALUES (100, 'Imported Customer');

The BY DEFAULT mode permits the explicit ID of 100. The application or migration must still consider the sequence state before future automatically generated values are used.

GENERATED ALWAYS AS IDENTITY vs GENERATED BY DEFAULT AS IDENTITY

Both modes generate identity values automatically when no explicit value is supplied. ALWAYS is stricter about manually supplied values; BY DEFAULT permits them.

Aspect	GENERATED ALWAYS AS IDENTITY	GENERATED BY DEFAULT AS IDENTITY
Normal automatic generation	Yes	Yes
Explicit value in INSERT	Normally rejected unless an appropriate OVERRIDING clause is used	Accepted when other constraints permit it
Typical use	New tables where PostgreSQL should control identifier generation	Workflows that need to import or explicitly assign identifiers
Primary key behavior	Does not create a primary key automatically	Does not create a primary key automatically

When to use GENERATED ALWAYS AS IDENTITY:

Prefer for ordinary application-generated identifiers when explicit IDs should not normally be supplied.

When to use GENERATED BY DEFAULT AS IDENTITY:

Use when controlled workflows need to supply explicit values while retaining automatic generation for omitted values.

9. Identity Columns vs SERIAL

Older PostgreSQL examples frequently use SERIAL for automatically generated integer values.

For example:

id SERIAL PRIMARY KEY

SERIAL is a PostgreSQL-specific convenience feature. It creates an integer column with a sequence-backed default and related sequence ownership behavior.

PostgreSQL introduced standard-style identity columns in version 10. For new table definitions, identity columns are generally preferred because their generation behavior is explicitly part of the column definition.

SERIAL is not inherently broken and remains common in existing projects. You should be able to recognize it in older code.

Neither SERIAL nor an identity declaration automatically makes the column unique unless a PRIMARY KEY or UNIQUE constraint is also defined.

Modern identity syntax

sql

1
2
3
4
5
6
CREATE TABLE orders (
    order_id BIGINT
        GENERATED ALWAYS AS IDENTITY
        PRIMARY KEY,
    order_status VARCHAR(30) NOT NULL
);

The identity declaration makes order_id automatically generated, while PRIMARY KEY enforces uniqueness and non-nullability.

Note

You do not need to rewrite every existing SERIAL column immediately. Understand the behavior of both approaches and use identity columns for suitable new designs.

10. Assign Default Values

The DEFAULT clause supplies a value when an INSERT omits the column or explicitly uses DEFAULT.

Common examples include:

- DEFAULT 0 for an initial stock count.
- DEFAULT TRUE for a Boolean flag.
- DEFAULT CURRENT_TIMESTAMP for a creation timestamp.

Defaults reduce repetitive work for applications and provide consistent initial values.

A default does not prevent an application from supplying another value unless a separate constraint or generation rule requires that behavior.

Also, a default does not replace NOT NULL. If a column has a default but an INSERT explicitly supplies NULL, the default is not used; the insert stores NULL if permitted or fails if the column is NOT NULL.

Create columns with defaults

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE tasks (
    task_id BIGINT
        GENERATED ALWAYS AS IDENTITY
        PRIMARY KEY,
    task_title TEXT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    is_completed BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL
        DEFAULT CURRENT_TIMESTAMP
);
 
INSERT INTO tasks (task_title)
VALUES ('Learn CREATE TABLE');
 
SELECT *
FROM tasks;

The INSERT supplies only the task title. PostgreSQL generates task_id and supplies the defaults for status, is_completed, and created_at. The timestamp represents the current transaction timestamp and is displayed according to the session time zone.

Note

DEFAULT is used for an omitted column or an explicit DEFAULT keyword. An explicit NULL is not replaced by the default.

11. Create a Complete Products Table

Now combine the concepts in a practical products table for an online store.

The table uses:

- An automatically generated primary key.
- A required, unique SKU.
- A required product title.
- An optional description.
- A non-negative exact decimal price.
- A non-negative stock quantity with a default.
- An active flag with a default.
- A creation timestamp with a default.

These constraints help protect the data even if records are inserted by different applications or scripts.

Create a products table

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE products (
    product_id BIGINT
        GENERATED ALWAYS AS IDENTITY
        PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    title VARCHAR(150) NOT NULL,
    description TEXT,
    price NUMERIC(10, 2) NOT NULL
        CHECK (price >= 0),
    stock_quantity INTEGER NOT NULL
        DEFAULT 0
        CHECK (stock_quantity >= 0),
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL
        DEFAULT CURRENT_TIMESTAMP
);

Output

CREATE TABLE

This creates a table with an identity primary key and constraints on SKU, title, price, and stock quantity. The default timestamp represents the current transaction time; its displayed zone depends on the session setting.

Note

Use constraints to enforce rules that must always hold for the stored data, not only rules checked by one particular frontend.

12. Insert Data into the New Table

Once the table exists, use INSERT to add records.

Because product_id is generated automatically, the INSERT statement can omit that column. The database generates an ID for each inserted row.

The stock quantity, active flag, and timestamp can also be omitted because they have defaults.

Insert products

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
INSERT INTO products (
    sku,
    title,
    description,
    price
)
VALUES
    (
        'KB-001',
        'Mechanical Keyboard',
        'Compact keyboard for everyday use',
        2499.99
    ),
    (
        'MS-001',
        'Wireless Mouse',
        'Wireless mouse with adjustable sensitivity',
        799.50
    );
 
SELECT
    product_id,
    sku,
    title,
    price,
    stock_quantity,
    is_active,
    created_at
FROM products
ORDER BY product_id;

The database generates product IDs and supplies the omitted default values. The query retrieves the inserted rows. Actual IDs and timestamp display depend on the database's current state and session configuration.

13. Inspect a Table's Structure

After creating a table, inspect its structure to verify the column names, data types, defaults, and nullability.

If you use psql, the meta-command \d products displays a table's structure and related information.

For a SQL-based approach, query information_schema.columns. This is useful when you need to inspect schema information through SQL or build administrative reports.

Remember that table names can repeat in different schemas. Filtering by table_schema helps ensure you inspect the intended table.

Inspect columns with information_schema

sql

1
2
3
4
5
6
7
8
9
10
SELECT
    column_name,
    data_type,
    is_nullable,
    column_default,
    ordinal_position
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'products'
ORDER BY ordinal_position;

The query lists columns in their defined order. Identity-column metadata and defaults can be represented differently from ordinary default expressions, depending on the column and PostgreSQL version.

Note

In psql, use \d products. If your table belongs to a schema other than the active search path, use a schema-qualified name such as \d public.products.

14. Use IF NOT EXISTS Carefully

PostgreSQL supports CREATE TABLE IF NOT EXISTS.

If a table with the specified name already exists, the statement avoids raising the usual duplicate-table error.

This can be useful in some setup scripts, but it has an important limitation: PostgreSQL does not verify that the existing table has the same columns, data types, or constraints as the definition in the statement.

For example, an old products table might lack a price constraint. Running CREATE TABLE IF NOT EXISTS will not automatically add that constraint.

For managed application schemas, use migrations to apply and track structural changes explicitly.

Create a table if it does not exist

sql

1
2
3
4
5
6
CREATE TABLE IF NOT EXISTS practice_notes (
    note_id BIGINT
        GENERATED ALWAYS AS IDENTITY
        PRIMARY KEY,
    note_title TEXT NOT NULL
);

If practice_notes does not exist, PostgreSQL creates it. If a relation with that name already exists, PostgreSQL issues a notice rather than validating and updating its structure.

Note

IF NOT EXISTS does not mean 'create or update the table to match this definition.' Use ALTER TABLE or a proper migration when an existing schema needs to change.

15. Modify a Table with ALTER TABLE

After creating a table, you may need to change its structure.

ALTER TABLE supports operations such as adding columns, changing column defaults, and adding or removing constraints.

Before modifying a table used by an application, consider its existing data and how the change affects application code.

For example, adding a nullable column is often straightforward. Adding a NOT NULL column to a populated table requires a plan for existing rows, such as a suitable default or a staged migration.

Test structural changes before applying them to production.

Add a column and change a default

sql

1
2
3
4
5
6
7
8
ALTER TABLE products
ADD COLUMN category_name VARCHAR(80);
 
ALTER TABLE products
ALTER COLUMN stock_quantity SET DEFAULT 10;
 
ALTER TABLE products
ALTER COLUMN stock_quantity SET DEFAULT 0;

The first statement adds a nullable category_name column. The next statement changes the default for future inserts that omit stock_quantity. The final statement restores the original default. Changing a default does not rewrite existing rows.

Note

Changing a column default affects future inserts that use the default. It does not automatically change the values already stored in existing rows.

16. Drop a Table Safely

DROP TABLE removes a table definition and its stored data.

This is a destructive operation. Before executing it, confirm the database, schema, and exact table name.

DROP TABLE IF EXISTS avoids an error if the target table does not exist. As with CREATE TABLE IF NOT EXISTS, this clause only handles the existence check; it does not make a destructive operation safe or reversible.

PostgreSQL also supports CASCADE and RESTRICT behavior when other database objects depend on a table. RESTRICT is the default behavior and prevents dropping the table when dependent objects block the operation. CASCADE can remove dependent objects and should be used with care.

A transaction rollback may be possible for transactional DDL in PostgreSQL when the operation is performed inside a transaction and the transaction has not been committed. Nevertheless, do not rely on rollback as your only protection against accidental deletion.

Remove a disposable practice table

sql

1
DROP TABLE IF EXISTS practice_notes;

Output

DROP TABLE

The statement drops practice_notes if it exists. PostgreSQL still checks for dependencies and permissions. The command should only be run after verifying that the table is safe to remove.

Note

Never use DROP TABLE on an important application table just to test a command. Confirm the target, dependencies, backup, and recovery plan first.

17. Common CREATE TABLE Errors

**Missing comma**

Each column definition must be separated by a comma, except for the last one.

**Duplicate table name**

Creating a table that already exists normally produces an error. Use IF NOT EXISTS only when its limited behavior is appropriate.

**Duplicate values in a unique column**

PostgreSQL rejects an INSERT or UPDATE that violates a UNIQUE or PRIMARY KEY constraint.

**Invalid CHECK value**

A row that violates a CHECK condition is rejected.

**Explicit identity value rejected**

A GENERATED ALWAYS identity column normally rejects explicitly supplied values unless an appropriate OVERRIDING clause is used.

**Invalid default or data type**

The default expression must be compatible with the column type.

**Column does not exist**

An INSERT, SELECT, or ALTER statement may reference a misspelled or nonexistent column.

Read the error message carefully. It often identifies the affected table, column, constraint, or statement location.

Note

When debugging DDL, first verify punctuation and syntax, then check the column types, constraints, existing schema, and permissions.

18. Knowledge Check: MCQs

Answer each question before reading its explanation.

19. Guided Practice

Use your PostgreSQL practice database to complete the following tasks.

**Task 1:** Create a table named practice_products.

**Task 2:** Add these columns:
- product_id: automatically generated primary key.
- sku: required and unique string.
- product_name: required string.
- price: required exact decimal value that cannot be negative.
- stock_quantity: required integer with a default of zero that cannot be negative.
- created_at: required timestamp with a time-zone-aware type and a default current timestamp.

**Task 3:** Insert two products without supplying product_id, stock_quantity, or created_at.

**Task 4:** Query all products and inspect their generated IDs and defaults.

**Task 5:** Use information_schema.columns to inspect the table definition.

**Task 6:** Add a category_name column using ALTER TABLE.

**Task 7:** Explain what happens if you run CREATE TABLE IF NOT EXISTS again with a different definition.

**Task 8:** After verifying that the table is disposable, remove it with DROP TABLE IF EXISTS.

Attempt each task before checking the solutions.

Begin the practice exercise

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE practice_products (
    product_id BIGINT
        GENERATED ALWAYS AS IDENTITY
        PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    product_name VARCHAR(150) NOT NULL,
    price NUMERIC(10, 2) NOT NULL
        CHECK (price >= 0),
    stock_quantity INTEGER NOT NULL
        DEFAULT 0
        CHECK (stock_quantity >= 0),
    created_at TIMESTAMPTZ NOT NULL
        DEFAULT CURRENT_TIMESTAMP
);

This creates the required structure with identity generation, uniqueness, required values, non-negative checks, and defaults.

20. Guided Practice: Solutions

Compare your answers with the following reference solution. Run the statements in order. The final DROP TABLE statement deletes the practice table, so do not run it if you want to keep your exercise data.

Complete practice solution

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- Task 2: The table definition is shown above.
 
-- Task 3: Insert two products.
INSERT INTO practice_products (
    sku,
    product_name,
    price
)
VALUES
    ('KB-001', 'Keyboard', 2499.99),
    ('MS-001', 'Mouse', 799.50);
 
-- Task 4: Inspect inserted products.
SELECT
    product_id,
    sku,
    product_name,
    price,
    stock_quantity,
    created_at
FROM practice_products
ORDER BY product_id;
 
-- Task 5: Inspect the schema.
SELECT
    column_name,
    data_type,
    is_nullable,
    column_default,
    ordinal_position
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'practice_products'
ORDER BY ordinal_position;
 
-- Task 6: Add a column.
ALTER TABLE practice_products
ADD COLUMN category_name VARCHAR(80);
 
-- Task 7:
-- IF NOT EXISTS does not update an existing
-- table to match a different definition.
 
-- Task 8: Run only after confirming the table
-- is disposable and no longer needed.
DROP TABLE IF EXISTS practice_products;

The INSERT omits columns with defaults and the generated ID. The inspection query displays column metadata. ALTER TABLE adds the category column, and the final statement removes the practice table.

21. Interview Questions and Answers

**Q1. What is CREATE TABLE?**

CREATE TABLE is a DDL statement that defines a new table, including its columns, data types, and constraints.

**Q2. What is a primary key?**

A primary key uniquely identifies each row and cannot contain NULL values.

**Q3. What is the difference between PRIMARY KEY and UNIQUE?**

Both enforce uniqueness. A primary key also requires non-nullability and identifies the table's primary row key. A table can have one primary key constraint but multiple UNIQUE constraints.

**Q4. What is the difference between NOT NULL and DEFAULT?**

NOT NULL prevents missing values. DEFAULT supplies a value when a column is omitted or DEFAULT is explicitly specified. A default alone does not prohibit NULL.

**Q5. What is a CHECK constraint?**

CHECK requires a Boolean condition to be satisfied for inserted or updated rows. In PostgreSQL, a condition that evaluates to NULL does not violate CHECK, so use NOT NULL as well when necessary.

**Q6. What is an identity column?**

An identity column uses a sequence-backed mechanism to generate values automatically. It is commonly used for numeric identifiers.

**Q7. What is the difference between GENERATED ALWAYS and GENERATED BY DEFAULT?**

ALWAYS normally rejects explicit identity values unless an appropriate OVERRIDING clause is used. BY DEFAULT permits explicitly supplied values when other constraints allow them.

**Q8. What is the difference between identity columns and SERIAL?**

Identity columns use standard-style identity syntax introduced in PostgreSQL 10. SERIAL is a PostgreSQL-specific convenience feature that creates a sequence-backed default. Both are used for automatic numeric values, but their definitions and override behavior differ.

**Q9. Does an identity column guarantee consecutive IDs?**

No. Sequence values can have gaps because of failed inserts, rollbacks, and other sequence behavior.

**Q10. What does IF NOT EXISTS do?**

It avoids a duplicate-object error when a table with the specified name already exists. It does not verify that the existing table has the requested definition.

**Q11. How can you inspect a table in psql?**

Use \d table_name. You can also query information_schema.columns for column names, data types, defaults, nullability, and ordinal positions.

**Q12. What is ALTER TABLE used for?**

ALTER TABLE changes an existing table, for example by adding a column, changing a default, or adding a constraint.

**Q13. What is the difference between DROP TABLE and DELETE?**

DROP TABLE removes the table definition and its data. DELETE removes selected rows from a table while leaving the table structure in place.

**Q14. Why should DROP TABLE be used carefully?**

It removes the table and its data. Dependencies, privileges, and recovery requirements should be checked before the operation is executed.

22. Lesson Summary

In this lesson, you learned how to create and manage PostgreSQL tables.

Remember these rules:

- CREATE TABLE defines columns, data types, and constraints.
- Use PRIMARY KEY to identify rows uniquely.
- Use NOT NULL when a value must be present.
- Use UNIQUE when duplicate values are not permitted.
- Use CHECK to enforce row-level conditions.
- Use DEFAULT to supply values when columns are omitted or DEFAULT is specified.
- Use GENERATED ALWAYS AS IDENTITY for automatically generated identifiers when explicit IDs should normally be rejected.
- Use GENERATED BY DEFAULT AS IDENTITY when explicit IDs may be required.
- Understand SERIAL because it is common in existing PostgreSQL code.
- Use information_schema.columns or psql's \d command to inspect table structure.
- Use ALTER TABLE to modify existing tables.
- Remember that IF NOT EXISTS does not update or repair an existing table's definition.
- Use DROP TABLE only after confirming the target and understanding its impact.

The central principle is to combine suitable data types with constraints that enforce your application's data rules.

You are now ready to continue learning how to insert records into tables and retrieve data with SELECT.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Using reserved SQL keywords as identifiers

Why it happens: Names such as order, group, and user can conflict with SQL syntax or have special meanings in PostgreSQL.

Incorrect Approach

sql

CREATE TABLE orders (
    order INTEGER,
    user VARCHAR(50)
);

Correct Solution

sql

CREATE TABLE orders (
    order_id INTEGER,
    user_id INTEGER,
    order_status VARCHAR(50)
);

Key takeaway: Descriptive names such as order_id and user_id avoid unnecessary quoting and make queries easier to read. Some keywords can be used as identifiers, but avoiding ambiguous names is a useful convention.

#2 Assuming an identity column is automatically a primary key

Why it happens: Automatically generated values can look unique, so beginners may assume uniqueness is enforced automatically.

Incorrect Approach

sql

CREATE TABLE customers (
    customer_id BIGINT GENERATED ALWAYS AS IDENTITY
);

Correct Solution

sql

CREATE TABLE customers (
    customer_id BIGINT
        GENERATED ALWAYS AS IDENTITY
        PRIMARY KEY
);

Key takeaway: Identity generation supplies values, but PRIMARY KEY or UNIQUE is needed to enforce uniqueness.

#3 Assuming DEFAULT replaces an explicit NULL

Why it happens: A default is sometimes mistaken for a rule that fills every missing or invalid value.

Incorrect Approach

sql

CREATE TABLE tasks (
    status TEXT DEFAULT 'pending'
);
 
INSERT INTO tasks (status) VALUES (NULL);

Correct Solution

sql

CREATE TABLE tasks (
    status TEXT NOT NULL DEFAULT 'pending'
);
 
INSERT INTO tasks DEFAULT VALUES;

Key takeaway: An explicit NULL does not invoke the default. NOT NULL rejects NULL, while DEFAULT supplies a value when the column is omitted or DEFAULT is specified.

#4 Assuming IF NOT EXISTS updates an existing table

Why it happens: The clause may sound as if it ensures the table always matches the latest definition.

Incorrect Approach

sql

Running CREATE TABLE IF NOT EXISTS again and expecting missing columns or constraints to be added automatically.

Correct Solution

sql

ALTER TABLE products
ADD COLUMN category_name VARCHAR(80);

Key takeaway: IF NOT EXISTS only avoids the duplicate-table error. Use ALTER TABLE or a controlled migration to change an existing schema.

#5 Using CHECK without considering NULL

Why it happens: A developer may assume that CHECK automatically makes a column mandatory.

Incorrect Approach

sql

CREATE TABLE products (
    price NUMERIC CHECK (price >= 0)
);

Correct Solution

sql

CREATE TABLE products (
    price NUMERIC NOT NULL CHECK (price >= 0)
);

Key takeaway: A CHECK condition evaluating to NULL does not violate the constraint in PostgreSQL. Add NOT NULL when the column must always contain a value.

#6 Expecting identity values to be gap-free

Why it happens: Automatically generated IDs may look like a consecutive count of inserted rows.

Incorrect Approach

sql

Assuming every inserted row must receive the next number with no gaps.

Correct Solution

sql

Use identity values as identifiers, not as guaranteed gap-free business numbering.

Key takeaway: Sequence values may be consumed by failed statements or rolled-back transactions. Gaps are normal.

#7 Dropping a table without checking the target

Why it happens: DROP TABLE is a short command, but it has a destructive effect.

Incorrect Approach

sql

Running DROP TABLE products without verifying the database or schema.

Correct Solution

sql

Verify the connection, schema, target table, dependencies, and recovery plan before dropping it.

Key takeaway: DROP TABLE removes the table and its stored data. IF EXISTS only handles a missing table and does not protect against dropping the wrong existing table.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Key takeaways

CREATE TABLE defines a table's columns, data types, and constraints.
PRIMARY KEY uniquely identifies each row and does not permit NULL values.
NOT NULL prevents a column from containing NULL.
UNIQUE prevents duplicate values in a column or combination of columns.
CHECK enforces a Boolean condition on inserted or updated rows.
DEFAULT supplies a value when an INSERT omits a column or specifies DEFAULT.
GENERATED ALWAYS AS IDENTITY is useful for automatically generated numeric identifiers.
GENERATED BY DEFAULT AS IDENTITY allows explicit values to be inserted when appropriate.
SERIAL is a PostgreSQL-specific legacy convenience feature; identity columns are generally preferred for new designs.
IF NOT EXISTS does not verify that an existing table matches the requested definition.
ALTER TABLE changes an existing table, while DROP TABLE removes the table and its data.
Destructive schema changes should be reviewed and tested before being applied to important databases.

Was this lesson helpful?

Found an error? Report an issue