PostgreSQL Alter Domain

• ALTER DOMAIN changes the definition of an existing domain
• Here ALTER DOMAIN conforms to the SQL standard, except for the OWNER, RENAME, SET SCHEMA, and VALIDATE CONSTRAINT variants, which are PostgreSQL extensions. The NOT VALID clause of the ADD CONSTRAINT variant is also a PostgreSQL extension.
• ALTER DOMAIN ADD CONSTRAINT and ALTER DOMAIN SET NOT NULL will fail if the named domain or any derived domain is used within a composite-type column of any table in the database. They should eventually be improved to be able to verify the new constraint for such nested columns.

Synaxes:
These forms set or remove the default value for a domain. Note that defaults only apply to subsequent INSERT commands; they do not affect rows already in a table using the domain.

ALTER DOMAIN name { SET DEFAULT expression | DROP DEFAULT };

These forms change whether a domain is marked to allow NULL values or to reject NULL values. You can only SET NOT NULL when the columns using the domain contain no null values.

ALTER DOMAIN name { SET | DROP } NOT NULL;

This form adds a new constraint to a domain using the same syntax as CREATE DOMAIN. When a new constraint is added to a domain, all columns using that domain will be checked against the newly added constraint. These checks can be suppressed by adding the new constraint using the NOT VALID option; the constraint can later be made valid using ALTER DOMAIN … VALIDATE CONSTRAINT. Newly inserted or updated rows are always checked against all constraints, even those marked NOT VALID. NOT VALID is only accepted for CHECK constraints.

ALTER DOMAIN name ADD domain_constraint [ NOT VALID ];

This form drops constraints on a domain. If IF EXISTS is specified and the constraint does not exist, no error is thrown. In this case a notice is issued instead.

ALTER DOMAIN name DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ];

his form changes the name of a constraint on a domain.

ALTER DOMAIN name RENAME CONSTRAINT constraint_name TO new_constraint_name;

This form validates a constraint previously added as NOT VALID, that is, verify that all data in columns using the domain satisfy the specified constraint.

ALTER DOMAIN name VALIDATE CONSTRAINT constraint_name;

This form changes the owner of the domain to the specified user.

ALTER DOMAIN name OWNER TO new_owner;

This form changes the name of the domain.

ALTER DOMAIN name RENAME TO new_name;

This form changes the schema of the domain. Any constraints associated with the domain are moved into the new schema as well.

ALTER DOMAIN name SET SCHEMA new_schema;

You must own the domain to use ALTER DOMAIN. To change the schema of a domain, you must also have CREATE privilege on the new schema. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the domain’s schema. (These restrictions enforce that altering the owner doesn’t do anything you couldn’t do by dropping and recreating the domain. However, a superuser can alter ownership of any domain anyway.)

postgres=# \dD zipcode
              List of domains
 Schema |  Name   | Type | Modifier | Check 
--------+---------+------+----------+-------
 public | zipcode | text |          | 
(1 row)

postgres=# ALTER DOMAIN zipcode SET NOT NULL;
ALTER DOMAIN
postgres=# \dD zipcode                       
              List of domains
 Schema |  Name   | Type | Modifier | Check 
--------+---------+------+----------+-------
 public | zipcode | text | not null | 
(1 row)

2. To remove a NOT NULL constraint from a domain:

postgres=# ALTER DOMAIN zipcode DROP NOT NULL;
ALTER DOMAIN

3. To add a check constraint to a domain:

postgres=# ALTER DOMAIN zipcode ADD CONSTRAINT zipchk1 CHECK (char_length(VALUE) = 3);
ALTER DOMAIN

4. To remove a check constraint from a domain:

postgres=# ALTER DOMAIN zipcode DROP CONSTRAINT zipchk1;
ALTER DOMAIN

5. To rename a check constraint on a domain

postgres=# ALTER DOMAIN zipcode ADD CONSTRAINT zipchk1 CHECK (char_length(VALUE) = 3);
ALTER DOMAIN
postgres=# ALTER DOMAIN zipcode RENAME CONSTRAINT zipchk1 TO zip_check;               
ALTER TABLE

6. To move the domain into a different schema:

postgres=# \dD zipcode
                           List of domains
 Schema |  Name   | Type | Modifier |             Check              
--------+---------+------+----------+--------------------------------
 public | zipcode | text |          | CHECK (char_length(VALUE) = 3)
(1 row)


postgres=# ALTER DOMAIN zipcode SET SCHEMA SCHEMA1;
ALTER DOMAIN


postgres=# \dD schema1.zipcode
                           List of domains
 Schema  |  Name   | Type | Modifier |             Check              
---------+---------+------+----------+--------------------------------
 schema1 | zipcode | text |          | CHECK (char_length(VALUE) = 3)
(1 row)