SQLDatabase Fundamentalsbeginner40 min read

Understanding Tables and Data Types

Learn how relational tables store structured data and how to choose appropriate PostgreSQL data types for integers, decimals, strings, dates, timestamps, booleans, UUIDs, and JSONB. Practice selecting types, understanding precision, and avoiding common database design mistakes.

In this lesson, you will learn

✓Explain how tables, rows, columns, and data types work together.
✓Choose appropriate integer types such as SMALLINT, INTEGER, and BIGINT.
✓Understand NUMERIC, DECIMAL, REAL, and DOUBLE PRECISION.
✓Distinguish between CHAR, VARCHAR, and TEXT in PostgreSQL.
✓Choose appropriate DATE, TIME, TIMESTAMP, and TIMESTAMPTZ types.
✓Understand NULL, BOOLEAN, UUID, JSON, and JSONB.
✓Create tables with suitable data types and inspect their structure.
✓Identify common data type mistakes and select better alternatives.

1. What Are Tables and Data Types?

A relational database organizes information into tables.

Imagine that you are building a student management application. You might create a students table containing each student's ID, name, age, and admission date.

A table contains three important parts:

- **Columns:** The individual fields, such as student_id, name, and age.
- **Rows:** Individual records. Each row represents one student.
- **Data types:** Rules that specify which kinds of values a column can store.

For example, a student's age should normally be stored as an integer rather than as arbitrary text.

Why does this matter?

Suppose you store ages as strings. Sorting and comparing those values can produce unexpected results. With an appropriate numeric type, the database can perform arithmetic and numeric comparisons correctly.

A data type is therefore more than a storage choice. It helps define what your data means and which operations are valid.

Create a table with different data types

sql

1
2
3
4
5
6
CREATE TABLE students (
    student_id INTEGER,
    name VARCHAR(100),
    age SMALLINT,
    admission_date DATE
);

Output

CREATE TABLE

The table contains four columns. student_id stores an integer, name stores a variable-length string, age stores a small integer, and admission_date stores a calendar date. This example defines column types but does not yet add primary keys or other constraints.

Note

A table can contain columns with different data types. Choose each type according to the meaning and valid range of the data.

2. Overview of Common PostgreSQL Data Types

PostgreSQL supports many data types. The following table introduces commonly used types and their typical purposes.

Storage figures below describe PostgreSQL's ordinary internal representation for the listed fixed-size types. Variable-length types use storage that depends on their values and representation.

Note

You do not need to memorize every storage detail immediately. First learn what each type represents and when to use it.

PostgreSQL Data Types Quick Reference

Category	Type	Typical Storage	Common Use
Integer	SMALLINT	2 bytes	Small counts and bounded quantities
Integer	INTEGER / INT	4 bytes	Age, quantity, ordinary numeric IDs
Integer	BIGINT	8 bytes	Large counters and high-volume identifiers
Exact decimal	NUMERIC(p, s)	Variable	Prices and precise decimal calculations
Floating point	REAL	4 bytes	Approximate scientific measurements
Floating point	DOUBLE PRECISION	8 bytes	Approximate calculations needing more precision
String	CHAR(n)	Blank-padded fixed-length value	Fixed-width text when appropriate
String	VARCHAR(n)	Variable	Strings with a maximum character length
String	TEXT	Variable	Descriptions, articles, and other text
Date	DATE	4 bytes	Birth dates and due dates
Time	TIME	8 bytes	A time of day without a date
Timestamp	TIMESTAMP	8 bytes	A local date and time without time-zone semantics
Timestamp	TIMESTAMPTZ	8 bytes	An instant in time
Boolean	BOOLEAN	1 byte	Flags and yes/no states
Identifier	UUID	16 bytes	Globally unique identifiers
Structured data	JSONB	Variable	Querying structured JSON documents

3. Integer Types: SMALLINT, INTEGER, and BIGINT

Integer types store whole numbers without a fractional part.

PostgreSQL provides three commonly used integer types:

**SMALLINT**
- Uses 2 bytes.
- Range: -32,768 to 32,767.
- Useful when values are guaranteed to remain within this range.

