Data type for email in postgresql

Summary: in this tutorial, you will learn how to create PostgreSQL user-defined data type using CREATE DOMAIN and CREATE TYPE statements.

Besides built-in data types, PostgreSQL allows you to create user-defined data types through the following statements:

  • CREATE DOMAIN creates a user-defined data type with constraints such as NOT NULL, CHECK, etc.
  • CREATE TYPE creates a composite type used in stored procedures as the data types of returned values.

PostgreSQL CREATE DOMAIN statement

In PostgreSQL, a domain is a data type with optional constraints e.g., NOT NULL and CHECK. A domain has a unique name within the schema scope.

Domains are useful for centralizing the management of fields with common constraints. For example, some tables may have the same column that do not accept NULL and spaces.

The following statement create a table named mailing_list:

CREATE TABLE mailing_list ( id SERIAL PRIMARY KEY, first_name VARCHAR NOT NULL, last_name VARCHAR NOT NULL, email VARCHAR NOT NULL, CHECK ( first_name !~ '\s' AND last_name !~ '\s' ) );

Code language: SQL (Structured Query Language) (sql)

In this table, both first_name and last_name columns do not accept null and spaces. Instead of defining the CHECK constraint, you can create a contact_name domain and reuse it in multiple columns.

The following statement uses the CREATE DOMAIN to create a new domain called contact_name with the VARCHAR datatype and do not accept NULL and spaces:

CREATE DOMAIN contact_name AS VARCHAR NOT NULL CHECK (value !~ '\s');

Code language: SQL (Structured Query Language) (sql)

And you use contact_name as the datatype of the first_name and last_name columns as a regular built-in type:

CREATE TABLE mailing_list ( id serial PRIMARY KEY, first_name contact_name, last_name contact_name, email VARCHAR NOT NULL );

Code language: SQL (Structured Query Language) (sql)

The following statement inserts a new row into the mailing_list table:

INSERT INTO mailing_list (first_name, last_name, email) VALUES('Jame V','Doe','');

Code language: SQL (Structured Query Language) (sql)

PostgreSQL issued the following error because the first name contains a space:

ERROR: value for domain contact_name violates check constraint "contact_name_check"

Code language: SQL (Structured Query Language) (sql)

The following statement works because it does not violate any constraints of the contact_name type:

INSERT INTO mailing_list (first_name, last_name, email) VALUES('Jane','Doe','');

Code language: JavaScript (javascript)

To change or remove a domain, you use the ALTER DOMAIN or DROP DOMAIN respectively.

To view all domains in the current database, you use the \dD command as follows:

test=#\dD List of domains Schema | Name | Type | Modifier | Check --------+--------------+-------------------+----------+----------------------------------- public | contact_name | character varying | not null | CHECK (VALUE::text !~ '\s'::text) (1 row)

Code language: SQL (Structured Query Language) (sql)

Getting domain information

To get all domains in a specific schema, you use the following query:

SELECT typname FROM pg_catalog.pg_type JOIN pg_catalog.pg_namespace ON pg_namespace.oid = pg_type.typnamespace WHERE typtype = 'd' and nspname = '';

Code language: JavaScript (javascript)

The following statement returns domains in the public schema of the current database:

SELECT typname FROM pg_catalog.pg_type JOIN pg_catalog.pg_namespace ON pg_namespace.oid = pg_type.typnamespace WHERE typtype = 'd' and nspname = 'public';

Code language: JavaScript (javascript)
Data type for email in postgresql

PostgreSQL CREATE TYPE

The CREATE TYPE statement allows you to create a composite type, which can be used as the return type of a function.

Suppose you want to have a function that returns several values: film_id, title, and release_year. The first step is to create a type e.g., film_summary as follows:

CREATE TYPE film_summary AS ( film_id INT, title VARCHAR, release_year SMALLINT );

Code language: SQL (Structured Query Language) (sql)

Second, use the film_summary data type as the return type of a function:

CREATE OR REPLACE FUNCTION get_film_summary (f_id INT) RETURNS film_summary AS $$ SELECT film_id, title, release_year FROM film WHERE film_id = f_id ; $$ LANGUAGE SQL;

Code language: SQL (Structured Query Language) (sql)

Third, call the get_film_summary() function:

SELECT * FROM get_film_summary (40);

Code language: SQL (Structured Query Language) (sql)
Data type for email in postgresql

To change a user-defined type, you use the ALTER TYPE statement. To remove a user-defined type, you use the DROP TYPE statement.

If you use the psql program, you can list all user-defined types in the current database using the \dT or \dT+ command:

dvdrental=# \dT List of data types Schema | Name | Description --------+--------------+------------- public | contact_name | public | film_summary | public | mpaa_rating | (3 rows)

Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to create PostgreSQL user-defined types using the CREATE DOMAIN and CREATE TYPE statements.

Was this tutorial helpful ?

What is the data type for email in PostgreSQL?

@SaketSuraj: varchar is a valid data type in Postgres (it's an alias for character varying ) dbfiddle.uk/…

What data type is an email address?

Email address is a standard Experience Data Model (XDM) data type that describes the details of an email address.

What should be the datatype for email in SQL?

you can use varchar as your data type for email column as emails are usually composed of letters, numbers and special characters.

Can PostgreSQL send email?

To resolve this problems, the PostgreSQL has a great functionality: notify - the sending notifications on channels set by developer. A trigger can be used to notify a channel, and this, in turn, to send a email by script Python.