Integer types (smallint, integer, bigint) in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of integer type casting in PostgreSQL

What is the output of this query in PostgreSQL?

SELECT 32767::smallint + 1::smallint AS result;

PostgreSQL

SELECT 32767::smallint + 1::smallint AS result;

A 32768

B NULL

C -32768

D Overflow error

💡 Hint

Remember the range of smallint is from -32768 to 32767.

Adding 1 to the maximum smallint value causes an overflow error in PostgreSQL; it does not wrap around.

❓ query_result

intermediate

Result of integer multiplication exceeding integer range

What is the output of this query?

SELECT 50000 * 50000 AS result;

Assuming default integer type in PostgreSQL.

PostgreSQL

SELECT 50000 * 50000 AS result;

A NULL

B Overflow error

C 2500000000

D -1794967296

💡 Hint

Default integer is 4 bytes with range -2147483648 to 2147483647.

The multiplication result exceeds the 4-byte integer range and causes an overflow error in PostgreSQL.

📝 Syntax

advanced

Identify the correct syntax for declaring a bigint column

Which of the following SQL statements correctly creates a table with a bigint column named 'id' in PostgreSQL?

A CREATE TABLE users (id BIG INT);

B CREATE TABLE users (id BIGINTEGER);

C CREATE TABLE users (id BIGINT);

D CREATE TABLE users (id BIGINTGER);

💡 Hint

Check the exact spelling of the bigint type.

The correct PostgreSQL type is BIGINT. Other options have spelling or spacing errors causing syntax errors.

❓ optimization

advanced

Choosing the best integer type for storing ages

You want to store ages of people in a PostgreSQL table. Ages range from 0 to 120. Which integer type is the most efficient choice?

A smallint

B integer

C bigint

D numeric

💡 Hint

Consider the range and storage size of each type.

smallint uses 2 bytes and can store values from -32768 to 32767, which covers 0 to 120 efficiently.

🧠 Conceptual

expert

Understanding integer overflow behavior in PostgreSQL

What happens when you insert a value larger than 2147483647 into an integer column in PostgreSQL without explicit type casting?

A PostgreSQL raises an error and rejects the insert.

B The value is truncated to 2147483647 without error.

C The value wraps around to a negative number silently.

D PostgreSQL automatically converts the column to bigint.

💡 Hint

Think about how PostgreSQL handles out-of-range values for integer columns.

PostgreSQL raises an error when a value exceeds the integer range; it does not truncate, wrap, or auto-convert 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