Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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

Comments

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

vacuumlo - removing large objects orphans from a database PostgreSQL