loading data for newb

From: James Nobles <james(at)bolshevikhosting(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: loading data for newb
Date: 2005-08-09 23:58:52
Message-ID: 1123631933.4527.12.camel@ip24-250-136-203.bc.dl.cox.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi,
I'm creating my first set up database's in postgres, my first db's every
actually, and I have some questions about how to load data into one db
in particular.

The file i need to load is an USPS file, the tiger zip+4.
here is the data layout:
Field Field Logical Relative Position
Number Description Length From/Thru
01 ZIP Code 05 01 05
02 ZIP+4 Code 04 06 09
03 TLID 10 10 19
04 Carrier Route04 20 23
05 State Code 02 24 25
06 County Code 03 26 28
07 RL Flag 01 29 29
08Census Tract Number 06 30 35
09 Census Block Number 04 36 39
10 From Latitude 09 40 48
11 From Longitude 10 49 58
12 To Latitude 09 59 67
13 To Longitude 10 68 77
14 PMSA Code 04 78 81
15 CMSA Code 04 82 85
16 Multiple Match Indicator 01 86 86
17 CRLF 02 87 88

sample data:
778011000 21813913C05148041R0006044001+30650898 -96376141+30648998
-96376541 1260
778011001 21813912C05148041R0006044000+30650898 -96376141+30650998
-96377741 1260
778011002 21813912C05148041L0006044001+30650898 -96376141+30650998
-96377741 1260
778011003 21813891C05148041R0006044000+30650998 -96377741+30651252
-96378998 1260
778011003 21813889C05148041R0006044000+30651252 -96378998+30651298
-96380241 1260Y

Could someone give me pointer on how to load the data and split the
column at the appropriate point? I've used the the COPY command but that
was for a simple two column file for county fips codes.

Thanks in advance,
James

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2005-08-10 02:38:31 Re: Getting exception information (SQLERRM, SQLSTATE, etc.)
Previous Message Lance Arlaus 2005-08-09 23:13:17 Getting exception information (SQLERRM, SQLSTATE, etc.)