**INTEGER or INT**
- Uses 4 bytes.
- Range: -2,147,483,648 to 2,147,483,647.
- A good general-purpose integer type.

**BIGINT**
- Uses 8 bytes.
- Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
- Useful for large counters and identifiers that may exceed INTEGER's range.

Choose the smallest type that safely accommodates the expected values, but do not choose a type so small that normal growth causes overflow.

PostgreSQL's INTEGER type is signed. It does not have a separate built-in UNSIGNED INTEGER type like some other database systems.

Compare integer ranges

sql

1
2
3
4
SELECT
    100::SMALLINT AS small_value,
    100000::INTEGER AS normal_value,
    5000000000::BIGINT AS large_value;

The casts explicitly convert the constants to their respective integer types. The large value requires BIGINT because it exceeds INTEGER's maximum.

Note

An integer column does not automatically become unique. Use PRIMARY KEY or UNIQUE when the values must not repeat.

4. Exact Decimal Types: NUMERIC and DECIMAL

NUMERIC and DECIMAL are equivalent type names in PostgreSQL. They are used when exact decimal representation matters.

For example, NUMERIC(10, 2) specifies:
- A precision of 10 digits in total.
- A scale of 2 digits after the decimal point.

A value such as 12345.67 fits this definition.

NUMERIC(10, 2) can represent values from -99999999.99 through 99999999.99.

PostgreSQL can round values to the declared scale when storing them. Values that exceed the permitted precision can cause an error.

For financial data, NUMERIC is often a good choice because decimal arithmetic can be performed exactly within the selected precision and scale. You still need to design the precision, rounding rules, and calculations correctly for your application.

Store product prices

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE products (
    product_id INTEGER,
    product_name VARCHAR(100),
    price NUMERIC(10, 2)
);
 
INSERT INTO products
    (product_id, product_name, price)
VALUES
    (1, 'Keyboard', 1299.99),
    (2, 'Mouse', 499.50);
 
SELECT product_name, price
FROM products;

Output

product_name | price --------------+--------- Keyboard | 1299.99 Mouse | 499.50

The price column stores decimal values with a declared scale of two digits. Actual output formatting may vary by client.

Note

NUMERIC avoids binary floating-point representation errors for decimal values, but it does not automatically define your business rules for tax, currency conversion, or rounding.

5. Floating-Point Types: REAL and DOUBLE PRECISION

REAL and DOUBLE PRECISION store approximate numbers using floating-point representation.

- REAL uses 4 bytes and offers roughly 6 decimal digits of precision.
- DOUBLE PRECISION uses 8 bytes and offers roughly 15 decimal digits of precision.

These types are useful for scientific calculations, sensor readings, simulations, and other workloads where approximate arithmetic is acceptable.

However, many decimal fractions cannot be represented exactly in binary floating-point. This means that a result can be slightly different from the decimal value you expect.

For example, 0.1 and 0.2 do not have exact finite binary floating-point representations.

For financial calculations that require exact decimal arithmetic, prefer NUMERIC or DECIMAL.

Compare exact and approximate arithmetic

sql

1
2
3
4
SELECT
    0.1::NUMERIC + 0.2::NUMERIC AS exact_sum,
    0.1::DOUBLE PRECISION
        + 0.2::DOUBLE PRECISION AS approximate_sum;

The NUMERIC calculation produces the exact decimal result 0.3. The floating-point result is approximate and may display as 0.30000000000000004, depending on representation and formatting.

Note

Do not assume that floating-point arithmetic is wrong. It is appropriate for many scientific calculations. The key is to understand when approximation is acceptable.

6. String Types: CHAR, VARCHAR, and TEXT

PostgreSQL provides three familiar character types.

**CHAR(n)**

CHAR is shorthand for CHARACTER. CHAR(n) is blank-padded to the declared length. It is useful for some fixed-width formats, but it is not the best default for ordinary names or descriptions.

**VARCHAR(n)**

VARCHAR(n) stores strings of variable length, with a maximum of n characters. An insert or update that exceeds the limit normally produces an error.

**TEXT**

TEXT stores variable-length text without a declared character limit.

In PostgreSQL, VARCHAR without a length limit and TEXT have essentially the same performance characteristics for ordinary use. You generally do not need to choose VARCHAR(255) merely because it is common in tutorials.

