Load data from a text file into PostgreSQL Database

We can load data from text(flat) file,CSV(comma separated values), excel or from other database engines(Oracle,MySQL,..). Follow steps covers Load data from a text file into PostgreSQL Database.

Syntax:

COPY table_name [ ( column_name [, ...] ) ] FROM { 'filename' | STDIN } [ [ WITH ] ( option [, ...] ) ]

Where option can be:

FORMAT format_name
OIDS [ boolean ]
FREEZE [ boolean ]
DELIMITER ‘delimiter_character’
NULL ‘null_string’
HEADER [ boolean ]
QUOTE ‘quote_character’
ESCAPE ‘escape_character’
FORCE_QUOTE { ( column_name [, …] ) | * }
FORCE_NOT_NULL ( column_name [, …] )
FORCE_NULL ( column_name [, …] )
ENCODING ‘encoding_name’

Steps to Load data from a text file into PostgreSQL Database:

1. Create table address.

create table address(name varchar(80), age int, dob date,village varchar(8), locality varchar(80),district varchar(80), state varchar(40), pin int);

2. Test data of table address.

select * from address; name | age | dob | village | locality | district | state | pin ------+-----+-----+---------+----------+----------+-------+----- (0 rows)

3. Save following data into a flat file(text) with the name address_data.txt
David,43,1972-10-23,Elchuru,Addanki,Prakasam,AP,544421
George,53,1962-10-23,London,London,LN,LN,544421
David,28,1982-10-23,Ongole,Ongole,Prakasam,AP,520421

4. Load flat file data into table address.

copy address from 'C:/P Files/address_data.txt' ( DELIMITER(',') );

5.Validate data is copied or not to the table address

select * from address;

name | age | dob | village | locality | district | state | pin --------+-----+------------+---------+----------+----------+-------+-------- David | 43 | 1972-10-23 | Elchuru | Addanki | Prakasam | AP | 544421 George | 53 | 1962-10-23 | London | London | LN | LN | 544421 David | 28 | 1982-10-23 | Ongole | Ongole | Prakasam | AP | 520421

Responses are currently closed, but you can trackback from your own site.

Comments are closed.

Powered by k2schools
%d bloggers like this: