Exercise – Loading Data

Topic
Materials

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;

Copy to clipboard

  • 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);

Copy to clipboard

  • 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;