Integer types (smallint, integer, bigint) in PostgreSQL - Step-by-Step Execution

Concept Flow - Integer types (smallint, integer, bigint)

Choose integer type

↓

smallint: 2 bytes, range -32768 to 32767

↓

integer: 4 bytes, range -2147483648 to 2147483647

↓

bigint: 8 bytes, range -9223372036854775808 to 9223372036854775807

↓

Store value in chosen type

↓

Use value in queries/calculations

You pick one of three integer types based on size and range, then store and use values accordingly.

Execution Sample

PostgreSQL

CREATE TABLE numbers (
  small_num SMALLINT,
  int_num INTEGER,
  big_num BIGINT
);

INSERT INTO numbers VALUES (32000, 2000000000, 9000000000000000000);

Creates a table with three integer columns of different sizes and inserts values within their ranges.

Execution Table

Step	Action	Data Type	Value Inserted	Result
1	Create table with smallint column	smallint	N/A	Column created with 2 bytes storage
2	Create table with integer column	integer	N/A	Column created with 4 bytes storage
3	Create table with bigint column	bigint	N/A	Column created with 8 bytes storage
4	Insert 32000 into smallint	smallint	32000	Success, within range (-32768 to 32767)
5	Insert 2000000000 into integer	integer	2000000000	Success, within range (-2147483648 to 2147483647)
6	Insert 9000000000000000000 into bigint	bigint	9000000000000000000	Error: value out of range for bigint
7	Attempt to insert 40000 into smallint	smallint	40000	Error: value out of range for smallint
8	Attempt to insert 3000000000 into integer	integer	3000000000	Error: value out of range for integer
9	Insert -9000000000000000000 into bigint	bigint	-9000000000000000000	Error: value out of range for bigint
10	Query all values	N/A	N/A	Returns inserted rows with correct values

💡 Execution stops after all inserts and queries; errors occur when values exceed type ranges.

Variable Tracker

Variable	Start	After Step 4	After Step 5	After Step 6	After Step 9	Final
small_num	NULL	32000	32000	32000	32000	32000
int_num	NULL	NULL	2000000000	2000000000	2000000000	2000000000
big_num	NULL	NULL	NULL	NULL	NULL	NULL

Key Moments - 3 Insights

Why does inserting 40000 into smallint cause an error?

Because smallint can only store values from -32768 to 32767, and 40000 is outside this range (see execution_table step 7).

Can integer store values larger than 2 billion?

No, integer's max is 2147483647, so values like 3000000000 cause errors (see execution_table step 8).

Why use bigint instead of integer or smallint?

Bigint stores much larger numbers (8 bytes), useful when values exceed integer or smallint ranges (see execution_table steps 6 and 9).

Visual Quiz - 3 Questions

Look at the execution table, what value is stored in small_num after step 4?

A 32000

B 40000

C NULL

D -32768

💡 Hint

Check the 'Value Inserted' and 'Result' columns at step 4 in the execution_table.

At which step does inserting a value cause an error due to exceeding integer range?

A Step 5

B Step 8

C Step 7

D Step 9

💡 Hint

Look for 'Error' in the 'Result' column related to integer type in execution_table.

If you tried to insert 50000 into smallint, what would happen?

A It would store as 32767

B It would store successfully

C It would cause an error

D It would store as NULL

💡 Hint

Refer to the error shown in execution_table step 7 for out-of-range smallint values.

Concept Snapshot

Integer types in PostgreSQL:
- smallint: 2 bytes, range -32768 to 32767
- integer: 4 bytes, range -2147483648 to 2147483647
- bigint: 8 bytes, range -9223372036854775808 to 9223372036854775807
Choose type based on needed range to avoid errors.

Full Transcript

This visual execution shows how PostgreSQL handles integer types: smallint, integer, and bigint. Each type has a specific size and range. We create a table with columns of each type and insert values within their ranges successfully. Attempts to insert values outside the allowed range cause errors. The variable tracker shows how values are stored step by step. Key moments clarify why errors happen when values exceed type limits. The quiz tests understanding of value storage and errors related to integer types.

Practice

1. Which PostgreSQL integer type is best suited for storing very large whole numbers?

easy

A. bigint

B. smallint

C. integer

D. decimal

Solution

