Character types (char, varchar, text) in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output length of stored values in different character types

Consider a table messages with three columns: fixed_char of type char(5), var_char of type varchar(5), and text_col of type text. The row inserted is ('hi', 'hi', 'hi'). What will be the result of the query that selects the length of each column's value?

PostgreSQL

CREATE TABLE messages (fixed_char char(5), var_char varchar(5), text_col text);
INSERT INTO messages VALUES ('hi', 'hi', 'hi');
SELECT length(fixed_char) AS len_fixed, length(var_char) AS len_var, length(text_col) AS len_text FROM messages;

A {"len_fixed": 2, "len_var": 2, "len_text": 2}

B {"len_fixed": 5, "len_var": 5, "len_text": 5}

C {"len_fixed": 5, "len_var": 2, "len_text": 2}

D {"len_fixed": 2, "len_var": 5, "len_text": 2}

💡 Hint

Remember that char(n) pads the string with spaces to the fixed length n.

The char(5) type pads the string 'hi' with 3 spaces, so its length is 5. The varchar(5) and text types store the string as is, so their lengths are 2.

🧠 Conceptual

intermediate

Difference in storage behavior between char and varchar

Which statement correctly describes the difference between char(n) and varchar(n) in PostgreSQL?

A <code>char(n)</code> and <code>varchar(n)</code> both store variable-length strings without padding.

B <code>char(n)</code> stores variable-length strings; <code>varchar(n)</code> stores fixed-length strings padded with spaces.

C <code>char(n)</code> and <code>varchar(n)</code> both store fixed-length strings padded with spaces.

D <code>char(n)</code> always stores fixed-length strings padded with spaces; <code>varchar(n)</code> stores variable-length strings without padding.

💡 Hint

Think about how the database treats the length of stored strings for each type.

char(n) pads strings with spaces to always have length n. varchar(n) stores strings as they are, up to length n, without padding.

📝 Syntax

advanced

Which query will cause an error due to character type length limit?

Given a table users with a column username varchar(3), which INSERT statement will cause an error?

PostgreSQL

CREATE TABLE users (username varchar(3));

A INSERT INTO users VALUES ('abcd');

B INSERT INTO users VALUES ('abc');

C INSERT INTO users VALUES ('ab');

D INSERT INTO users VALUES ('a');

💡 Hint

Check the length of the string compared to the column's maximum length.

The varchar(3) column allows strings up to length 3. The string 'abcd' has length 4, which exceeds the limit and causes an error.

❓ optimization

advanced

Choosing the best character type for large text data

You need to store user comments that can be very long, sometimes thousands of characters. Which character type is best to use in PostgreSQL for this column?

A <code>char(1000)</code>

B <code>text</code>

C <code>varchar(1000)</code>

D <code>varchar(255)</code>

💡 Hint

Consider flexibility and performance for very long strings.

text type can store strings of any length efficiently without a fixed limit, making it ideal for very long text data.

🔧 Debug

expert

Why does this query return unexpected trailing spaces?

Given a table products with a column code char(4), the query SELECT code FROM products WHERE code = 'AB' returns no rows, but SELECT code FROM products WHERE code LIKE 'AB%' returns rows. Why?

A Because <code>char(4)</code> pads 'AB' with spaces, so equality fails but LIKE matches the prefix.

B Because <code>char(4)</code> trims trailing spaces automatically, causing LIKE to work but = to fail.

C Because <code>char(4)</code> stores only the first two characters, so 'AB' is incomplete.

D Because <code>char(4)</code> converts all values to uppercase, affecting equality.

💡 Hint

Think about how fixed-length char stores strings and how comparisons work.

char(4) pads stored strings with spaces to length 4. The string 'AB' is stored as 'AB '. The equality comparison with 'AB' fails because 'AB' is length 2 without spaces, but LIKE 'AB%' matches the prefix 'AB'.

Practice

1. Which PostgreSQL character type stores text with a fixed length, padding with spaces if the input is shorter?

easy

A. CHAR(n)

B. VARCHAR(n)

C. TEXT

D. INT

Solution

1. Step 1: Understand fixed-length storage

   CHAR(n) stores exactly n characters, padding with spaces if input is shorter.

2. Step 2: Compare with other types

   VARCHAR(n) stores variable length up to n, TEXT has no limit, INT is numeric.

3. Final Answer:

   CHAR(n) -> Option A

4. Quick Check:

   Fixed length with padding = CHAR(n) [OK]

Hint: Fixed length with padding means CHAR(n) [OK]

Common Mistakes:

• Confusing VARCHAR(n) as fixed length
• Thinking TEXT has length limit
• Choosing numeric types by mistake

2. Which of the following is the correct syntax to declare a variable-length character column with a maximum of 50 characters in PostgreSQL?

easy

A. CHAR(50)

B. VARCHAR(50)

C. TEXT(50)

D. STRING(50)

Solution

1. Step 1: Identify variable-length syntax

   VARCHAR(n) declares variable-length text with max n characters.

2. Step 2: Check other options

   CHAR(50) is fixed length, TEXT has no length limit, STRING is invalid in PostgreSQL.

3. Final Answer:

   VARCHAR(50) -> Option B

4. Quick Check:

   Variable length with max limit = VARCHAR(n) [OK]

Hint: Use VARCHAR(n) for variable length with max size [OK]

Common Mistakes:

• Using TEXT with length limit (invalid)
• Confusing CHAR as variable length
• Using non-existent STRING type

3. Consider the following table and insert statements:

CREATE TABLE users (name CHAR(5));
INSERT INTO users VALUES ('Tom');
SELECT name, LENGTH(name) FROM users;

What will be the output of the SELECT query?

medium

A. name = 'Tom ', length = 5

B. name = 'Tom', length = 3

C. name = 'Tom', length = 5

D. name = 'Tom ', length = 3

Solution

1. Step 1: Understand CHAR(5) behavior on insert

   CHAR(5) pads 'Tom' with 2 spaces to make length 5.

2. Step 2: LENGTH function counts all characters including spaces

   LENGTH('Tom ') returns 5 because spaces count.

3. Final Answer:

   name = 'Tom ', length = 5 -> Option A

4. Quick Check:

   CHAR pads spaces, LENGTH counts them [OK]

Hint: CHAR pads spaces; LENGTH counts all characters [OK]

Common Mistakes:

• Assuming LENGTH ignores trailing spaces
• Thinking CHAR stores variable length
• Confusing CHAR with VARCHAR behavior

4. You have a table with a column defined as VARCHAR(10). You try to insert a string of 15 characters. What will happen?

medium

A. The string is truncated to 10 characters and inserted

B. The string is padded with spaces to 15 characters

C. An error is raised and insertion fails

D. The string is inserted fully ignoring the limit

Solution

1. Step 1: Understand VARCHAR(n) length limit enforcement

   VARCHAR(10) limits input to 10 characters strictly.

2. Step 2: Behavior on exceeding length

   PostgreSQL raises an error if input exceeds declared length.

3. Final Answer:

   An error is raised and insertion fails -> Option C

4. Quick Check:

   Exceeding VARCHAR limit causes error [OK]

Hint: Exceeding VARCHAR(n) length causes error, no truncation [OK]

Common Mistakes:

• Assuming automatic truncation happens
• Thinking VARCHAR pads with spaces
• Believing PostgreSQL ignores length limits

5. You want to store user comments of any length without worrying about size limits. Which PostgreSQL data type should you choose and why?

hard

A. INT, because it stores numeric values efficiently

B. CHAR(255), because it fixes length and saves space

C. VARCHAR(255), because it limits length to 255 characters

D. TEXT, because it stores variable-length text with no size limit

Solution

1. Step 1: Identify requirement for unlimited length

   User comments can be very long, so no fixed or limited length is suitable.

2. Step 2: Compare data types for length limits

   TEXT stores variable-length text with no size limit, unlike CHAR or VARCHAR.

3. Step 3: Exclude irrelevant types

   INT is numeric and unsuitable for text storage.

4. Final Answer:

   TEXT, because it stores variable-length text with no size limit -> Option D

5. Quick Check:

   No size limit needed = TEXT [OK]

Hint: Use TEXT for unlimited length text storage [OK]

Common Mistakes:

• Choosing CHAR or VARCHAR with fixed limits
• Confusing numeric types with text
• Assuming VARCHAR can store unlimited text