Developers' taste for type annotations doesn't reach all the way down to the database layer. The SQL standard and a PostgreSQL feature come to the rescue!

Data type annotation in languages that don't require it has become fashionable. A type hint might help you catch the common Python bug of iterating over every character in a string when your code expected a list of strings. Type signatures certainly do replace free-form documentation with formal metadata that human developers, automated source code analysis tools, and artificial intelligence systems can share.

Syntax: Start with re-usable types in SQL

It surprises me that developers' new taste for types doesn't reach all the way down to the database layer. Yes, every stored column has a type, but the popular SQL base types are blunt, each one used for many purposes. Literal values without a type specification slip into queries, for use in comparisons and calculations. Do you bother to cast the results of calculations (or of calls to aggregate functions) to known types when you're just outputting the results, not storing them?

Standard SQL provides domain , which gives a data type specification a name, making the specification (and associated column constraints, if you like) re-usable.

SQL source code in this article is intended for use with PostgreSQL 18, in its psql utility. The code is also available at github.com/sqlxpert/data-types-sql-domain-postgresql-operator .

-- should be  place_name etc. but screen width is limited!
create domain place  as varchar 
                     -- varchar(maximum length) except in postgresql
  constraint place_not_null       check (value is not null)

  constraint place_length_minimum check ( 2 <= length(value)      )
  constraint place_length_maximum check (      length(value) <= 25)
  -- potentially easier to increase allowed length by editing a constraint

  constraint place_space_trimmed  check (value = trim(both from value))
  constraint place_lowercase      check (value = lower(value))
;
create domain county as place;
create domain city   as place;

-- postgresql psql ...

\dDx

-- List of domains
-- -[ RECORD 1 ]--------------------------------------------------------------
-- Schema    | example
-- Name      | city
-- Type      | place
-- Check     | 
-- -[ RECORD 2 ]--------------------------------------------------------------
-- Schema    | example
-- Name      | county
-- Type      | place
-- Check     | 
-- -[ RECORD 3 ]--------------------------------------------------------------
-- Schema    | example
-- Name      | place
-- Type      | character varying
-- Check     | CHECK (length(VALUE::text) <= 25)
--             CHECK (2 <= length(VALUE::text))
--             CHECK (VALUE::text = lower(VALUE::text))
--             CHECK (VALUE IS NOT NULL) CHECK (VALUE::text = TRIM(BOTH FROM VALUE))

Semantics: Add custom operators in PostgreSQL

People often confuse the name of the place where I live. The City of Alameda is an island in the San Francisco Bay, and it's in Alameda County. Sadly, domain in the SQL standard isn't sophisticated enough to resolve this sort of ambiguity. The behavior of the underlying type prevails.

That is, unless you use PostgreSQL, which has a rich type system and supports user-defined operators! Here's how easy it is to override (specialize) the equality operator:

create function county_city_never_equal(county, city)
  returns boolean

  immutable
  leakproof
  strict
  security invoker
  parallel safe

  language sql
  return false
;

create function county_city_never_equal(city, county)
  returns boolean

  immutable
  leakproof
  strict
  security invoker
  parallel safe

  language sql
  return false  -- more complex? call the other function with inputs reversed!
;

create operator   = (
   commutator =   =
  ,leftarg    = county
  ,rightarg   = city
  ,function   = county_city_never_equal
);

create operator   = (
   commutator =   =
  ,leftarg    = city
  ,rightarg   = county
  ,function   = county_city_never_equal
);

-- postgresql psql ...

\df

--                                  List of functions
--  Schema  |          Name           | Result data type | Argument data types | Type 
-- ---------+-------------------------+------------------+---------------------+------
--  example | county_city_never_equal | boolean          | city, county        | func
--  example | county_city_never_equal | boolean          | county, city        | func

\do