Use a length limit when it is a genuine data rule, such as a maximum permitted username length. Use TEXT when the content does not need that particular limit.

Choose suitable string types

sql

1
2
3
4
5
6
CREATE TABLE user_profiles (
    user_id INTEGER,
    username VARCHAR(50),
    country_code CHAR(2),
    biography TEXT
);

username has a 50-character limit. country_code uses a fixed-width character type for illustration, although CHAR(2) blank-pads shorter values. biography can hold variable-length text without a declared character limit.

Note

VARCHAR(n) limits characters, not bytes. Multibyte characters can use more than one byte in the database encoding.

7. DATE, TIME, and TIMESTAMP

PostgreSQL provides different types for calendar dates and clock times.

**DATE**
Stores a calendar date, such as 2026-10-18. It does not store a time of day.

**TIME**
Stores a time of day, such as 14:30:00. It does not include a calendar date.

**TIMESTAMP WITHOUT TIME ZONE**
Stores a date and time without a time-zone interpretation. It is useful for values that represent local wall-clock time, provided that this is the intended meaning.

**TIMESTAMP WITH TIME ZONE**
Also called TIMESTAMPTZ, represents an instant in time. PostgreSQL interprets time-zone information in input values, converts the instant to its internal representation, and displays it according to the session time zone.

TIMESTAMPTZ does not retain the original time-zone name or the original offset as a separate property. If you need to preserve the user's chosen time-zone name, store it separately, for example in a TEXT column.

Use the type that matches the meaning of your data rather than automatically selecting a timestamp type for every situation.

Create columns for dates and times

sql

1
2
3
4
5
6
7
8
CREATE TABLE events (
    event_id INTEGER,
    event_name VARCHAR(100),
    event_date DATE,
    opening_time TIME,
    local_start TIMESTAMP,
    created_at TIMESTAMPTZ
);

event_date stores a date, opening_time stores a time of day, local_start stores a date and time without time-zone interpretation, and created_at represents an instant in time.

8. Handling Time Zones Correctly

Consider an application with users in India, the United Kingdom, and the United States.

If an event represents one specific moment worldwide, storing it as TIMESTAMPTZ is usually appropriate. PostgreSQL can display that instant in different session time zones.

For example, a meeting at 09:00 UTC corresponds to different local clock times in different regions.

A timestamp without a time zone does not identify a unique global instant on its own. The application needs additional context to interpret it.

For backend systems, TIMESTAMPTZ is commonly used for audit events such as created_at and updated_at.

PostgreSQL's now() and CURRENT_TIMESTAMP return a timestamp with time-zone semantics. The displayed value depends on the session time zone. Setting the session time zone to UTC can make output and debugging more consistent.

If an appointment follows a local calendar and time-zone rule, such as a meeting at 9 AM in a named city, you may also need to store the intended time-zone identifier separately.

Store and inspect a timestamp

sql

1
2
3
4
5
6
7
8
9
10
11
12
SET TIME ZONE 'UTC';
 
