PostgreSQL Insert Records - Exercises, Practice, Solution

INSERT INTO countries VALUES('C1','India',1002);

postgres=# SELECT * FROM countries;
 country_id | country_name | region_id
------------+--------------+-----------
 C1         | India        |      1002
(1 row)

INSERT INTO countries (country_id,country_name) VALUES('C2','USA');

postgres=# SELECT * FROM countries;
 country_id | country_name | region_id
------------+--------------+-----------
 C1         | India        |      1002
 C2         | USA          |

CREATE TABLE country_new
AS SELECT * FROM countries;

postgres=# \d country_new;
    Column    |         Type          | Modifiers
--------------+-----------------------+-----------
 country_id   | character varying(2)  |
 country_name | character varying(40) |
 region_id    | numeric(10,0)         |
 
 

postgres=# SELECT * FROM country_new;
 country_id | country_name | region_id
------------+--------------+-----------
 C1         | India        |      1002
 C2         | USA          |
(2 rows)

INSERT INTO countries (country_id,country_name,region_id) VALUES('C3','UK',NULL);

postgres=# SELECT * FROM countries;
 country_id | country_name | region_id
------------+--------------+-----------
 C1         | India        |      1002
 C2         | USA          |
 C3         | UK           |
(3 rows)

INSERT INTO countries VALUES('C4','India',1001),
('C5','USA',1007),('C6','UK',1003);

postgres=# SELECT * FROM countries;

 country_id | country_name | region_id
------------+--------------+-----------
 C1         | India        |      1002
 C2         | USA          |
 C3         | UK           |
 C4         | India        |      1001
 C5         | USA          |      1007
 C6         | UK           |      1003
(6 rows)

INSERT INTO countries
SELECT * FROM country_new;

postgres=# SELECT * FROM countries;


 country_id | country_name | region_id
------------+--------------+-----------
 C1         | India        |      1002
 C2         | USA          |
 C3         | UK           |
 C4         | India        |      1001
 C5         | USA          |      1007
 C6         | UK           |      1003
(6 rows)

Create the table jobs.

CREATE TABLE jobs ( 
JOB_ID integer NOT NULL UNIQUE , 
JOB_TITLE varchar(35) NOT NULL, 
MIN_SALARY decimal(6,0)
);

INSERT INTO jobs VALUES(1001,'OFFICER',8000);


postgres=# SELECT * FROM jobs;
 job_id | job_title | min_salary
--------+-----------+------------
   1001 | OFFICER   |       8000
(1 row)

INSERT INTO jobs VALUES(1001,'OFFICER',8000);

postgres=# INSERT INTO jobsn VALUES(1001,'OFFICER',8000);
ERROR:  duplicate key value violates unique constraint "jobs_job_id_key"
DETAIL:  Key (job_id)=(1001) already exists.

Create the table jobs.

CREATE TABLE jobs ( 
JOB_ID integer NOT NULL UNIQUE PRIMARY KEY, 
JOB_TITLE varchar(35) NOT NULL, 
MIN_SALARY decimal(6,0)
);

INSERT INTO jobs VALUES(1001,'OFFICER',8000);

postgres=#  SELECT * FROM jobs;
 job_id | job_title | min_salary | max_salary
--------+-----------+------------+------------
 1001   | OFFICER   |       8000 |
(1 row)

INSERT INTO jobs VALUES(1001,'OFFICER',8000);

postgres=# INSERT INTO jobsn VALUES(1001,'OFFICER',8000);
ERROR:  duplicate key value violates unique constraint "jobs_pkey"
DETAIL:  Key (job_id)=(1001) already exists.

Create the table countries.


CREATE TABLE countries ( 
COUNTRY_ID integer NOT NULL,
COUNTRY_NAME varchar(40) NOT NULL,
REGION_ID integer NOT NULL,
PRIMARY KEY (COUNTRY_ID,REGION_ID)
);

INSERT INTO countries VALUES(501,'India',185);

postgres=# SELECT * FROM countries;
 country_id | country_name | region_id
------------+--------------+-----------
        501 | India        |       185
(1 row)

INSERT INTO countries VALUES(501,'Italy',185);

postgres=# INSERT INTO kan VALUES(501,'Italy',185);
ERROR:  duplicate key value violates unique constraint "countries_pkey"
DETAIL:  Key (country_id, region_id)=(501, 185) already exists.

Create the table countries.


CREATE TABLE countries ( 
COUNTRY_ID SERIAL PRIMARY KEY,
COUNTRY_NAME varchar(40) NOT NULL,
REGION_ID integer NOT NULL
);

INSERT INTO countries(COUNTRY_NAME,REGION_ID) VALUES('India',185);

postgres=# SELECT * FROM countries;
 country_id | country_name | region_id
------------+--------------+-----------
          1 | India        |       185
(1 row)

INSERT INTO countries(COUNTRY_NAME,REGION_ID) VALUES('Japan',102);

postgres=# SELECT * FROM countries;
 country_id | country_name | region_id
