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

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

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory

PostgreSQL pgBadger

PostgreSQL Database startup / shutdown /restart

How to configure Replication Manager (repmgr) ?