CREATE TABLE audit_events (
    event_id INTEGER,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
 
INSERT INTO audit_events (event_id)
VALUES (1);
 
SELECT event_id, created_at
FROM audit_events;

The session time zone is set to UTC. The DEFAULT expression records the current transaction timestamp when a row is inserted without a created_at value. The session setting affects how the timestamp is displayed.

Note

Store an instant with TIMESTAMPTZ, and perform local-time presentation in the application or with appropriate database time-zone functions. Preserve a named time zone separately when the business requirement needs it.

9. Boolean Values: TRUE, FALSE, and NULL

BOOLEAN stores a logical state.

A Boolean value can be:
- TRUE
- FALSE
- NULL, meaning the value is unknown or missing

For example, an email_verified column can record whether a user has verified an email address.

Be careful with NULL. It is not the same as FALSE. If a value is NULL, the database does not know whether the condition is true or false.

SQL uses three-valued logic for many expressions involving NULL. Use IS NULL and IS NOT NULL to test for missing values.

Create and query a Boolean column

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE accounts (
    account_id INTEGER,
    email_verified BOOLEAN
);
 
INSERT INTO accounts
    (account_id, email_verified)
VALUES
    (1, TRUE),
    (2, FALSE),
    (3, NULL);
 
SELECT *
FROM accounts
WHERE email_verified IS TRUE;
 
SELECT *
FROM accounts
WHERE email_verified IS NULL;

The first query returns accounts whose email_verified value is TRUE. The second returns accounts where the value is missing or unknown.

Note

Use IS NULL rather than = NULL. Comparisons with NULL do not behave like ordinary comparisons with known values.

10. UUID: Universally Unique Identifiers

UUID stands for Universally Unique Identifier. PostgreSQL's UUID type stores a 128-bit identifier.

A UUID looks like this:

550e8400-e29b-41d4-a716-446655440000

UUIDs are useful when records need identifiers that can be generated independently by different services or systems.

Compared with sequential integers, UUIDs are longer and can use more space in indexes. Random UUIDs can also affect index insertion patterns. Choose them when their benefits fit the application's requirements rather than using them automatically.

PostgreSQL supports UUID values directly. Current PostgreSQL versions also provide built-in UUID generation functions, with availability depending on the version and chosen UUID algorithm.

Create a table with a UUID

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE public_profiles (
    id UUID PRIMARY KEY,
    display_name TEXT NOT NULL
);
 
INSERT INTO public_profiles (id, display_name)
VALUES (
    '550e8400-e29b-41d4-a716-446655440000',
    'Alex'
);
 
SELECT id, display_name
FROM public_profiles;

The id column stores a UUID and is declared as the primary key. The example supplies a UUID explicitly, so it does not depend on a particular UUID generation function.

Note

A UUID is an identifier, not an access-control mechanism. Do not treat an unpredictable identifier as a replacement for authorization.

11. JSON and JSONB

JSON is a format for representing structured data using objects, arrays, strings, numbers, booleans, and null values.

PostgreSQL supports two main JSON types.

**JSON**
Stores the original JSON text and reparses it when processing values.

**JSONB**
Stores JSON in a decomposed binary format. It is generally more efficient for repeated processing and supports indexing options that are useful for searching JSON documents.

JSONB does not preserve every detail of the original input text, such as whitespace or object-key order. Duplicate object keys are also not preserved as separate keys in the same way as the original text.

JSONB is useful when some attributes are flexible or naturally represented as documents. It does not mean that every table should put all its data into a JSON column. Relational columns remain useful for stable fields, constraints, relationships, and frequently queried values.

Store structured attributes in JSONB

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE products_json (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    attributes JSONB
);
 
INSERT INTO products_json
    (product_id, product_name, attributes)
VALUES
    (
        1,
        'Keyboard',
        '{"layout": "US", "wireless": true}'
    );
 
SELECT
    product_name,
    attributes ->> 'layout' AS layout
FROM products_json;

The attributes column stores a JSON object. The ->> operator extracts the layout field as text. If the key is absent, the extraction returns NULL.

Note

Use JSONB when you need to query structured JSON data. For fields that are stable, essential to the data model, and frequently filtered or joined, ordinary relational columns may be a better choice.

12. Create a Complete Table

Now combine several data types in a single table.

The example below creates a simple product catalog. It uses a primary key, required text fields, an exact decimal price, a Boolean flag, a timestamp, and optional JSONB attributes.

The constraints are included to show how data types and table rules work together.

Create a product catalog

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
CREATE TABLE product_catalog (
    product_id INTEGER PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    description TEXT,
    price NUMERIC(10, 2) NOT NULL,
    is_available BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    attributes JSONB
);
 
INSERT INTO product_catalog (
    product_id,
    product_name,
    description,
    price,
    attributes
)
VALUES (
    1,
    'Keyboard',
    'A compact mechanical keyboard',
    2499.99,
    '{"layout": "US", "wireless": true}'
);
 
SELECT
    product_id,
    product_name,
    price,
    is_available,
    created_at,
    attributes
FROM product_catalog;

The primary key uniquely identifies each product. NOT NULL prevents missing values in required columns. DEFAULT supplies a value when the caller omits a column. NUMERIC stores the price using exact decimal arithmetic, and JSONB stores flexible attributes.

Note

A data type defines the kind of value a column can store. Constraints such as PRIMARY KEY, NOT NULL, UNIQUE, and CHECK enforce additional rules.

13. Common Data Type Selection Mistakes

**Mistake 1: Storing dates as strings**

A VARCHAR column can contain invalid dates and inconsistent formats. A DATE column allows PostgreSQL to validate calendar dates and perform date arithmetic.

**Mistake 2: Using floating-point values for exact monetary amounts**

REAL and DOUBLE PRECISION use approximate binary arithmetic. NUMERIC is generally preferable when exact decimal calculations are required.

**Mistake 3: Choosing SMALLINT without checking the range**

If the data exceeds the type's range, an insert or update can fail. Choose a type that supports the expected values and future growth.

**Mistake 4: Assuming TEXT is slower than VARCHAR**

In PostgreSQL, TEXT and VARCHAR without a length limit have essentially the same ordinary storage and performance behavior. A length limit should reflect a real requirement.

**Mistake 5: Assuming TIMESTAMPTZ stores the original time-zone name**

TIMESTAMPTZ represents an instant in time. Store a named time zone separately if it must be preserved.

**Mistake 6: Treating NULL as FALSE**

NULL means unknown or missing. Use IS NULL or IS NOT NULL to test for it.

**Mistake 7: Putting every attribute into JSONB**

JSONB is useful for flexible attributes, but it does not replace relational design, foreign keys, or constraints for every situation.

Note

Before choosing a type, ask three questions: What does the value mean? What values are valid? Which operations will the application perform on it?

14. Knowledge Check: MCQs

Try to answer each question before reading its explanation.

15. Guided Practice

Complete these exercises in your PostgreSQL practice database.

**Task 1:** Create a table named employees with employee_id, employee_name, salary, joining_date, and is_active.

Choose an appropriate data type for each column.

**Task 2:** Insert at least two employees with different salaries and joining dates.

**Task 3:** Write a query that returns employees whose salary is greater than 30000.

**Task 4:** Create a products table with a product ID, product name, price, and JSONB attributes.

**Task 5:** Insert a product whose attributes include a color and a wireless Boolean value.

**Task 6:** Retrieve the color from the JSONB attributes using ->>.

**Task 7:** Explain why NUMERIC is generally preferable to DOUBLE PRECISION for prices.

**Task 8:** Explain the difference between DATE and TIMESTAMPTZ.

Try to solve each task before viewing the reference solutions.

Start the exercise

sql

1
2
3
4
5
6
7
CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    salary NUMERIC(10, 2) NOT NULL,
    joining_date DATE NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE
);

