Character types (char, varchar, text) in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a column that stores fixed-length character data of length 10.

PostgreSQL

CREATE TABLE users (username [1](10));

Drag options to blanks, or click blank then click option'

ATEXT

BVARCHAR

CCHAR

DINT

Attempts:

3 left

💡 Hint

Common Mistakes

Using VARCHAR instead of CHAR for fixed-length data.

Using INT which is for numbers, not characters.

The CHAR(10) type stores fixed-length character data of length 10.

2fill in blank

medium

Complete the code to create a column that stores variable-length character data up to 255 characters.

PostgreSQL

CREATE TABLE products (description [1](255));

Drag options to blanks, or click blank then click option'

AVARCHAR

BTEXT

CCHAR

DBOOLEAN

Attempts:

3 left

💡 Hint

Common Mistakes

Using CHAR which always uses fixed space.

Using TEXT which has no length limit.

The VARCHAR(255) type stores variable-length character data up to 255 characters.

3fill in blank

hard

Fix the error in the code to create a column for long text without length limit.

PostgreSQL

CREATE TABLE articles (content [1]);

Drag options to blanks, or click blank then click option'

ATEXT

BCHAR

CVARCHAR

DINT

Attempts:

3 left

💡 Hint

Common Mistakes

Specifying a length with TEXT which is not allowed.

Using INT which is for numbers.

The TEXT type stores long text without a length limit, so no number is needed.

4fill in blank

hard

Fill both blanks to create a table with a fixed-length code and a variable-length name.

PostgreSQL

CREATE TABLE items (code [1](5), name [2](100));

Drag options to blanks, or click blank then click option'

ACHAR

BTEXT

CVARCHAR

DINT

Attempts:

3 left

💡 Hint

Common Mistakes

Using TEXT for code which should be fixed length.

Using INT for character data.

CHAR(5) is for fixed-length codes, and VARCHAR(100) is for variable-length names.

5fill in blank

hard

Fill all three blanks to create a table with a fixed-length ID, a variable-length title, and a long text body.

PostgreSQL

CREATE TABLE posts (id [1](8), title [2](200), body [3]);

Drag options to blanks, or click blank then click option'

ACHAR

BVARCHAR

CTEXT

DINT

Attempts:

3 left

💡 Hint

Common Mistakes

Using INT for character fields.

Using TEXT with length specification.

CHAR(8) for fixed-length ID, VARCHAR(200) for variable-length title, and TEXT for long body text.

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