Exercise – Loading Data

As part of this exercise, you need to take care of loading data using COPY Command.

• You can connect to the database using the following details in the environment provided by us.

  • Host: localhost

  • Port: 5342

  • Database Name: YOUR_OS_USER_hr_db

  • User Name: YOUR_OS_USER_hr_user

  • Password: YOUR_OS_USER_PASSWORD (provided by us).

• If you are using your own environment, make sure to create database for storing HR Data.

  • Database Name: hr_db

  • User Name: hr_user

  • You can create user with password of your choice.

CREATE DATABASE hr_db;
CREATE USER hr_user WITH PASSWORD 'hr_password';
GRANT ALL ON DATABASE hr_db TO hr_user;
GRANT pg_read_server_files TO hr_user;

• Create table using this script.

CREATE TABLE employees
   ( employee_id INTEGER
   , first_name VARCHAR(20)
   , last_name VARCHAR(25)
   , email VARCHAR(25)
   , phone_number VARCHAR(20)
   , hire_date DATE
   , job_id VARCHAR(10)
   , salary NUMERIC(8,2)
   , commission_pct NUMERIC(2,2)
   , manager_id INTEGER
   , department_id INTEGER
   ) ;
CREATE UNIQUE INDEX emp_emp_id_pk
         ON employees (employee_id) ;
ALTER TABLE employees ADD
   PRIMARY KEY (employee_id);

• Understand data.

  • Check for delimiters (record as well as field).

  • Check whether header exists or not.

  • Ensure number of fields for the table and data being loaded are same or not.

• Load data into the table using COPY Command. The file is under /data/hr_db/employees

• Validate by running these queries. You can also use SQL Workbench to run the queries to validate whether data is loaded successfully or not.

SELECT * FROM employees LIMIT 10;
SELECT count(1) FROM employees;