Domain types for validation in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a domain type in PostgreSQL?

A domain type is a user-defined data type based on an existing data type with optional constraints to enforce validation rules.

beginner

How do domain types help with data validation?

Domain types allow you to define rules like NOT NULL, CHECK constraints, or specific formats once, and reuse them to ensure consistent validation across tables.

intermediate

Write the basic syntax to create a domain type that only accepts positive integers.

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

beginner

Can domain types be used as column data types in tables?

Yes, domain types can be used as column types in tables, and the constraints defined in the domain are automatically enforced on the column data.

beginner

What happens if you try to insert invalid data into a column using a domain type?

PostgreSQL rejects the insert or update operation and returns an error because the data violates the domain's constraints.

What is the main purpose of a domain type in PostgreSQL?

A To create a reusable data type with validation rules

B To store large binary data

C To define a new table

D To create a backup of the database

Domain types let you define a data type with constraints that can be reused for validation.

Which SQL keyword is used to create a domain type?

A CREATE TYPE

B CREATE DOMAIN

C CREATE TABLE

D CREATE CONSTRAINT

The correct syntax to create a domain type uses CREATE DOMAIN.

If a domain type has a CHECK constraint, when is it enforced?

A Only when the domain is created

B When the table is dropped

C When data is inserted or updated in columns using the domain

D Only during database backup

CHECK constraints on domains are enforced whenever data is inserted or updated in columns using that domain.

Can a domain type be based on another domain type?

A Yes, domains can be based on other domains

B No, domains must be based on built-in types only

C Only if the domain is temporary

D Only in PostgreSQL versions before 10

PostgreSQL allows domains to be based on other domains, inheriting their constraints.

What happens if you try to insert a NULL value into a domain with NOT NULL constraint?

A The database crashes

B The insert succeeds

C The NULL is converted to zero

D The insert fails with a constraint violation error

NOT NULL constraints on domains prevent NULL values, causing an error if violated.

Explain what a domain type is and how it helps with data validation in PostgreSQL.

Think about how you can create a custom data type with rules that apply everywhere.

Describe the steps and syntax to create a domain type that only accepts email addresses in PostgreSQL.

Use CREATE DOMAIN with a CHECK that tests the format of the input.