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

Mastering SQL using Postgresql

Exercise - Loading Data¶