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.