Domain types for validation in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of domain constraint violation

Consider the following domain definition and insert statement in PostgreSQL:

CREATE DOMAIN positive_int AS INTEGER CHECK (VALUE > 0);
INSERT INTO test_table (id) VALUES (-5);

What will be the result of the insert statement if id is of type positive_int?

PostgreSQL

CREATE DOMAIN positive_int AS INTEGER CHECK (VALUE > 0);
CREATE TABLE test_table (id positive_int);
INSERT INTO test_table (id) VALUES (-5);

A The insert succeeds and stores -5.

B The insert succeeds but stores NULL instead of -5.

C The insert fails with a check constraint violation error.

D The insert fails with a syntax error.

💡 Hint

Think about what the CHECK constraint on the domain does when a value does not meet the condition.

The domain positive_int restricts values to integers greater than 0. Inserting -5 violates this CHECK constraint, so PostgreSQL raises an error and rejects the insert.

🧠 Conceptual

intermediate

Purpose of domain types in PostgreSQL

What is the main purpose of using domain types in PostgreSQL?

A To speed up query execution by indexing columns automatically.

B To create reusable custom data types with built-in validation rules.

C To store large binary objects efficiently.

D To define user roles and permissions.

💡 Hint

Think about how domains help keep data consistent across tables.

Domains allow you to define a custom data type with constraints like CHECK rules. This helps enforce consistent validation rules wherever the domain is used.

📝 Syntax

advanced

Correct domain creation syntax

Which of the following SQL statements correctly creates a domain named email_addr that stores text and validates that the value contains an '@' character?

A CREATE DOMAIN email_addr TYPE TEXT CHECK (VALUE CONTAINS '@');

B CREATE DOMAIN email_addr AS TEXT WHERE VALUE LIKE '%@%';

C CREATE DOMAIN email_addr AS VARCHAR CHECK (VALUE LIKE '@');

D CREATE DOMAIN email_addr AS TEXT CHECK (VALUE LIKE '%@%');

💡 Hint

Remember the correct keywords for domain creation and how to write a CHECK constraint.

Option D uses the correct syntax: CREATE DOMAIN name AS base_type CHECK (condition). The condition uses LIKE with wildcards to check for '@'.

❓ optimization

advanced

Performance impact of domains with complex checks

If a domain has a complex CHECK constraint involving multiple function calls, what is the likely impact on database performance when inserting rows using this domain?

A Insert performance may decrease because the CHECK constraint is evaluated for each row.

B Insert performance improves because the domain caches results.

C There is no impact on performance because CHECK constraints are ignored during inserts.

D Insert performance depends only on the base data type, not the domain.

💡 Hint

Consider what happens when PostgreSQL validates data against constraints.

CHECK constraints are evaluated on every insert or update. Complex checks with functions add overhead, slowing down inserts.

🔧 Debug

expert

Diagnosing domain constraint error

A developer created this domain:

CREATE DOMAIN phone_number AS TEXT CHECK (LENGTH(VALUE) = 10 AND VALUE ~ '^[0-9]+$');

They try to insert '123-456-7890' but get a constraint violation error. Why?

A The value contains dashes which violate the regular expression constraint.

B The LENGTH function does not work on TEXT types.

C The domain does not allow NULL values by default.

D The regular expression is invalid and causes a syntax error.

💡 Hint

Check what the regular expression requires and what the inserted value contains.

The regex requires only digits 0-9 with no other characters. The inserted value has dashes, so it fails the CHECK constraint.

Practice

1. What is the main purpose of using domain types in PostgreSQL?

easy

A. To speed up query execution by indexing

B. To create custom data types with automatic validation rules

C. To store large binary data efficiently

D. To create temporary tables for session use

Solution

1. Step 1: Understand what domain types are

   Domain types are user-defined data types that include validation rules to ensure data quality.

2. Step 2: Identify the main purpose

   The main purpose is to enforce rules automatically when data is inserted or updated, keeping data clean.

3. Final Answer:

   To create custom data types with automatic validation rules -> Option B

4. Quick Check:

   Domain types = custom types with validation [OK]

Hint: Domains add rules to types, not speed or storage [OK]

Common Mistakes:

• Confusing domains with indexing
• Thinking domains store large files
• Mixing domains with temporary tables

2. Which of the following is the correct syntax to create a domain named positive_int that only allows positive integers?

easy

A. CREATE DOMAIN positive_int AS integer CHECK (VALUE > 0);

B. CREATE DOMAIN positive_int TYPE integer WHERE VALUE > 0;

C. CREATE DOMAIN positive_int AS integer VALIDATE (VALUE > 0);

D. CREATE DOMAIN positive_int AS integer IF VALUE > 0;

Solution

