Can you show the output of \d geo_data ? Try 'using' delimiters Are you doing this as the postgres superuser? Because COPY can't load from files as a casual user, you need to pipe it to copy & read from stdin. Simple script below works for me, modified copy statement might help?. HTH, Brent Wood #! /bin/bash DB=test psql -d $DB -c "drop table geo_data;" # latitude has only one 't' psql -d $DB -c "create table geo_data ( zip_code text, latitude float8, longitude float8, city text, state text, county text);" echo "96799,-7.209975,-170.7716,PAGO PAGO,AS,AMERICAN SAMOA 96941,7.138297,151.503116,POHNPEI,FM,FEDERATED STATES OF MICRO 96942,7.138297,151.503116,CHUUK,FM,FEDERATED STATES OF MICRO" | \ psql -d $DB -c "copy geo_data from stdin using delimiters ',' null '';" psql -d $DB -c "select * from geo_data;" Output: zip_code | latitude | longitude | city | state | county ----------+-----------+------------+-----------+-------+--------------------------- 96799 | -7.209975 | -170.7716 | PAGO PAGO | AS | AMERICAN SAMOA 96941 | 7.138297 | 151.503116 | POHNPEI | FM | FEDERATED STATES OF MICRO 96942 | 7.138297 | 151.503116 | CHUUK | FM | FEDERATED STATES OF MICRO (3 rows) Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> Bryan Nelson <shrek@xxxxxxxxxxxxxxxx> 07/16/11 7:15 AM >>> I am having problems importing a CSV file of sample data for testing in a web app. Columns & Types ------------------- zip_code - text lattitude - float8 longitude - float8 city - text state - text county - text Some Sample Data From CSV File ------------------------------ 96799,-7.209975,-170.7716,PAGO PAGO,AS,AMERICAN SAMOA 96941,7.138297,151.503116,POHNPEI,FM,FEDERATED STATES OF MICRO 96942,7.138297,151.503116,CHUUK,FM,FEDERATED STATES OF MICRO COPY Command ------------ COPY geo_data FROM 'geo_data2.csv' DELIMITERS ',' CSV; Error Message ------------- ERROR: invalid input syntax for integer: "96799" CONTEXT: COPY geo_data, line 1, column id: "96799" I can't figure out why it keeps choking with "invalid syntax for integer" since the field was created as "text". Any and all help greatly appreciated! -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Please consider the environment before printing this email.
NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. |