1. Step 1: Understand integer types and their sizes

   smallint stores small numbers, integer stores medium-sized numbers, and bigint stores very large numbers.

2. Step 2: Identify the type for very large whole numbers

   Since the question asks for very large whole numbers, bigint is the correct choice.

3. Final Answer:

   bigint -> Option A

4. Quick Check:

   Very large whole numbers = bigint [OK]

Hint: Biggest integer type is bigint for very large numbers [OK]

Common Mistakes:

• Confusing smallint with bigint
• Choosing decimal which is for decimals, not integers
• Picking integer which is medium size

2. Which of the following is the correct way to declare a column named age as a 2-byte integer in PostgreSQL?

easy

A. age INT2(2);

B. age INTEGER(2);

C. age BIGINT(2);

D. age SMALLINT;

Solution

1. Step 1: Recall PostgreSQL integer type syntax

   PostgreSQL uses SMALLINT for 2-byte integers. The syntax is simply column_name SMALLINT;.

2. Step 2: Evaluate each option

   age SMALLINT; uses correct syntax. The other options incorrectly use size in parentheses (like INT2(2), INTEGER(2), BIGINT(2)), which is invalid for integer types in PostgreSQL.

3. Final Answer:

   age SMALLINT; -> Option D

4. Quick Check:

   2-byte integer = SMALLINT syntax [OK]

Hint: Use SMALLINT keyword for 2-byte integers, no size in parentheses [OK]

Common Mistakes:

• Adding size in parentheses like INTEGER(2)
• Using BIGINT for small numbers
• Specifying size like INT2(2) for integer types

3. What will be the result of this PostgreSQL query?

CREATE TABLE test_numbers (num SMALLINT); INSERT INTO test_numbers VALUES (40000); SELECT * FROM test_numbers;

medium

A. 40000

B. Error: integer out of range

C. 0

D. NULL

Solution

1. Step 1: Understand SMALLINT range

   SMALLINT can store values from -32768 to 32767 only.

2. Step 2: Check inserted value against range

   40000 is greater than 32767, so inserting it causes an error.

3. Final Answer:

   Error: integer out of range -> Option B

4. Quick Check:

   40000 > SMALLINT max 32767 = error [OK]

Hint: Check value fits type range; else error occurs [OK]

Common Mistakes:

• Assuming value truncates silently
• Expecting 0 or NULL instead of error
• Confusing SMALLINT range with INTEGER

4. You wrote this table definition:

CREATE TABLE sales (id INTEGER, amount SMALLINT);

But when inserting a large amount like 40000, you get an error. How do you fix it?

medium

A. Change amount column type to INTEGER

B. Change id column type to SMALLINT

C. Insert smaller values only

D. Change amount column type to tinyint

Solution

1. Step 1: Identify cause of error

   Amount column is SMALLINT which maxes at 32767, so 40000 is too large.

2. Step 2: Choose appropriate type to hold 40000

   INTEGER can hold values up to 2,147,483,647, so changing amount to INTEGER fixes the error.

3. Final Answer:

   Change amount column type to INTEGER -> Option A

4. Quick Check:

   40000 fits INTEGER but not SMALLINT [OK]

Hint: Use INTEGER for values larger than 32767 [OK]

Common Mistakes:

• Changing unrelated column type
• Trying to insert smaller values only
• Choosing tinyint which is a MySQL type, not PostgreSQL

5. You need to store user IDs that can be very large numbers, possibly exceeding 2 billion. Which PostgreSQL integer type should you choose and why?

hard

A. smallint because it uses less storage

B. integer because it supports up to 2 billion

C. bigint because it supports numbers larger than 2 billion

D. serial because it auto-increments

Solution

1. Step 1: Understand size limits of integer types

   smallint max is 32767, integer max is about 2,147,483,647, bigint supports much larger numbers.

2. Step 2: Match requirement to type

   User IDs may exceed 2 billion, so integer is insufficient. bigint is needed to store larger values.

3. Final Answer:

   bigint because it supports numbers larger than 2 billion -> Option C

4. Quick Check:

   IDs > 2 billion need bigint [OK]

Hint: Use bigint for numbers larger than 2 billion [OK]

Common Mistakes:

• Choosing smallint for large IDs
• Assuming integer supports >2 billion
• Confusing serial with integer size