• Home
  • Services
    • DBA Support
    • DBA Consultancy Services
    • PostgreSQL Support
    • Website Maintenance
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum Database

    Greenplum Database

    $2,000.00 $1,500.00
    Read More
  • Company
    • FAQs
    • About Us
    • Contact
  • Events
  • Portfolio
  • Blogs
    • Blog – RayaFeeL
    • Blog – PostgreSQL Support
    • Blog – PostgreSQL Migration
    • Blog – All DB’s
    • Blog – Linux
    • Blog – Medical Coding
      • Cart

        0

    Have any question?
    (+91)8838953252
    ITsupport@rayafeel.com
    RegisterLogin
    RayaFeeL
    • Home
    • Services
      • DBA Support
      • DBA Consultancy Services
      • PostgreSQL Support
      • Website Maintenance
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum Database

      Greenplum Database

      $2,000.00 $1,500.00
      Read More
    • Company
      • FAQs
      • About Us
      • Contact
    • Events
    • Portfolio
    • Blogs
      • Blog – RayaFeeL
      • Blog – PostgreSQL Support
      • Blog – PostgreSQL Migration
      • Blog – All DB’s
      • Blog – Linux
      • Blog – Medical Coding
        • Cart

          0

      Databases

      • Home
      • All courses
      • Databases
      • PostgreSQL Database
      CoursesIT & SoftwareDatabasesPostgreSQL Database
      • BASIC POSTGRESQL
        6
        • Lecture1.1
          PostgreSQL Brief History 30 min
        • Lecture1.2
          PostgreSQL Features 20 min
        • Lecture1.3
          PostgreSQL Monitoring Tools Overview 30 min
        • Lecture1.4
          PostgreSQL – Oracle Vs PostgreSQL 30 min
        • Lecture1.5
          PostgreSQL Data Types 30 min
        • Lecture1.6
          PostgreSQL Maximum Objects Size 30 min
      • VMWARE & POSTGRESQL INSTALLATION
        9
        • Lecture2.1
          Vmware Installation 30 min
        • Lecture2.2
          Creating Linux Virtual Machine 01 hour
        • Lecture2.3
          PostgreSQL Installation Types 30 min
        • Lecture2.4
          PostgreSQL GUI Installation 30 min
        • Lecture2.5
          PostgreSQL Text Mode Installation 30 min
        • Lecture2.6
          PostgreSQL Unattended Mode Installation 30 min
        • Lecture2.7
          Configure the network & Disk Partition 30 min
        • Lecture2.8
          How to install PostgreSQL10 On Linux Server ? 20 min
        • Lecture2.9
          PostgreSQL -11 Installation (rpm & source code) 30 min
      • POSTGRESQL DATABASE
        6
        • Lecture3.1
          Connect Postgres Server 10 min
        • Lecture3.2
          PostgreSQL startup / shutdown /restart the postgresql server 30 min
        • Lecture3.3
          PostgreSQL .bash_profile Set Up 30 min
        • Lecture3.4
          PostgreSQL Database Creation 30 min
        • Lecture3.5
          PostgreSQL Connect Database 30 min
        • Lecture3.6
          PostgreSQL Drop Database 30 min
      • POSTGRESQL TABLE
        16
        • Lecture4.1
          PostgreSQL Languages 30 min
        • Lecture4.2
          PostgreSQL Create Table 30 min
        • Lecture4.3
          PostgreSQL Select Table 30 min
        • Lecture4.4
          PostgreSQL Alter Table 30 min
        • Lecture4.5
          PostgreSQL Drop Table 30 min
        • Lecture4.6
          PostgreSQL Truncate Table 30 min
        • Lecture4.7
          PostgreSQL Rename 30 min
        • Lecture4.8
          PostgreSQL Comment 30 min
        • Lecture4.9
          PostgreSQL Insert 30 min
        • Lecture4.10
          PostgreSQL Update 30 min
        • Lecture4.11
          PostgreSQL Table Delete 30 min
        • Lecture4.12
          PostgreSQL Merge Table 30 min
        • Lecture4.13
          PostgreSQL UNION 30 min
        • Lecture4.14
          PostgreSQL UNION ALL 30 min
        • Lecture4.15
          PostgreSQL INTERSECT 30 min
        • Lecture4.16
          PostgreSQL DISTINCT 30 min
      • USER/OBJECTS PRIVILEGE AND ROLES ASIGNING
        3
        • Lecture5.1
          PostgreSQL Grant 30 min
        • Lecture5.2
          PostgreSQL Revoke 30 min
        • Lecture5.3
          Postgres user creation and restrict DDL statement & other database access 30 min
      • TRANSACTIONS - MVCC
        3
        • Lecture6.1
          PostgreSQL Commit 30 min
        • Lecture6.2
          PostgreSQL Rollback 30 min
        • Lecture6.3
          PostgreSQL Savepoint 30 min
      • POSTGRESQL USER/SCHEMA MANAGEMENT
        9
        • Lecture7.1
          PostgreSQL User Creation 30 min
        • Lecture7.2
          PostgreSQL User Creation Using Utility 30 min
        • Lecture7.3
          PostgreSQL Drop user 30 min
        • Lecture7.4
          PostgreSQL Drop User Using Utility 30 min
        • Lecture7.5
          PostgreSQL Password Changing and Views 30 min
        • Lecture7.6
          PostgreSQL Group/role 30 min
        • Lecture7.7
          Alter PostgreSQL User/Role/group 30 min
        • Lecture7.8
          PostgreSQL Schema 30 min
        • Lecture7.9
          PostgreSQL user creation and restrict DDL & database access 30 min
      • POSTGRESQL CONSTRAINTS
        6
        • Lecture8.1
          PostgreSQL Constraints 30 min
        • Lecture8.2
          PostgreSQL Primary Key 30 min
        • Lecture8.3
          PostgreSQL UNIQUE Constraint 30 min
        • Lecture8.4
          PostgreSQL CHECK Constraint 30 min
        • Lecture8.5
          PostgreSQL NOT NULL Constraint 30 min
        • Lecture8.6
          PostgreSQL Foreign Key 30 min
      • POSTGRESQL ADVANCE DATA TYPE
        5
        • Lecture9.1
          PostgreSQL DOMAIN Data Type 30 min
        • Lecture9.2
          PostgreSQL Alter Domain 30 min
        • Lecture9.3
          PostgreSQL Drop DOMAIN 30 min
        • Lecture9.4
          PostgreSQL Json Data Type 30 min
        • Lecture9.5
          PostgreSQL Sequence 30 min
      • POSTGRESQL VIEWS
        1
        • Lecture10.1
          How to Create PostgreSQL View 30 min
      • POSTGRESQL MONITORING OBJECT USUAGE/SIZE
        1
        • Lecture11.1
          How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database 30 min
      • POSTGRESQL DATABASE ARCHITECTURE
        4
        • Lecture12.1
          PostgreSQL Tablespace 30 min
        • Lecture12.2
          PostgreSQL UnderStanding Of Objects 30 min
        • Lecture12.3
          PostgreSQL Architecture 30 min
        • Lecture12.4
          PostgreSQL Query Flow 30 min
      • POSTGRESQL BACKUP AND RECOVERY
        13
        • Lecture13.1
          PostgreSQL Backup & Recovery Overview 30 min
        • Lecture13.2
          PostgreSQL Database Backup Tools – Pg_dump 30 min
        • Lecture13.3
          PostgreSQL Database Backup Tools – Pg_dumpall 30 min
        • Lecture13.4
          PostgreSQL Database Backup Tools – Pg_basebackup 30 min
        • Lecture13.5
          PostgreSQL COPY data From Text File 30 min
        • Lecture13.6
          PostgreSQL COPY data to Text File 30 min
        • Lecture13.7
          PostgreSQL Clone Table 20 min
        • Lecture13.8
          Postgres Database Schema Migration 30 min
        • Lecture13.9
          PostgreSQL Database clone/migration 30 min
        • Lecture13.10
          PostgreSQL Compress backup format 30 min
        • Lecture13.11
          PostgreSQL Archivelog 30 min
        • Lecture13.12
          PostgreSQL Point In Time Recovery 30 min
        • Lecture13.13
          Taking Backup On Postgres Slave (Standby) Server 30 min
      • POSTGRESQL PERFORMANCE TUNING
        5
        • Lecture14.1
          PostgreSQL Index 30 min
        • Lecture14.2
          PostgreSQL Reindex 30 min
        • Lecture14.3
          PostgreSQL PerformanceTuning 30 min
        • Lecture14.4
          Understanding Postgres VACUUM | VACUUM FULL | VACUUM ANALYZE 30 min
        • Lecture14.5
          Postgres Autovacuum Configuration/Setup 30 min
      • HIGH AVAILABILITY, LOAD BALANCING, AND REPLICATION
        11
        • Lecture15.1
          POSTGRESQL STREAMING REPLICATION 30 min
        • Lecture15.2
          SSL Streaming replication with PostgreSQL 10 30 min
        • Lecture15.3
          How to make Linux partition and configure the network on Redhat Linux7.3 30 min
        • Lecture15.4
          How To Fix Firewall Issues while connecting the other server? 30 min
        • Lecture15.5
          How to install the PostgreSQL10 On Linux ? 30 min
        • Lecture15.6
          How to Configure the cascade replication On PostgreSQL 10.3 ? 30 min
        • Lecture15.7
          How to add extra one slave an existing PostgreSQL cascade replication without down time ? 30 min
        • Lecture15.8
          PostgreSQL Switchover 30 min
        • Lecture15.9
          Postgres Failover 30 min
        • Lecture15.10
          Postgres Upgrade 30 min
        • Lecture15.11
          PostgreSQL Upgrade 9.5 to 11.3 . 30 min

        PostgreSQL DOMAIN Data Type

        • CREATE DOMAIN statement creates a user-defined data type with a range, optional DEFAULT, NOT NULL and CHECK constraint.
        • CREATE DOMAIN creates a new domain. A domain is essentially a data type with optional constraints (restrictions on the allowed set of values). The user who defines a domain becomes its owner.
        • If a schema name is given (for example, CREATE DOMAIN myschema.mydomain …) then the domain is created in the specified schema. Otherwise it is created in the current schema. The domain name must be unique among the types and domains existing in its schema.
        • Domains are useful for abstracting common constraints on fields into a single location for maintenance. For example, several tables might contain email address columns, all requiring the same CHECK constraint to verify the address syntax. Define a domain rather than setting up each table’s constraint individually.
        • To be able to create a domain, you must have USAGE privilege on the underlying type.

        Syntax:

        CREATE DOMAIN name [ AS ] data_type
            [ COLLATE collation ]
            [ DEFAULT expression ]
            [ constraint [ ... ] ]
        
        where constraint is:
        
        [ CONSTRAINT constraint_name ]
        { NOT NULL | NULL | CHECK (expression) }

        Parameters:

        parameter Explanation
        name The name (optionally schema-qualified) of a domain to be created.
        data_type The underlying data type of the domain. This can include array specifiers.
        collation An optional collation for the domain. If no collation is specified, the underlying data type’s default collation is used. The underlying type must be collatable if COLLATE is specified.
        DEFAULT expression The DEFAULT clause specifies a default value for columns of the domain data type. The value is any variable-free expression (but subqueries are not allowed). The data type of the default expression must match the data type of the domain. If no default value is specified, then the default value is the null value.

        The default expression will be used in any insert operation that does not specify a value for the column. If a default value is defined for a particular column, it overrides any default associated with the domain. In turn, the domain default overrides any default value associated with the underlying data type.

        CONSTRAINT constraint_name An optional name for a constraint. If not specified, the system generates a name.
        NOT NULL Values of this domain are prevented from being null (but see notes below).
        NULL Values of this domain are allowed to be null. This is the default.
        CHECK (expression) CHECK clauses specify integrity constraints or tests which values of the domain must satisfy. Each constraint must be an expression producing a Boolean result. It should use the key word VALUE to refer to the value being tested. Expressions evaluating to TRUE or UNKNOWN succeed. If the expression produces a FALSE result, an error is reported and the value is not allowed to be converted to the domain type.

        When a domain has multiple CHECK constraints, they will be tested in alphabetical order by name. (PostgreSQL versions before 9.5 did not honor any particular firing order for CHECK constraints.)

        Notes:

        Domain constraints, particularly NOT NULL, are checked when converting a value to the domain type. It is possible for a column that is nominally of the domain type to read as null despite there being such a constraint. For example, this can happen in an outer-join query, if the domain column is on the nullable side of the outer join. A more subtle example is

        INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM tab WHERE false));

        The empty scalar sub-SELECT will produce a null value that is considered to be of the domain type, so no further constraint checking is applied to it, and the insertion will succeed.

        It is very difficult to avoid such problems, because of SQL’s general assumption that a null value is a valid value of every data type. Best practice therefore is to design a domain’s constraints so that a null value is allowed, and then to apply column NOT NULL constraints to columns of the domain type as needed, rather than directly to the domain type.


        PRACTICAL1. POSTGRESQL DOMAIN CREATION:

        postgres=# CREATE DOMAIN postal_code AS TEXT 
                     CHECK(VALUE ~ '^\d{5}$'OR VALUE ~ '^\d{5}-\d{4}$');
        CREATE DOMAIN
        
        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)
        (1 row)
        postgres=# CREATE TABLE us_address (address_id SERIAL PRIMARY KEY,
                                             street1 TEXT NOT NULL,
                                             street2 TEXT,
                                             street3 TEXT,
                                             city TEXT NOT NULL,
                                             postal postal_code NOT NULL);
        
        postgres=# \d us_address
                                         Table "public.us_address"
           Column   |    Type     |                            Modifiers                            
        ------------+-------------+-----------------------------------------------------------------
         address_id | integer     | not null default nextval('us_address_address_id_seq'::regclass)
         street1    | text        | not null
         street2    | text        | 
         street3    | text        | 
         city       | text        | not null
         postal     | postal_code | not null
        Indexes:
            "us_address_pkey" PRIMARY KEY, btree (address_id)

        Insert some data into “us_address” table:

        postgres=# insert into us_address values(1,'add1','add2','add3','cal',76321);
        INSERT 0 1
        
        postgres=# insert into us_address values(2,'add1','add2','add3','cal',16321);
        INSERT 0 1
        
        postgres=# insert into us_address values(3,'add1','add2','add3','cal',10001);
        INSERT 0 1

        Domain checking is faster than constraints:
        because domain will be check faster than primary key  you will understand from below example

        postgres=# insert into us_address values(4,'add1','add2','add3','cal',09001);
        ERROR:  value for domain postal_code violates check constraint "postal_code_check"
        
        postgres=# insert into us_address values(3,'add1','add2','add3','cal',09001);
        ERROR:  value for domain postal_code violates check constraint "postal_code_check"
        Note: we already inserted “address_id” 3 but this will not throw error fastly only throw faster domain(postal_code violates check constraint “postal_code_check”) Error
        –finally we fetch data From “us_address” table
        postgres=# select * from us_address;
         address_id | street1 | street2 | street3 | city | postal 
        ------------+---------+---------+---------+------+--------
                  1 | add1    | add2    | add3    | cal  | 76321
                  2 | add1    | add2    | add3    | cal  | 16321
                  3

        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. Create 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

         

         

         

        Prev PostgreSQL Foreign Key
        Next PostgreSQL Alter Domain

        ADVERTISEMENT

        Latest Courses

        PostgreSQL Database

        PostgreSQL Database

        $600.00 $500.00
        Greenplum Database

        Greenplum Database

        $2,000.00 $1,500.00

        Preview Course

        Free

        Latest Posts

        PostgreSQL Patching version 9, 10,11
        10Oct2019
        Tools for PostgreSQL
        16Sep2019
        Postgres user creation and restrict DDL & database access
        13Sep2019

        Recent Forum Topics

        • thought behind whiteboard activity
        • Are you going to take your first ste
        • How to start working on an application?
        • please let me know pre requirements to increase work_mem
        • how to copy some data in one table to another table in postgres

        2ndquadrant.in

        (+91) 8838953252

        ITsupport@rayafeel.com

        Company

        • About Us
        • Contact
        • Our Team
        • Blog

        COURSES

        • List Of Course
        • Become An Instructor
        • Events
        • Postgres Support Blog

        Support

        • DBA Support
        • Consultancy Services
        • Postgres Migration Blogs
        • Forum

        Recommend

        • Groups
        • Login
        • FAQs
        • SignUp

        IT Services by rayafeel.com. Powered by Rayafeel Technologies Pvt Ltd.

        • Privacy
        • Terms

        Become An Instructor?

        Join thousand of instructors and earn money hassle free!

        Get Started Now

        Login with:

        Login with Google Login with Twitter Login with LinkedIn Login with Microsoft

        Login with your site account

        Lost your password?

        Not a member yet? Register now

        Register a new account

        Are you a member? Login now

        Modal title

        Message modal