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'.