PostgreSQL Import and Export on CSV

Import and Export on CSV

Create CSV File:

[root@server1 /]# mkdir import_dir
[root@server1 /]# chown -R postgres. import_dir/
[root@server1 /]# chmod 700 import_dir/
[root@server1 /]# cd import_dir/
[root@server1 import_dir]# cat siva.csv
id,name,age
1,muthu,21
2,siva,26
3,thanigai,27
4,chandru,28
5,kathir,27
[root@server1 import_dir]# chown -R postgres. siva.csv

Syntax:

\copy table_name from <location + filename> delimiter ',' HEADER;

\copy //perform SQL COPY with data stream to the client host
table_name //Table Name
from //import from a file
to //in order to export it to a file
<location + filename> //location and file name
delimiter ',' //Specifies the delimiter, which in our case is a comma: ‘,’
CSV //Specifies the file type from which we are going to import
HEADER //Signifies that we have a header row in our .csv file and while importing we should ignore the first row (similarly, while exporting we can use this to specify whether we want to include or exclude the header file)

Import Csv File:

pearl=# create table siva(id int,name text,age int);
CREATE TABLE
pearl=# \copy siva from /import_dir/siva.csv delimiter ',' csv HEADER;
COPY 5
pearl=# SELECT * FROM SIVA;
 id |   name   | age
----+----------+-----
  1 | muthu    |  21
  2 | siva     |  26
  3 | thanigai |  27
  4 | chandru  |  28
  5 | kathir   |  27
(5 rows)

Export CSV File:

pearl=# \copy siva to /import_dir/siva_ex.sql delimiter ',' csv HEADER;
COPY 5

[root@server1 import_dir]# cat siva_ex.sql
id,name,age
1,muthu,21
2,siva,26
3,thanigai,27
4,chandru,28
5,kathir,27