• 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 UNIQUE Constraint

        • UNIQUE constraint same as primary key different is UNIQUE allow single NULL value.
        • UNIQUE constraint, every time you insert a new row, PostgreSQL checks if the value is already in the table. If it found that the new value is already there, it would give back an error message and reject the changes. The same process is carried out for the update existing data.
        • When you add a UNIQUE constraint to a column or a group of columns, PostgreSQL will create a btree index on the respective column or a group of columns automatically.
        • The Value of the column must be unique across the whole table. However, the column can have many NULL values because PostgreSQL treats each NULL value to be unique. Notice that SQL standard only allows one NULL value in the column that has the UNIQUE constraint.

        CREATING UNIQUE CONSTRAINT FOLLOWING ANYONE METHOD :

        1. COLUMN METHOD UNIQUE CONSTRAINT
        CREATE TABLE table_name (
            Column_1 integer UNIQUE,
            Column_2 text,
            Column_3 numeric
        );
        
        
        2.TABLE METHOD UNIQUE CONSTRAIN:
        CREATE TABLE table_name (
            Column_1 integer,
            Column_2 text,
            Column_3 numeric,
            UNIQUE (column_1)
        );
        
        3.ALTER METHOD UNIQUE CONSTRAINT:
        ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column_1,column_2,.,.,....etc);
        PRACTICAL 1. CREATION OF UNIQUE CONSRAINTS:

        The following CREATE TABLE statement creates a new table named student with a UNIQUE constraint applied to the student_id column.

        postgres=# CREATE TABLE STUDENTS (
            student_id integer UNIQUE,
            name text,
            class integer
        );
        
        postgres=# \d students
             Table "public.students"
           Column   |  Type   | Modifiers 
        ------------+---------+-----------
         student_id | integer | 
         name       | text    | 
         class      | integer | 
        Indexes:
            "students_student_id_key" UNIQUE CONSTRAINT, btree (student_id)
        Note:“students_student_id_key” are the system generated unique key.
         
        PRACTICAL 2. CREATION OF UNIQUE CONSRAINTS WITH CONSTRAINT NAME:

        The UNIQUE constraint can be written as the table constraint with constraint name as following example.

        postgres=# CREATE TABLE INDIA(  
        STATE_no integer,   
        STATE_name character(35),  
        STATE_grade character(1),
        CONSTRAINT uni_key UNIQUE(STATE_no)
        );
        
        postgres=# \d india
                  Table "public.india"
           Column    |     Type      | Modifiers 
        -------------+---------------+-----------
         state_no    | integer       | 
         state_name  | character(35) | 
         state_grade | character(1)  | 
        Indexes:
            "uni_key" UNIQUE CONSTRAINT, btree (state_no)
        Note:Here “uni_key” is the unique constraint name.
        PRACTICAL 3. INSERTING OPERATION ON UNIQUE KEY TABLE(STUDENTS):
        • when you inserting the New value PostgreSQL checks if the value is already in the table. If it found that the new value is already there, it would give back an error message and reject the changes. The same process is carried out for the update existing data.
        • the table  column can have many NULL values because PostgreSQL treats each NULL value to be unique. Notice that SQL standard only allows one NULL value in the column that has the UNIQUE constraint.
        postgres=# insert into students values(1,'nijam',8),(2,'junaith',10),(3,'benz',12);
        INSERT 0 3
        postgres=# insert into students values(4,'nijam',8),(5,'junaith',10),(null,'benz',12);
        INSERT 0 3
        postgres=# insert into students values(8,'nijam',8),(9,'junaith',10),(null,'benz',12);
        INSERT 0 3
        postgres=# insert into students values(11,'nijam',8),(10,'junaith',10),(null,'benz',12);
        INSERT 0 3
        
        postgres=# select * from students;                                              
         student_id |  name   | class 
        ------------+---------+-------
                  1 | nijam   |     8
                  2 | junaith |    10
                  3 | benz    |    12
                  4 | nijam   |     8
                  5 | junaith |    10
                    | benz    |    12
                  8 | nijam   |     8
                  9 | junaith |    10
                    | benz    |    12
                 11 | nijam   |     8
                 10 | junaith |    10
                    | benz    |    12
        (12 rows)

        when you inserting duplicate values Postgresql will throw error, see following exaple you will understood unique constraints

        postgres=# insert into students values(11,'nijam',8),(10,'junaith',10),(12,'benz',12);  
        ERROR:  duplicate key value violates unique constraint "students_student_id_key"
        DETAIL:  Key (student_id)=(11) already exists.
        –why becouse means value 11 and 12 is already exist in students table

        PRACTICAL 4. UNIQUE CONSTRAINT ON MULTIPLE COLUMN:

        postgres=# CREATE TABLE batch(  
        batch_id integer,   
        batch_name character(35),  
        members integer,
        CONSTRAINT mul_uni_key UNIQUE(batch_id,members)
        );
        CREATE TABLE
        
        postgres=# \d batch
                  Table "public.batch"
           Column   |     Type      | Modifiers 
        ------------+---------------+-----------
         batch_id   | integer       | 
         batch_name | character(35) | 
         members    | integer       | 
        Indexes:
            "mul_uni_key" UNIQUE CONSTRAINT, btree (batch_id, members)

        PRACTICAL 5. INSERTING OPERATION ON COMPOSITE UNIQUE KEY TABLE(BATCH):

        postgres=# insert into batch values(1,'nijam',1);
        INSERT 0 1
        postgres=# insert into batch values(2,'nijam',5);
        INSERT 0 1
        postgres=# insert into batch values(3,'nijam',5);
        INSERT 0 1
        postgres=# insert into batch values(4,'nijam',5);
        INSERT 0 1
        postgres=# insert into batch values(4,'nijam',8);
        INSERT 0 1
        postgres=# select * from batch;                                                                          
         batch_id |             batch_name              | members 
        ----------+-------------------------------------+---------
                1 | nijam                               |       1
                2 | nijam                               |       5
                3 | nijam                               |       5
                4 | nijam                               |       5
                4 | nijam                               |       8
        (5 rows)

        Note:A table can have at most one UNIQUE  (There can be any number of unique and not-null constraints, which are functionally almost the same thing, but only one can be identified as the UNIQUE KEY.)

        postgres=# insert into batch values(4,'nijam',8);
        ERROR:  duplicate key value violates unique constraint "mul_uni_key"
        DETAIL:  Key (batch_id, members)=(4, 8) already exists.
        Solution:
        Postgresql table will have any number of unique and not-null constraints when you inserting the values any one value should be identified as the UNIQUE  values other wise it will throw error look above example.
        PRACTICAL 6. UNIQUE CONSTRAINT USING UNIQUE INDEX:
        If you want to add a unique constraint to a column or a group of columns using existing unique index. Let’s take a look at the following steps.

        step 1.Suppose we have a table named cellphones:

        create table cellphones(
        id int,
         name VARCHAR (50) NOT NULL,
         phone_model VARCHAR (16) NOT NULL
        );
        CREATE TABLE

        step 2. We create a unique index based on the phone_model column.

        postgres=# CREATE UNIQUE INDEX CONCURRENTLY uni_index ON cellphones(phone_model);
        CREATE INDEX

        step 3.  Add a unique constraint to the cellphones table using the “uni_index” index.

        postgres=# ALTER TABLE cellphones ADD CONSTRAINT uni_constraint UNIQUE USING INDEX uni_index;
        NOTICE:  ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "uni_index" to "uni_constraint"
        ALTER TABLE
        
        postgres=# \d cellphones
                                           Table "public.cellphones"
           Column    |         Type          |                        Modifiers                        
        -------------+-----------------------+---------------------------------------------------------
         id          | integer               | not null default nextval('cellphones_id_seq'::regclass)
         name        | character varying(50) | not null
         phone_model | character varying(16) | not null
        Indexes:
            "cellphones_pkey" PRIMARY KEY, btree (id)
            "uni_constraint" UNIQUE CONSTRAINT, btree (phone_model)

        step 4. MONITOR THE PENDING TRANSACTION USING PG_STAT_ACTIVITY:

        postgres=# select datid,datname,usename,state_change,state,query_start,waiting from pg_stat_activity;
         datid | datname  | usename  |         state_change          | state  |          query_start          | waiting 
        -------+----------+----------+-------------------------------+--------+-------------------------------+---------
         12814 | postgres | postgres | 2017-04-02 12:54:56.761781-04 | active | 2017-04-02 12:54:56.761768-04 | f
         12814 | postgres | postgres | 2017-03-30 11:09:38.073502-04 | idle   | 2017-03-30 11:09:38.03711-04  | f
        (2 rows)
        Note: ALTER TABLE statement needs an exclusive lock on the table. If you have many pending transactions, it will wait for those to complete before changing the table. You should check the  pg_stat_activity table to see how many pending transactions are in place using the following query:
        You should look at the result to find the state column with the value  idle in transaction. Those are the transactions that are pending to complete.

        PRACTICAL 7. UNIQUE CONSTRAINT ON MULTIPLE COLUMN USING ALTER TABLE:

        postgres=# CREATE TABLE LOCATION (
         No integer,
         village VARCHAR (50) NOT NULL,
         area_code integer
        );
        
        
        postgres=# \d LOCATION
                    Table "public.location"
          Column   |         Type          | Modifiers 
        -----------+-----------------------+-----------
         no        | integer               | 
         village   | character varying(50) | not null
         area_code | integer               |

        –To add a (multicolumn) unique constraint to a table

        postgres=# ALTER TABLE LOCATION ADD CONSTRAINT dist_id_zipcode_key UNIQUE (No,area_code);
        ALTER TABLE
        
        postgres=# \d LOCATION                                                                   
                    Table "public.location"
          Column   |         Type          | Modifiers 
        -----------+-----------------------+-----------
         no        | integer               | 
         village   | character varying(50) | not null
         area_code | integer               | 
        Indexes:
            "dist_id_zipcode_key" UNIQUE CONSTRAINT, btree (no, area_code)

        PRACTICAL 8. DROP UNIQUE KEY USING ALTER TABLE STATEMENT:

        postgres=# alter table LOCATION drop constraint dist_id_zipcode_key ;
        ALTER TABLE
        
        postgres=# \d LOCATION                                               
                    Table "public.location"
          Column   |         Type          | Modifiers 
        -----------+-----------------------+-----------
         no        | integer               | 
         village   | character varying(50) | not null
         area_code | integer
        PRACTICAL 9. POSTGRESQL CONSTRAINT VIEWS:

        if you want see all constraint name and table name(relname) you need to join pg_stat_all_tables and pg_constraint views

        postgres=# select a.conname,a.contype,a.conrelid,b.relname,b.relid from pg_constraint a,pg_stat_all_tables b where a.conrelid=b.relid;
                 conname         | contype | conrelid |    relname     | relid 
        -------------------------+---------+----------+----------------+-------
         prikey                  | p       |    24676 | films          | 24676
         production              | u       |    24697 | k              | 24697
         distributorc_did_check  | c       |    24702 | distributorc   | 24702
         uni_key                 | u       |    24761 | india          | 24761
         code_title              | p       |    24710 | primtab        | 24710
         students_student_id_key | u       |    24753 | students       | 24753
         pkey                    | p       |    24735 | postgres3      | 24735
         uni_constraint2         | u       |    24784 | mobiles        | 24784
         mobiles_pkey            | p       |    24784 | mobiles        | 24784
         uni_constraint          | u       |    24773 | cellphones     | 24773
         cellphones_pkey         | p       |    24773 | cellphones     | 24773
         con1                    | c       |    24706 | distributorsct | 24706
         postgres1_order_no_key  | u       |    24725 | postgres1      | 24725
         mul_uni_key             | u       |    24766 | batch          | 24766
         distributors_pkey       | p       |    24690 | distributors   | 24690
         distributors_name_check | c       |    24690 | distributors   | 24690
         postgres_ord_no_key     | u       |    24715 | postgres       | 24715
        (17 rows)
        Prev PostgreSQL Primary Key
        Next PostgreSQL CHECK Constraint

        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

        • 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
        • postgres script for finding queries more than 1 hours ?

        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