Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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)

Comments

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

vacuumlo - removing large objects orphans from a database PostgreSQL