Background
Let us load text into a PostgreSQL table.
Command
The command we will be using is the Copy Command.
The Copy Command is able to get data out of table.
And, it is able to add data in.
Table
Table Declaration
Code
create schema if not exists bible
;
/*
drop table if exists bible.kjv cascade
*/
create table if not exists bible.kjv
(
id int not null
GENERATED
ALWAYS AS identity
, bookID char(3) not null
, chapterID smallint not null
, verseID smallint not null
, verse varchar(4000) not null
, constraint "bible.kjv.PK"
primary key
(
bookID
, chapterID
, verseID
)
)
;
Copy Command
Outline
- Identify Destination and Source tables and views
- Review columns
- Identify data files
- Our Case
- In our case we will be using text files availed through Sacred Text
- The URL is sacred-texts.com ( Link )
- In our case we will be using text files availed through Sacred Text
- Review column delimiters
- Header lines
- Data Encoding
- Our Case
- Import or Export data
- Clean data
- Update Statistics
Syntax
COPY (table-name) ( column-list ) FROM (file-name) delimiter AS (delimiter) ;
Sample
/*
On bible.kjv => Remove Existing Data
*/
truncate table bible.kjv
RESTART identity
;
/*
On bible.kjv => Restart identity column
*/
ALTER TABLE bible.kjv
ALTER COLUMN id
RESTART WITH 1
;
/*
On bible.kjv => Copy Data
a) Specify columns
b) Delimiter is |
*/
COPY bible.kjv
(
bookID
, chapterID
, verseID
, verse
)
FROM 'V:\\sample\sample.db\bible\textfile\kjv\kjvdat.txt'
delimiter AS '|'
;
/*
On bible.kjv => Data Cleansing
a) Column verse
i) Remove leading spaces
**/
update bible.kjv
set verse = ltrim(verse)
;
/*
On bible.kjv => Update statistics
*/
analyze bible.kjv
;
Summary
Importing and Exporting text data is straight forward.
