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

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

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?

Migrating From Oracle to PostgreSQL using ora2pg open source tools