The table uses INTEGER for the identifier, VARCHAR for the employee name, NUMERIC for the salary, DATE for the joining date, and BOOLEAN for the active flag.

16. Guided Practice: Solutions

Compare your answers with these reference solutions. They use the table created in Task 1 and demonstrate the data types covered in this lesson.

Insert, query, and use JSONB

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
47
48
49
50
51
52
-- Task 2
INSERT INTO employees (
    employee_id,
    employee_name,
    salary,
    joining_date,
    is_active
)
VALUES
    (1, 'Asha', 35000.00, '2026-01-15', TRUE),
    (2, 'Ravi', 28000.00, '2026-03-01', TRUE);
 
-- Task 3
SELECT employee_id, employee_name, salary
FROM employees
WHERE salary > 30000;
 
-- Task 4
CREATE TABLE practice_products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    attributes JSONB
);
 
-- Task 5
INSERT INTO practice_products (
    product_id,
    product_name,
    price,
    attributes
)
VALUES (
    1,
    'Headphones',
    1999.99,
    '{"color": "black", "wireless": true}'
);
 
-- Task 6
SELECT
    product_name,
    attributes ->> 'color' AS color
FROM practice_products;
 
-- Task 7:
-- NUMERIC supports exact decimal arithmetic.
-- DOUBLE PRECISION uses approximate binary arithmetic.
 
-- Task 8:
-- DATE represents a calendar date.
-- TIMESTAMPTZ represents an instant in time.

The employee query returns Asha because her salary is greater than 30000. The JSONB query extracts the color property as text. DATE and TIMESTAMPTZ serve different purposes, so choose based on whether the value represents a calendar date or a specific instant.

