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

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

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

7 Steps to configure BDR replication in postgresql

Postgres Streaming Replication Configuration

vacuumlo - removing large objects orphans from a database PostgreSQL