PostgreSQL DOMAIN Data Type -2

PRACTICAL 2.CREATION OF DOMAIN WITHOUT CONSTRAINT:
Consider the following scenario on PostgreSQL 9.3:
Step 1.Create a domain with no constraints:

postgres=# CREATE DOMAIN zipcode AS text;
CREATE DOMAIN

Step 2.Add a named constraint:

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

Step 3.Check the Domain whether created or not:

postgres=# \dD        
                                              List of domains
 Schema |    Name     | Type | Modifier |                              Check                               
--------+-------------+------+----------+------------------------------------------------------------------
 public | postal_code | text |          | CHECK (VALUE ~ '^\d{5}$'::text OR VALUE ~ '^\d{5}-\d{4}$'::text)
 public | zipcode     | text |          | CHECK (char_length(VALUE) = 3)
(2 rows)

Step 4.Ctreate a table using "zipcode" Data type

postgres=# create table details (name text,code zipcode);
CREATE TABLE

Step 5. Insert some data into "details" table for checking purpose:

postgres=# insert into details values('nijam',123);
INSERT 0 1
postgres=# insert into details values('nijam',676);
INSERT 0 1
postgres=# insert into details values('nijam','abc');
INSERT 0 1
postgres=# insert into details values('nijam','12a');
INSERT 0 1

if You insert below values surely it will throw Errors:

postgres=# insert into details values('nijam',00);  
ERROR:  value for domain zipcode violates check constraint "zipcheck"

postgres=# insert into details values('nijam',1); 
ERROR:  value for domain zipcode violates check constraint "zipcheck"

postgres=# insert into details values('nijam',1234);
ERROR:  value for domain zipcode violates check constraint "zipcheck"

postgres=# insert into details values('nijam',000); 
ERROR:  value for domain zipcode violates check constraint "zipcheck"

postgres=# insert into details values('nijam',000);
ERROR:  value for domain zipcode violates check constraint "zipcheck"

Step 5.Now,suppose that we want to change the constraint using ALTER DOMAIN, for example - drop the domain. The manual says:
Syntax:

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

postgres=# select conname from pg_constraint where contypid = 'zipcode'::regtype;
 zipcheck

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

postgres=# select conname from pg_constraint where contypid = 'zipcode'::regtype;
 conname 
---------
(0 rows)

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

• above command will show as empty becouse we droped constraint after that we can change constraint whatever we want
• "\dD" not only shows the constraint check sql prompt also shows the domains detail

PRACTICAL 3.POSTGRESQL CREATE DOMAIN NOT NULL DEFAULT 'N/A':
Step 1.CREATE DOMAIN statement allows you to create an alias for a built-in data type, and assign range and value constraints:

postgres=# CREATE DOMAIN addrtype VARCHAR(90) NOT NULL DEFAULT 'N/A';
CREATE DOMAIN

postgres=# CREATE DOMAIN idxtype INT CHECK (VALUE > 10 AND VALUE < 99);
CREATE DOMAIN

Step 2.Now we go to create a table as per created domain,note the point "idxtype" having some value in check constraints

postgres=# CREATE TABLE place (name varchar(15),address addrtype,index idxtype); 
CREATE TABLE

Step 3. Insert some data:

postgres=#   INSERT INTO place VALUES('nijam','chennai', 11);
INSERT 0 1

Step 4.Insert default name, address, and index NULL:

postgres=#   INSERT INTO place (index) values (null);          
INSERT 0 1

postgres=# select * from place;
 name  | address | index 
-------+---------+-------
 nijam | chennai |    11
       | N/A     |   

Step 5.Insert values some out of range:

postgres=#   INSERT INTO place values ('junaith','kolkatta',9);        
ERROR:  value for domain idxtype violates check constraint "idxtype_check"

PRACTICAL 4.DROPPING DOMAIN:

postgres=# drop domain us_postal_code;
ERROR:  cannot drop type us_postal_code because other objects depend on it
DETAIL:  table us_snail_addy column postal depends on type us_postal_code
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

postgres=# drop domain us_postal_code casecade;
ERROR:  syntax error at or near "casecade"
LINE 1: drop domain us_postal_code casecade;

postgres=# drop domain us_postal_code cascade; 
NOTICE:  drop cascades to table us_snail_addy column postal
DROP DOMAIN