Domain types for validation in PostgreSQL - Step-by-Step Execution

Concept Flow - Domain types for validation

Define Domain with Base Type

↓

Add Validation Constraints

↓

Use Domain in Table Columns

↓

Insert Data

↓

Validation Checks Applied

↓

Data Stored

You define a domain with a base type and constraints, then use it in tables. When inserting data, PostgreSQL checks constraints and accepts or rejects the data.

Execution Sample

PostgreSQL

CREATE DOMAIN us_postal_code AS TEXT
  CHECK (VALUE ~ '^\d{5}(-\d{4})?$');

CREATE TABLE addresses (
  id SERIAL PRIMARY KEY,
  postal_code us_postal_code
);

INSERT INTO addresses (postal_code) VALUES ('12345');
INSERT INTO addresses (postal_code) VALUES ('ABCDE');

Defines a domain for US postal codes with a regex check, creates a table using it, then inserts one valid and one invalid postal code.

Execution Table

Step	Action	Input/Value	Validation Result	Outcome
1	Create domain us_postal_code	TEXT with regex check	Domain created	Domain ready for use
2	Create table addresses	id SERIAL, postal_code us_postal_code	Table created	Table ready for data
3	Insert postal_code '12345'	'12345'	Matches regex	Row inserted successfully
4	Insert postal_code 'ABCDE'	'ABCDE'	Does not match regex	Error: check constraint violation

💡 Execution stops on error at step 4 due to invalid postal code format

Variable Tracker

Variable	Start	After Step 3	After Step 4
postal_code	undefined	'12345' accepted	'ABCDE' rejected

Key Moments - 2 Insights

Why does the second insert fail even though the column type is TEXT?

Because the domain us_postal_code has a check constraint that requires the value to match a specific regex pattern. The value 'ABCDE' does not match, so PostgreSQL rejects it (see execution_table step 4).

Can I use the domain type in multiple tables?

Yes, once defined, the domain type can be used in any table column just like a regular data type, enforcing the same validation everywhere.

Visual Quiz - 3 Questions

Look at the execution_table, what happens at step 3 when inserting '12345'?

A The value fails validation and raises an error

B The value matches the regex and is inserted successfully

C The value is converted to a number before insertion

D The domain is redefined at this step

💡 Hint

Check the Validation Result and Outcome columns at step 3 in the execution_table

At which step does the check constraint cause an error?

A Step 4

B Step 2

C Step 3

D Step 1

💡 Hint

Look at the Validation Result and Outcome columns for each step in the execution_table

If the regex in the domain was changed to allow letters, how would the execution_table change?

A Step 3 would fail instead

B The domain creation would fail at step 1

C Step 4 would succeed and insert the value

D No change would happen

💡 Hint

Think about how the Validation Result and Outcome depend on the regex check in the domain

Concept Snapshot

CREATE DOMAIN name AS base_type
  CHECK (condition);
Use domain as column type.
PostgreSQL enforces constraints on insert/update.
Invalid data causes errors, valid data stores normally.

Full Transcript

This visual execution shows how PostgreSQL domain types work for validation. First, a domain is created with a base type and a check constraint using a regex pattern. Then a table is created using this domain as a column type. When inserting data, PostgreSQL checks if the value matches the domain's constraint. If it matches, the row is inserted successfully. If not, an error is raised and insertion fails. This ensures data validity at the database level. Domains can be reused in multiple tables for consistent validation.

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