--                               List of operators
--  Schema  | Name | Left arg type | Right arg type | Result type | Description 
-- ---------+------+---------------+----------------+-------------+-------------
--  example | =    | city          | county         | boolean     | 
--  example | =    | county        | city           | boolean     |

Now, comparison semantics for county and city names are correct.

select
   cast('alameda' as varchar)  = cast('alameda' as varchar) as string_eq
  ,cast('alameda' as county )  = cast('alameda' as county ) as county_eq
  ,cast('alameda' as city   )  = cast('alameda' as city   ) as city_eq
  ,cast('alameda' as county )  = cast('alameda' as city   ) as county_eq_city
  ,cast('alameda' as city   )  = cast('alameda' as county ) as city_eq_county
;

--  string_eq | county_eq | city_eq | county_eq_city | city_eq_county 
-- -----------+-----------+---------+----------------+----------------
--  t         | t         | t       | f              | f

A different meaning for identical character strings is a good way to introduce the combination of domain in standard SQL with custom operators in PostgreSQL, but let's consider a more sophisticated example...

A financial and mathematical application for specific data types

In a mortgage lender's database, we might represent principal, accrued interest, payments, and balances — money values, all — with the numeric type. (Some SQL database management systems support a money type, but it's non-standard.) Let's agree on a total "precision" of 14 significant digits, including a "scale" of 2 digits for the fractional part. We can represent up to $999,999,999,999.99 , which is $1 trillion less 1¢ . To make this quantity meaningful, it's approximately half the budget of the United States federal government as of September 1, 2026.

We should represent interest rates with the numeric type, too. We avoid a floating point type because the rounding errors that occur when base-10 (decimal) fractions are converted to base-2 (binary) become significant when we repeat calculations with small fractions. Let's agree on a "precision" and a "scale" of 5 digits. We can represent up to 0.99999 , which is 99.999% . The smallest increment we can represent is one thousandth of one percentage point. Given $1,000.00 , that would be only $0.01 .

create domain money_portable as numeric(14, 2)
  constraint money_portable_not_null check (value is not null)
;

create domain interest_rate  as numeric( 5, 5)
  constraint interest_rate_not_null  check (value is not null)

  constraint interest_rate_minimum   check (0 <= value)
;

Given the county versus city name confusion explained above, we know that 5¢ and 5% interest mean different things. Even though both quantities may be represented as 0.05 , they are not equal.

Specialize the equals operator for money and interest 
create function money_interest_never_equal(money_portable, interest_rate)
  returns boolean

  immutable
  leakproof
  strict
  security invoker
  parallel safe

  language sql
  return false
;

create function money_interest_never_equal(interest_rate, money_portable)
  returns boolean

  immutable
  leakproof
  strict
  security invoker
  parallel safe

  language sql
  return false
;

create operator   = (
   commutator =   =
  ,leftarg    = money_portable
  ,rightarg   = interest_rate
  ,function   = money_interest_never_equal
);

create operator   = (
   commutator =   =
  ,leftarg    = interest_rate
  ,rightarg   = money_portable
  ,function   = money_interest_never_equal
);

select
   cast(  0.05 as numeric(14, 2)) = cast(  0.05 as numeric( 5, 5)) as numeric_eq_numeric
  ,cast(  0.05 as money_portable) = cast(  0.05 as interest_rate ) as money_eq_interest
  ,cast(  0.05 as interest_rate ) = cast(  0.05 as money_portable) as interest_eq_money
;

--  numeric_eq_numeric | money_eq_interest | interest_eq_money
-- --------------------+-------------------+-------------------
--  t                  | t                 | t

That was easy. What about calculations? Though it makes sense to add money values, it makes no sense to add a money value and an interest rate.

-- $100.00 plus 5¢ ($0.05) or 5% interest (0.05)
select
   cast(100.00 as numeric(14, 2)) + cast(  0.05 as numeric(14, 2)) as money_plus_money
  ,cast(100.00 as numeric(14, 2)) + cast(  0.05 as numeric( 5, 5)) as money_plus_interest_before
