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

step2.create the table as per text file.

postgres=# create table dept(id integer,name text);
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

[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 ',';
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

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

postgres=# copy emp_d from '/opt/PostgreSQL/9.3/data/nijam.csv' with delimiter ',';
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)


Popular posts from this blog

rman interview and scenario based questions and answer and also rman(backup and recovery) discussions panel

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

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

7 Steps to configure BDR replication in postgresql

PostgreSQL pgBadger