17. Interview Questions and Answers

**Q1. What is a data type in SQL?**

A data type defines the kind of values a column can store and the operations that are appropriate for those values.

**Q2. What is the difference between INTEGER and BIGINT?**

INTEGER uses 4 bytes and supports values from -2,147,483,648 to 2,147,483,647. BIGINT uses 8 bytes and supports a much larger range.

**Q3. Why should NUMERIC be used for financial values?**

NUMERIC supports exact decimal arithmetic within its defined precision and scale. Floating-point types use approximate binary representations, which can produce rounding differences.

**Q4. What is the difference between CHAR, VARCHAR, and TEXT in PostgreSQL?**

CHAR(n) is blank-padded to a fixed length. VARCHAR(n) stores variable-length strings with a maximum character length. TEXT stores variable-length text without a declared character limit.

**Q5. Is TEXT slower than VARCHAR in PostgreSQL?**

TEXT and VARCHAR without a length limit have essentially the same performance characteristics for ordinary use. Choose VARCHAR(n) when the length limit is a real data requirement.

**Q6. What is the difference between DATE and TIMESTAMP?**

DATE stores a calendar date. TIMESTAMP stores a date and time without time-zone interpretation. TIMESTAMPTZ represents an instant in time.

**Q7. Does TIMESTAMPTZ preserve the original time zone?**

No. PostgreSQL converts the input to an instant and displays it using the session time zone. Store the original named time zone separately if it must be retained.

**Q8. What is the difference between REAL and DOUBLE PRECISION?**

Both are approximate floating-point types. REAL uses 4 bytes and offers roughly 6 decimal digits of precision, while DOUBLE PRECISION uses 8 bytes and offers roughly 15 decimal digits.

**Q9. What is UUID, and why might an application use it?**

UUID is a 128-bit identifier. It is useful when identifiers need to be generated independently across services or systems without relying on a single sequential counter.

**Q10. What is the difference between JSON and JSONB?**

JSON stores the original JSON text, while JSONB stores a decomposed binary representation designed for efficient processing and indexing. JSONB does not preserve all original textual formatting details.

**Q11. What is the difference between NULL and FALSE?**

FALSE is a known Boolean value. NULL represents an unknown or missing value. Use IS NULL to test for NULL.

**Q12. Why are data types important for database performance?**

Data types influence storage requirements, valid operations, comparison behavior, and index size. A suitable type helps maintain data integrity and can reduce unnecessary storage or conversion work.

**Q13. Does declaring a column as INTEGER make it unique?**

No. A data type controls the kind of value the column stores. Use PRIMARY KEY or UNIQUE to enforce uniqueness.

**Q14. Should every flexible field be stored as JSONB?**

No. JSONB is useful for flexible document-like attributes, but stable fields and relationships often benefit from ordinary columns, constraints, and relational design.

18. Lesson Summary

In this lesson, you learned how PostgreSQL data types help define and validate the information stored in tables.

Remember these core rules:

- Use SMALLINT, INTEGER, or BIGINT according to the expected whole-number range.
- Use NUMERIC or DECIMAL for exact decimal calculations such as many monetary values.
- Use REAL and DOUBLE PRECISION when approximate floating-point arithmetic is appropriate.
- Use CHAR(n), VARCHAR(n), and TEXT according to your text requirements.
- Use DATE for calendar dates and TIME for times of day.
- Use TIMESTAMP when the value represents a date and time without time-zone interpretation.
- Use TIMESTAMPTZ when the value represents a specific instant.
- Use BOOLEAN for logical states, remembering that NULL is different from FALSE.
- Use UUID when a 128-bit identifier fits your application's requirements.
- Use JSONB for structured JSON data that benefits from database querying.
- Combine suitable data types with constraints such as PRIMARY KEY and NOT NULL.

The most important habit is to select a type based on the meaning and valid range of the data, not simply because a type is familiar.

In the next lessons, you can build on this foundation by creating tables with constraints and learning how to insert, update, and query records.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Using floating-point types for exact monetary values

Why it happens: REAL and DOUBLE PRECISION look like convenient numeric types, but they use approximate binary floating-point representation.