------------+--------------+-----------
          1 | India        |       185
          2 | Japan        |       102
(2 rows)

Create the table countries.


CREATE TABLE countries ( 
COUNTRY_ID SERIAL PRIMARY KEY,
COUNTRY_NAME varchar(40) NOT NULL DEFAULT 'N/A',
REGION_ID integer NOT NULL
);

INSERT INTO countries VALUES(501,'India',102);

postgres=# SELECT * FROM countries;
 country_id | country_name | region_id
------------+--------------+-----------
        501 | India        |       102
(1 row)

INSERT INTO countries(region_id) VALUES(109);

postgres=# SELECT * FROM countries;
 country_id | country_name | region_id
------------+--------------+-----------
        501 | India        |       102
          1 | N/A          |       109
(2 rows)

INSERT INTO countries(country_name,region_id) VALUES('Australia',121);

postgres=# SELECT * FROM countries;
 country_id | country_name | region_id
------------+--------------+-----------
        501 | India        |       102
          1 | N/A          |       109
          2 | Australia    |       121
(3 rows)

Sample table jobs.


CREATE TABLE jobs ( 
JOB_ID integer NOT NULL UNIQUE PRIMARY KEY, 
JOB_TITLE varchar(35) NOT NULL DEFAULT ' ', 
MIN_SALARY decimal(6,0) DEFAULT 8000, 
MAX_SALARY decimal(6,0) DEFAULT 20000
);

INSERT INTO jobs(JOB_ID,JOB_TITLE) VALUES(1001,'OFFICER');
INSERT INTO jobs(JOB_ID,JOB_TITLE) VALUES(1002,'CLERK');


 job_id | job_title | min_salary | max_salary
--------+-----------+------------+------------
   1001 | OFFICER   |       8000 |      20000
   1002 | CLERK     |       8000 |      20000
(2 rows)

Sample table job_history;

CREATE TABLE job_history ( 
EMPLOYEE_ID integer NOT NULL PRIMARY KEY, 
JOB_ID integer NOT NULL, 
DEPARTMENT_ID integer DEFAULT NULL, 
FOREIGN KEY (job_id) REFERENCES jobs(job_id)
);

Now insert the rows in the job_history.

INSERT INTO job_history VALUES(501,1001,60);

postgres=# SELECT  * FROM job_history;
 employee_id | job_id | department_id
-------------+--------+---------------
         501 |   1001 |            60
(1 row)

The value against job_id is 1001 which is exists in the job_id column of the jobs table, so no problem arise.

Now insert another row in the job_history table.

INSERT INTO job_history VALUES(502,1003,80);

postgres=# INSERT INTO job_history VALUES(502,1003,80);
ERROR:  insert or update on table "job_history" violates foreign key constraint "job_history_job_id_fkey"
DETAIL:  Key (job_id)=(1003) is not present in table "jobs".

Here in the above, the value against job_id is 1003 which is not exists in the job_id column of the jobs(parent table) table and that is why the child table job_history can not contain the value of job_id as specified. Here the primary key - foreign key relationship is violating and shows the above message.

Sample table departments.


CREATE TABLE departments ( 
DEPARTMENT_ID integer NOT NULL UNIQUE, 
DEPARTMENT_NAME varchar(30) NOT NULL, 
MANAGER_ID integer DEFAULT NULL, 
LOCATION_ID integer DEFAULT NULL, 
PRIMARY KEY (DEPARTMENT_ID,MANAGER_ID) 
);


INSERT INTO departments VALUES(60,'SALES',201,89);
INSERT INTO departments VALUES(61,'ACCOUNTS',201,89);
INSERT INTO departments VALUES(80,'FINANCE',211,90);

postgres=# SELECT * FROM departments;
 department_id | department_name | manager_id | location_id
---------------+-----------------+------------+-------------
            60 | SALES           |        201 |          89
            61 | ACCOUNTS        |        201 |          89
            80 | FINANCE         |        211 |          90
(3 rows)

Sample table employees.

CREATE TABLE employees ( 
EMPLOYEE_ID integer NOT NULL PRIMARY KEY, 
FIRST_NAME varchar(20) DEFAULT NULL, 
LAST_NAME varchar(25) NOT NULL, 
JOB_ID varchar(10) NOT NULL, 
SALARY decimal(8,2) DEFAULT NULL, 
MANAGER_ID integer DEFAULT NULL, 
DEPARTMENT_ID integer DEFAULT NULL, 
FOREIGN KEY(DEPARTMENT_ID,MANAGER_ID) 
REFERENCES  departments(DEPARTMENT_ID,MANAGER_ID)
);

Now insert the rows in the employees.

INSERT INTO employees VALUES(510,'Alex','Hanes','CLERK',18000,201,60);
INSERT INTO employees VALUES(511,'Kim','Leon','CLERK',18000,211,80);

postgres=# SELECT * FROM employees;
 employee_id | first_name | last_name | job_id |  salary  | manager_id | department_id