;

--  money_plus_money | money_plus_interest_before
-- ------------------+----------------------------
--            100.05 |                  100.05000

We can block the second, meaningless calculation by overriding the addition operator. The code stays simple relative to the safety and clarity it creates for users.

create or replace function add_money_interest(
     money_portable
    ,
     interest_rate
  )
  returns void

  immutable
  leakproof
  strict
  security invoker
  parallel safe

  as $$
    begin
      raise exception datatype_mismatch;
    end;
  $$
  language plpgsql
;

create  or replace function add_money_interest(
     interest_rate
    ,
     money_portable
  )
  returns void

  immutable
  leakproof
  strict
  security invoker
  parallel safe

  as $$
    begin
      raise exception datatype_mismatch;
    end;
  $$
  language plpgsql
;

create operator +(
   commutator =  +
  ,leftarg    = money_portable
  ,rightarg   = interest_rate
  ,function   = add_money_interest
);

create operator +(
  commutator =  +
  ,leftarg    = interest_rate
  ,rightarg   = money_portable
  ,function   = add_money_interest
);

Now, the DBMS refuses to add a money value and an interest rate. Imagine column references (the table definition specifies each column's type) instead of literal values (which we cast to specific types to make the examples work). No matter where requests come from (application, reporting system, etc.), calculations performed at the database level will be checked for correct data types.

-- $100.00 plus 5¢ ($0.05) or 5% interest (0.05)
select
   cast(100.00 as money_portable) + cast(  0.05 as money_portable) as money_plus_money
;

--  money_plus_money 
-- ------------------
--            100.05

select
   cast(100.00 as money_portable) + cast(  0.05 as interest_rate ) as money_plus_interest
;

-- ERROR:  datatype_mismatch
-- CONTEXT:  PL/pgSQL function add_money_portable_and_interest_rate(money_portable,interest_rate) line 3 at RAISE

Check with reversed inputs (important!) 

select
   cast(  0.05 as interest_rate ) + cast(100.00 as money_portable) as interest_plus_money
;

-- ERROR:  datatype_mismatch
-- CONTEXT:  PL/pgSQL function add_money_interest(interest_rate,money_portable) line 3 at RAISE

Why bother with specific data types in the database?

Bringing more specific data types to the database might seem esoteric, but defining a domain reduces DDL (data definition language) source code duplication while promoting consistency, and the small cost of defining operators can prevent very expensive calculation errors.

MySQL doesn't support domain , let alone custom operators. Some other SQL DBMSes support domain , but few developers and few database professionals take advantage. Fewer still pair domain with PostgreSQL's custom operator feature. Because examples are rare, your large language model isn't likely to suggest these solutions. Where LLM training data are limited, your professional knowledge and discretion shine.

Going deeper: questions

  1. Based on the county versus city name example, what could be improved in PostgreSQL's create operator syntax?
  2. Should cast('san francisco' as county) = cast('san francisco' as city ) really be false ? Why, or why not? Hint: Either answer can be defended. See the May 22, 2026 "Meaning and the Data Model" Database Debunkings blog article, in which Fabian Pascal explains a quote from Codd, the creator of the Relational Model.
  3. If you defined a custom multiplication operator for money values and interest rates, what should the return type be? What problem presents itself, depending whether you're performing an intermediate or final calculation?
  4. Based on the money value versus interest rate example, what refinement could be made to PostgreSQL's operator system to make domain more useful? Hint: PostgreSQL's built-in but non-standard money type happens to prevent more kinds of potentially meaningless addition operations than money_portable can. See the comment at the end of Example 10.5, Custom Operator on a Domain Type, in Chapter 10.2 Type Conversion: Operators of the PostgreSQL 18 manual. Then, check the selection of Polymorphic Types in Chapter 36.2.5 .

I hope you learned something useful from this article. Please feel free to leave a comment on the LinkedIn version...