Incorrect Approach

sql

CREATE TABLE products (
    price DOUBLE PRECISION
);

Correct Solution

sql

CREATE TABLE products (
    price NUMERIC(10, 2) NOT NULL
);

Key takeaway: NUMERIC supports exact decimal arithmetic within the selected precision and scale. The application must still define appropriate rounding and currency rules.

#2 Storing dates as VARCHAR

Why it happens: Text values can appear easy to enter, but they do not provide the same date validation and date arithmetic as temporal types.

Incorrect Approach

sql

CREATE TABLE events (
    event_date VARCHAR(20)
);

Correct Solution

sql

CREATE TABLE events (
    event_date DATE
);

Key takeaway: DATE supports valid calendar dates, date comparisons, date functions, and chronological sorting.

#3 Assuming VARCHAR is always faster than TEXT

Why it happens: Some database systems and older tutorials present these types as having different performance characteristics.

Incorrect Approach

sql

Choosing VARCHAR(255) for every string because it is assumed to be faster than TEXT.

Correct Solution

sql

CREATE TABLE articles (
    title VARCHAR(200),
    body TEXT
);

Key takeaway: In PostgreSQL, VARCHAR without a length limit and TEXT have essentially the same ordinary performance characteristics. Use a length limit when it represents a real requirement.

#4 Choosing an integer type without considering its range

Why it happens: A smaller type can appear more efficient, but it may not accommodate future values.

Incorrect Approach

sql

CREATE TABLE counters (
    total SMALLINT
);

Correct Solution

sql

CREATE TABLE counters (
    total BIGINT
);

Key takeaway: Choose a type according to the expected values and growth. BIGINT is appropriate when a counter may exceed the INTEGER range, but it is unnecessary for every small quantity.

#5 Assuming TIMESTAMPTZ preserves the original time-zone name

Why it happens: The type name suggests that the original time zone is stored with the timestamp.

Incorrect Approach

sql

Expecting a TIMESTAMPTZ value to retain the exact original zone name supplied during insertion.

Correct Solution

sql

CREATE TABLE appointments (
    appointment_at TIMESTAMPTZ NOT NULL,
    time_zone_name TEXT
);

Key takeaway: TIMESTAMPTZ represents an instant in time. Store the named time zone separately when the original zone is important to the application.

#6 Treating NULL as FALSE

Why it happens: A missing Boolean value can be mistaken for a negative answer.

Incorrect Approach

sql

WHERE is_active = NULL

Correct Solution

sql

WHERE is_active IS NULL

Key takeaway: NULL represents an unknown or missing value, not FALSE. Use IS NULL and IS NOT NULL to test for it.

#7 Using JSONB for all application data

Why it happens: JSONB appears flexible because it can store attributes without defining a separate column for each one.

Incorrect Approach

sql

Putting every field, including stable identifiers and relationships, into one JSONB document without considering constraints.

Correct Solution

sql

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    attributes JSONB
);

Key takeaway: Keep stable, frequently queried fields in relational columns where appropriate. JSONB is useful for additional flexible attributes, not a universal replacement for relational modeling.

Ready for hands-on challenges?

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

Practice Arena →

Key takeaways

Every PostgreSQL table column has a data type that controls which values it can store.
INTEGER uses 4 bytes, SMALLINT uses 2 bytes, and BIGINT uses 8 bytes.
NUMERIC and DECIMAL support exact decimal arithmetic and are appropriate for many financial calculations.
REAL and DOUBLE PRECISION are approximate floating-point types.
In PostgreSQL, VARCHAR without a length limit and TEXT have essentially the same performance characteristics for ordinary use.
DATE stores a calendar date, TIME stores a time of day, TIMESTAMP stores a date and time without time-zone interpretation, and TIMESTAMPTZ represents an instant in time.
TIMESTAMPTZ does not preserve the original time-zone name supplied with an input value.
BOOLEAN represents TRUE, FALSE, or NULL.
UUID stores a 128-bit identifier, while JSONB stores JSON data in a format suitable for efficient processing and indexing.
Choosing an appropriate data type improves data integrity and makes queries easier to write correctly.

Was this lesson helpful?

Found an error? Report an issue