-------------+------------+-----------+--------+----------+------------+---------------
         510 | Alex       | Hanes     | CLERK  | 18000.00 |        201 |            60
         511 | Kim        | Leon      | CLERK  | 18000.00 |        211 |            80
(2 rows)

The value against department_id and manager_id combination (60,201) and (80,211) are unique in the departmentis(parent) table so, there is no problem arise to insert the rows in the child table employees.

Now insert another row in the employees table.

INSERT INTO employees VALUES(512,'Kim','Leon','CLERK',18000,80,211);

postgres=# INSERT INTO employees VALUES(512,'Kim','Leon','CLERK',18000,80,211);
ERROR:  insert or update on table "employees" violates foreign key constraint "employees_department_id_fkey"
DETAIL:  Key (department_id, manager_id)=(211, 80) is not present in table "departments".

Here in the above, the value against department_id and manager_id combination (211,80) does not matching with the same combination in departments(parent table) table and that is why the child table employees can not contain the combination of values including department_id and manager_id as specified. Here the primary key - foreign key relationship is being violated and shows the above message.

Sample table departments.


CREATE TABLE departments ( 
DEPARTMENT_ID integer NOT NULL UNIQUE, 
DEPARTMENT_NAME varchar(30) NOT NULL, 
MANAGER_ID integer DEFAULT NULL, 
LOCATION_ID integer DEFAULT NULL, 
PRIMARY KEY (DEPARTMENT_ID) 
);


INSERT INTO departments VALUES(60,'SALES',201,89);
INSERT INTO departments VALUES(61,'ACCOUNTS',201,89);


postgres=# select * from departments;
 department_id | department_name | manager_id | location_id
---------------+-----------------+------------+-------------
            60 | SALES           |        201 |          89
            61 | ACCOUNTS        |        201 |          89
(2 rows)

Sample table jobs.

CREATE TABLE jobs ( 
JOB_ID integer NOT NULL UNIQUE PRIMARY KEY, 
JOB_TITLE varchar(35) NOT NULL DEFAULT ' ', 
MIN_SALARY decimal(6,0) DEFAULT 8000, 
MAX_SALARY decimal(6,0) DEFAULT 20000
);


INSERT INTO jobs(JOB_ID,JOB_TITLE) VALUES(1001,'OFFICER');
INSERT INTO jobs(JOB_ID,JOB_TITLE) VALUES(1002,'CLERK');

postgres=# SELECT * FROM jobs;
 job_id | job_title | min_salary | max_salary
--------+-----------+------------+------------
   1001 | OFFICER   |       8000 |      20000
   1002 | CLERK     |       8000 |      20000
(2 rows)


Sample table employees.

CREATE TABLE employees ( 
EMPLOYEE_ID integer NOT NULL PRIMARY KEY, 
FIRST_NAME varchar(20) DEFAULT NULL, 
LAST_NAME varchar(25) NOT NULL, 
DEPARTMENT_ID integer DEFAULT NULL, 
FOREIGN KEY(DEPARTMENT_ID) 
REFERENCES  departments(DEPARTMENT_ID),
JOB_ID integer NOT NULL, 
FOREIGN KEY(JOB_ID) 
REFERENCES  jobs(JOB_ID),
SALARY decimal(8,2) DEFAULT NULL
);

Now insert the rows into the table employees.

INSERT INTO employees VALUES(510,'Alex','Hanes',60,1001,18000);

postgres=# SELECT * FROM employees;
 employee_id | first_name | last_name | department_id | job_id |  salary
-------------+------------+-----------+---------------+--------+----------
         510 | Alex       | Hanes     |            60 |   1001 | 18000.00
(1 row)

Here in the above insert statement the child column department_id and job_id of child table employees are successfully referencing with the department_id and job_id column of parent tables departments and jobs respectively, so no problem have been arisen to the insertion.

Now insert another row in the employees table.

INSERT INTO employees VALUES(511,'Tom','Elan',60,1003,22000);

postgres=# INSERT INTO employees VALUES(511,'Tom','Elan',60,1003,22000);
ERROR:  insert or update on table "employees" violates foreign key constraint "employees_job_id_fkey"
DETAIL:  Key (job_id)=(1003) is not present in table "jobs".

Here in the above insert statement show that, within child columns department_id and job_id of child table employees, the department_id are successfully referencing with the department_id of parent table departments but job_id column are not successfully referencing with the job_id of parent table jobs, so the problem have been arisen to the insertion displayed an error message.

Now insert another row in the employees table.

INSERT INTO employees VALUES(511,'Tom','Elan',80,1001,22000);

postgres=# INSERT INTO employees VALUES(511,'Tom','Elan',80,1001,22000);
ERROR:  insert or update on table "employees" violates foreign key constraint "employees_department_id_fkey"
DETAIL:  Key (department_id)=(80) is not present in table "departments".

Here in the above insert statement show that, within child columns department_id and job_id of child table employees, the job_id are successfully referencing with the job_id of parent table jobs but department_id column are not successfully referencing with the department_id of parent table departments, so the problem have been arisen to the insertion and displayed the error message.