-
BASIC POSTGRESQL
6-
Lecture1.1
-
Lecture1.2
-
Lecture1.3
-
Lecture1.4
-
Lecture1.5
-
Lecture1.6
-
-
VMWARE & POSTGRESQL INSTALLATION
9-
Lecture2.1
-
Lecture2.2
-
Lecture2.3
-
Lecture2.4
-
Lecture2.5
-
Lecture2.6
-
Lecture2.7
-
Lecture2.8
-
Lecture2.9
-
-
POSTGRESQL DATABASE
6-
Lecture3.1
-
Lecture3.2
-
Lecture3.3
-
Lecture3.4
-
Lecture3.5
-
Lecture3.6
-
-
POSTGRESQL TABLE
16-
Lecture4.1
-
Lecture4.2
-
Lecture4.3
-
Lecture4.4
-
Lecture4.5
-
Lecture4.6
-
Lecture4.7
-
Lecture4.8
-
Lecture4.9
-
Lecture4.10
-
Lecture4.11
-
Lecture4.12
-
Lecture4.13
-
Lecture4.14
-
Lecture4.15
-
Lecture4.16
-
-
USER/OBJECTS PRIVILEGE AND ROLES ASIGNING
3-
Lecture5.1
-
Lecture5.2
-
Lecture5.3
-
-
TRANSACTIONS - MVCC
3-
Lecture6.1
-
Lecture6.2
-
Lecture6.3
-
-
POSTGRESQL USER/SCHEMA MANAGEMENT
9-
Lecture7.1
-
Lecture7.2
-
Lecture7.3
-
Lecture7.4
-
Lecture7.5
-
Lecture7.6
-
Lecture7.7
-
Lecture7.8
-
Lecture7.9
-
-
POSTGRESQL CONSTRAINTS
6-
Lecture8.1
-
Lecture8.2
-
Lecture8.3
-
Lecture8.4
-
Lecture8.5
-
Lecture8.6
-
-
POSTGRESQL ADVANCE DATA TYPE
5-
Lecture9.1
-
Lecture9.2
-
Lecture9.3
-
Lecture9.4
-
Lecture9.5
-
-
POSTGRESQL VIEWS
1-
Lecture10.1
-
-
POSTGRESQL MONITORING OBJECT USUAGE/SIZE
1 -
POSTGRESQL DATABASE ARCHITECTURE
4-
Lecture12.1
-
Lecture12.2
-
Lecture12.3
-
Lecture12.4
-
-
POSTGRESQL BACKUP AND RECOVERY
13-
Lecture13.1
-
Lecture13.2
-
Lecture13.3
-
Lecture13.4
-
Lecture13.5
-
Lecture13.6
-
Lecture13.7
-
Lecture13.8
-
Lecture13.9
-
Lecture13.10
-
Lecture13.11
-
Lecture13.12
-
Lecture13.13
-
-
POSTGRESQL PERFORMANCE TUNING
5-
Lecture14.1
-
Lecture14.2
-
Lecture14.3
-
Lecture14.4
-
Lecture14.5
-
-
HIGH AVAILABILITY, LOAD BALANCING, AND REPLICATION
11-
Lecture15.1
-
Lecture15.2
-
Lecture15.3
-
Lecture15.4
-
Lecture15.5
-
Lecture15.6
-
Lecture15.7
-
Lecture15.8
-
Lecture15.9
-
Lecture15.10
-
Lecture15.11
-
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);
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)
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)
- 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.
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.
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)
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
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)