1. Step 1: Recall the correct syntax for domain creation

   The syntax is: CREATE DOMAIN name AS base_type CHECK (condition);

2. Step 2: Match the syntax with options

   CREATE DOMAIN positive_int AS integer CHECK (VALUE > 0); matches the correct syntax with CHECK and VALUE keyword.

3. Final Answer:

   CREATE DOMAIN positive_int AS integer CHECK (VALUE > 0); -> Option A

4. Quick Check:

   CREATE DOMAIN ... AS ... CHECK(...) [OK]

Hint: Use CHECK with VALUE keyword in domain creation [OK]

Common Mistakes:

• Using TYPE instead of AS
• Using WHERE or VALIDATE instead of CHECK
• Missing parentheses around condition

3. Given the domain creation:

CREATE DOMAIN us_zipcode AS varchar(5) CHECK (VALUE ~ '^[0-9]{5}$');

What will happen if you try to insert '1234a' into a table column of type us_zipcode?

medium

A. The insert will succeed because it's a varchar

B. The insert will cause a syntax error

C. The insert will succeed but store NULL instead

D. The insert will fail due to the CHECK constraint violation

Solution

1. Step 1: Understand the domain's CHECK constraint

   The domain requires the value to match exactly 5 digits using a regular expression.

2. Step 2: Check the value '1234a' against the regex

   '1234a' contains a letter, so it does not match the pattern of 5 digits.

3. Final Answer:

   The insert will fail due to the CHECK constraint violation -> Option D

4. Quick Check:

   Regex check fails = insert rejected [OK]

Hint: Regex in CHECK rejects invalid patterns [OK]

Common Mistakes:

• Assuming varchar allows any string without checks
• Thinking invalid data stores as NULL automatically
• Confusing constraint violation with syntax error

4. You have this domain:

CREATE DOMAIN non_empty_text AS text CHECK (LENGTH(VALUE) > 0);

Which of the following INSERT statements will cause an error when inserting into a column of type non_empty_text?

medium

A. INSERT INTO table_name (col) VALUES ('');

B. INSERT INTO table_name (col) VALUES ('world');

C. INSERT INTO table_name (col) VALUES (' ');

D. INSERT INTO table_name (col) VALUES (' hello ');

Solution

1. Step 1: Understand the CHECK condition

   The domain requires text length to be greater than 0, so empty strings fail. Whitespace-only strings pass as they have length > 0.

2. Step 2: Evaluate each insert value

   INSERT INTO table_name (col) VALUES (''); inserts an empty string '', length 0, violating the check.

3. Final Answer:

   INSERT with empty string '' causes error -> Option A

4. Quick Check:

   Empty string fails length > 0 check [OK]

Hint: Empty strings fail length > 0 check [OK]

Common Mistakes:

• Thinking whitespace strings fail (they pass)
• Assuming empty string is allowed
• Confusing syntax errors with constraint errors

5. You want to create a domain rating that stores integers from 1 to 5 inclusive. Which domain definition correctly enforces this range and can be used in multiple tables?

hard

A. CREATE DOMAIN rating AS integer WHERE (VALUE BETWEEN 1 AND 5);

B. CREATE DOMAIN rating AS integer CHECK (VALUE > 1 AND VALUE < 5);

C. CREATE DOMAIN rating AS integer CHECK (VALUE >= 1 AND VALUE <= 5);

D. CREATE DOMAIN rating AS integer CHECK (VALUE IN (1, 2, 3, 4));

Solution

1. Step 1: Understand the required range

   The rating must include 1 and 5, so boundaries are inclusive.

2. Step 2: Analyze each CHECK condition

   The option using WHERE instead of CHECK has invalid syntax.
   CREATE DOMAIN rating AS integer CHECK (VALUE > 1 AND VALUE < 5); excludes 1 and 5.
   CREATE DOMAIN rating AS integer CHECK (VALUE >= 1 AND VALUE <= 5); uses >= 1 AND <= 5, correctly including boundaries.
   CREATE DOMAIN rating AS integer CHECK (VALUE IN (1, 2, 3, 4)); uses IN list which misses 5.

3. Step 3: Choose the best option for clarity and standard usage

   CREATE DOMAIN rating AS integer CHECK (VALUE >= 1 AND VALUE <= 5); is clear, standard, and commonly used for range checks.

4. Final Answer:

   CREATE DOMAIN rating AS integer CHECK (VALUE >= 1 AND VALUE <= 5); -> Option C

5. Quick Check:

   Inclusive range uses >= and <= [OK]

Hint: Use >= and <= for inclusive numeric domain checks [OK]

Common Mistakes:

• Using > and < excludes boundary values
• Using WHERE instead of CHECK
• Incomplete IN list misses values