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.