PostgreSQL :- Load text data using “Copy” statement

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

  1. Identify Destination and Source tables and views
    • Review columns
  2. 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 )
    • Review column delimiters
    • Header lines
    • Data Encoding
  3. Import or Export data
  4. Clean data
  5. 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.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s