Thread: loading data for newb
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
James Nobles <james@bolshevikhosting.com> writes:
> The file i need to load is an USPS file, the tiger zip+4.
Just in the past several days, someone offered a PG-converted version of
the TIGER database. Check the archives for the other PG mailing lists
(sorry I forget which one).
regards, tom lane
On Tue, Aug 09, 2005 at 10:52:03PM -0400, Tom Lane wrote: > James Nobles <james@bolshevikhosting.com> writes: > > The file i need to load is an USPS file, the tiger zip+4. > > Just in the past several days, someone offered a PG-converted version of > the TIGER database. Check the archives for the other PG mailing lists > (sorry I forget which one). It was on pgsql-hackers: http://archives.postgresql.org/pgsql-hackers/2005-08/msg00072.php Tim
Attachment
i do a sort by serial number. in order to sort the way i want, i need to cast serial_number (type text) as an integer. ... ORDER By serial_number::integer ASC; this works great in pgsql 7.4.x (whatever i'm using on my dev box, i think 7.4.3, but not sure), but it doesn't work in pgsql 7.3.9. it doesn't allow the query to complete and i eventually get a "non object" error. is there someting equivalent to the above line that will work in 7.3.9? i know, these are old versions of pgsql. we are going to bring the db in house and i will start working the latest and greatest pgsql and php shortly. until then, though, i'd like to display my serial numbers in a manner that looks nice to the user (iow, "10" won't come before "2"). as always, tia. __________________________________ Yahoo! Mail Stay connected, organized, and protected. Take the tour: http://tour.mail.yahoo.com/mailtour.html
On Thu, Aug 11, 2005 at 11:50:02AM -0700, operationsengineer1@yahoo.com wrote: > i do a sort by serial number. in order to sort the > way i want, i need to cast serial_number (type text) > as an integer. > > ... ORDER By serial_number::integer ASC; > > this works great in pgsql 7.4.x (whatever i'm using on > my dev box, i think 7.4.3, but not sure), but it > doesn't work in pgsql 7.3.9. > > it doesn't allow the query to complete and i > eventually get a "non object" error. What's the *exact* error message? I don't see "non object" anywhere in the 7.3 source code. What client interface are you using? The following example works for me in 7.3.10, and I don't see anything in the Release Notes that suggests the behavior has changed since 7.3.9: CREATE TABLE foo (serial_number text); INSERT INTO foo VALUES (1); INSERT INTO foo VALUES (2); INSERT INTO foo VALUES (3); INSERT INTO foo VALUES (10); INSERT INTO foo VALUES (20); INSERT INTO foo VALUES (30); SELECT * FROM foo ORDER BY serial_number::integer; serial_number --------------- 1 2 3 10 20 30 (6 rows) Maybe this example is too simple to show the problem. Could you post a complete test case? -- Michael Fuhr
--- Michael Fuhr <mike@fuhr.org> wrote: > On Thu, Aug 11, 2005 at 11:50:02AM -0700, > operationsengineer1@yahoo.com wrote: > > i do a sort by serial number. in order to sort > the > > way i want, i need to cast serial_number (type > text) > > as an integer. > > > > ... ORDER By serial_number::integer ASC; > > > > this works great in pgsql 7.4.x (whatever i'm > using on > > my dev box, i think 7.4.3, but not sure), but it > > doesn't work in pgsql 7.3.9. > > > > it doesn't allow the query to complete and i > > eventually get a "non object" error. > > What's the *exact* error message? I don't see "non > object" anywhere > in the 7.3 source code. What client interface are > you using? > > The following example works for me in 7.3.10, and I > don't see > anything in the Release Notes that suggests the > behavior has changed > since 7.3.9: > > CREATE TABLE foo (serial_number text); > INSERT INTO foo VALUES (1); > INSERT INTO foo VALUES (2); > INSERT INTO foo VALUES (3); > INSERT INTO foo VALUES (10); > INSERT INTO foo VALUES (20); > INSERT INTO foo VALUES (30); > > SELECT * FROM foo ORDER BY serial_number::integer; > serial_number > --------------- > 1 > 2 > 3 > 10 > 20 > 30 > (6 rows) > > Maybe this example is too simple to show the > problem. Could you > post a complete test case? > > -- > Michael Fuhr > my apologies, i thought serial number was type text, but it is type varchar(). i will change it to text and see what happens. this is the error... Warning: pg_exec(): Query failed: ERROR: Cannot cast type character varying to integer in /home/home/adodb/drivers/adodb-postgres64.inc.php on line 741 -1: ERROR: Cannot cast type character varying to integer it there is no error in 7.4. ____________________________________________________ Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs
On Thu, Aug 11, 2005 at 02:14:03PM -0700, operationsengineer1@yahoo.com wrote: > Warning: pg_exec(): Query failed: ERROR: Cannot cast > type character varying to integer in > /home/home/adodb/drivers/adodb-postgres64.inc.php on > line 741 > -1: ERROR: Cannot cast type character varying to > integer The error tells you what's wrong: the database doesn't know how to cast a varchar to an integer. A way around that is to cast the varchar value to text and then to integer, like this: ORDER BY serial_number::text::integer; > it there is no error in 7.4. That's odd -- 7.4.8 gives me the same "cannot cast type character varying to integer" error as 7.3.10. 8.0.3 works because 8.0 added the necessary casts. You can use "\dC" in psql to see what casts are available. -- Michael Fuhr