Insert The Data Into the Postgres Database From Text File

Here i'm Explained about How to insert the data from text file  to postgres database.

PRACTICAL1.insert the data into the database from text file 

Step1.create the text file using vi text editor

[root@p1 ~]# vi /opt/PostgreSQL/9.3/data/new.txt
[root@p1 ~]# cat /opt/PostgreSQL/9.3/data/new.txt
1,akash
2,varun
3,makash
4,nijam
5,benz

step2.create the table as per text file.

postgres=# create table dept(id integer,name text);
CREATE TABLE

step3.copy the data from new.txt file

postgres=# copy dept from '/opt/PostgreSQL/9.3/data/new.txt' with delimiter ',';
ERROR:  could not open file "/opt/PostgreSQL/9.3/data/new.txt" for writing: Permission denied

Becouse new.txt file is root user file so we need to give permission to postgres user following step
Solution:

[root@p1 ~]# chown postgres:postgres /opt/PostgreSQL/9.3/data/new.txt
[root@p1 ~]# cd /opt/PostgreSQL/9.3/data/

[root@p1 data]# ls -lrt new.txt 
-rw-r--r-- 1 postgres postgres 43 Apr  3 16:44 new.txt

postgres=# copy dept from '/opt/PostgreSQL/9.3/data/new.txt' with delimiter ',';
COPY 5
postgres=# select * from dept;
 id |  name  
----+--------
  1 | akash
  2 | varun
  3 | makash
  4 | nijam
  5 | benz
(5 rows)

PRACTICAL 2. this is same as above example  here i added only one extra column.

[root@p1 data]# vi nijam.csv

[root@p1 data]# cat nijam.csv 
1,      mala,20-SEP-90 00:00:00
2,      tarun,10-OCT-08 00:00:00
3,      lara,12-JAN-09 00:00:00
4,      sara,08-MAY-01 00:00:00
5,      guttu,10-MAY-09 00:00:00

[root@p1 data]# ls -lrt nijam.csv 
-rw-r--r-- 1 root root 174 Apr  3 17:22 nijam.csv

[root@p1data]# pwd
/opt/PostgreSQL/9.3/data/

[root@p1 data]# chown postgres:postgres /opt/PostgreSQL/9.3/data/nijam.csv 
[root@p1 data]# ls -lrt nijam.csv 
-rw-r--r-- 1 postgres postgres 174 Apr  3 17:22 nijam.csv


postgres=# create table emp_d(id integer,name text, dob date);
CREATE TABLE

postgres=# copy emp_d from '/opt/PostgreSQL/9.3/data/nijam.csv' with delimiter ',';
COPY 5
postgres=# select * from emp_d;
 id |    name     |    dob     
----+-------------+------------
  1 |       mala  | 1990-09-20
  2 |       tarun | 2008-10-10
  3 |       lara  | 2009-01-12
  4 |       sara  | 2001-05-08
  5 |       guttu | 2009-05-10
(5 rows)