[GENERAL] Newbie Copy From not working

[Walter Vaughan]

For the past few months we have been experimenting with using MySQL with Apache 
OFBiz. However, we are not sure that we can live with the performance.


We have a large dataset that we wanted to import into PostgreSQL, but it seems 
to fail no matter what we do. We tried ever nuance we could to get this file loaded.


Admin_=# COPY DATA_IMPORT_CUSTOMER FROM '/tmp/sold.pg' delimiter '|' null '';
ERROR:  missing data for column processed_timestamp
CONTEXT:  COPY data_import_customer, line 1: (xxx) xxx-||Mary|Smith|76 
Crest Street||Jersey City|NJ|07302-|New Jersey|USA||XXX|XXX-1...


The data looks like this

(xxx) xxx-||Mary|Smith|76 Crest Street||Jersey City|NJ|07302-|New J
ersey|USA||XXX|XXX-|||40401234567890|0604|.00|0.00|

The problem is I need to load a field that is allowed to be null with nothing 
and it work


  Table public.data_import_customer
 Column  |   Type   | Modifiers
-+--+---
 customer_id | character varying(20)| not null
 company_name| character varying(100)   |
 first_name  | character varying(100)   |
 last_name   | character varying(100)   |
 address1| character varying(255)   |
 address2| character varying(255)   |
 city| character varying(100)   |
 state_province_geo_id   | character varying(20)|
 postal_code | character varying(60)|
 state_province_geo_name | character varying(20)|
 country_geo_id  | character varying(20)|
 primary_phone_country_code  | character varying(10)|
 primary_phone_area_code | character varying(10)|
 primary_phone_number| character varying(60)|
 secondary_phone_country_code| character varying(10)|
 secondary_phone_area_code   | character varying(10)|
 secondary_phone_number  | character varying(60)|
 fax_country_code| character varying(10)|
 fax_area_code   | character varying(10)|
 fax_number  | character varying(60)|
 credit_card_number  | character varying(60)|
 credit_card_exp_date| character varying(60)|
 outstanding_balance | numeric(18,2)|
 billing_account_limit   | numeric(18,2)|
 ship_to_company_name| character varying(100)   |
 ship_to_first_name  | character varying(100)   |
 ship_to_last_name   | character varying(100)   |
 ship_to_address1| character varying(255)   |
 ship_to_address2| character varying(255)   |
 ship_to_city| character varying(100)   |
 ship_to_state_province_geo_id   | character varying(20)|
 ship_to_postal_code | character varying(60)|
 ship_to_state_province_geo_name | character varying(20)|
 ship_to_country_geo_id  | character varying(20)|
 processed_timestamp | timestamp with time zone |
 primary_party_id| character varying(20)|
 company_party_id| character varying(20)|
 person_party_id | character varying(20)|
 last_updated_stamp  | timestamp with time zone |
 last_updated_tx_stamp   | timestamp with time zone |
 created_stamp   | timestamp with time zone |
 created_tx_stamp| timestamp with time zone |
Indexes:
pk_data_import_customer PRIMARY KEY, btree (customer_id)
dt_impt_cstr_txcrs btree (created_tx_stamp)
dt_impt_cstr_txstp btree (last_updated_tx_stamp)


Thanks!
--
Walter

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Newbie Copy From not working

[Terry Lee Tucker]

On Thursday 17 August 2006 03:27 pm, Walter Vaughan 
[EMAIL PROTECTED] thus communicated:
 The problem is I need to load a field that is allowed to be null with
 nothing and it work

The above statement is not the problem you are having. We dumped and loaded a 
4 gig Progress database with copy and there were many instances of 
||value||more|and more| in the dump file. There is something else wrong with 
the line of data.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Newbie Copy From not working

[Michael Fuhr]

On Thu, Aug 17, 2006 at 03:27:26PM -0400, Walter Vaughan wrote:
 Admin_=# COPY DATA_IMPORT_CUSTOMER FROM '/tmp/sold.pg' delimiter '|' null 
 '';
 ERROR:  missing data for column processed_timestamp
 CONTEXT:  COPY data_import_customer, line 1: (xxx) xxx-||Mary|Smith|76 
 Crest Street||Jersey City|NJ|07302-|New Jersey|USA||XXX|XXX-1...

The table you posted has 42 columns; at least one line in the file
doesn't have that many fields.  Here's an easy way to count the
number of fields on each line in the file:

awk -F'|' '{print NR, NF}' /tmp/sold.pg

Suggestion: fix the file so each line has the same number of fields
as the table has columns.

-- 
Michael Fuhr

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Newbie Copy From not working

[Walter Vaughan]

Michael Fuhr wrote:


On Thu, Aug 17, 2006 at 03:27:26PM -0400, Walter Vaughan wrote:

ERROR:  missing data for column processed_timestamp



The table you posted has 42 columns; at least one line in the file
doesn't have that many fields.


Yes, we didn't have the right number of columns...

We've had only experience with MySQL's tools which are much less picky. :(
We've also discovered that it will also stop on fields that are too wide. I'm 
fairly sure that its better for the transaction to fail and tell us that it 
would be chopping off data rather than to just go on silently and stripping off 
data...


Thanks as well to terry at esc1.com for helping.

--
Walter


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match