From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | Andy Colson <andy(at)squeakycode(dot)net> |
Cc: | Mark Phillips <Mark(dot)Phillips(at)mophilly(dot)com>, PostgreSQL <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: copy in date string "00-00-00 00:00:00" |
Date: | 2012-03-15 01:46:49 |
Message-ID: | CAOR=d=0epMk4eJabcjKy6EbqQUKH+H7U_Md_6iR=PFZVe5VY0Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Mar 14, 2012 at 7:37 PM, Andy Colson <andy(at)squeakycode(dot)net> wrote:
> On 03/14/2012 08:32 PM, Andy Colson wrote:
>>
>> On 03/14/2012 08:16 PM, Scott Marlowe wrote:
>>>
>>> On Wed, Mar 14, 2012 at 6:47 PM, Mark Phillips
>>> <Mark(dot)Phillips(at)mophilly(dot)com> wrote:
>>>>
>>>> I am migrating a data set from Oracle 8i to PG 9.1. The process is to
>>>> export data into csv files, then use the pg "copy table from file csv
>>>> header" statement to load the tables.
>>>>
>>>> There are a number of date columns in the tables that include empty
>>>> value (null), valid dates, and some with the time component only. The empty
>>>> values are being output as 00-00-00 00:00:00.
>>>>
>>>> The import is falling over on rows that contain these "zero" dates.
>>>>
>>>> I can adjust the NLS session format of the date string, within a small
>>>> range, in the oracle environment. However, each form I have attempted still
>>>> results in these "zero" date values in the csv file.
>>>>
>>>> I am thinking of run the csv files through a filter to change the
>>>> "00-00-00 00:00:00" to an empty value.
>>>>
>>>> Is there a way for postgres to handle this?
>>>
>>>
>>> Can you run it through sed and replace the "0000-00-00 00:00:00" to
>>> NULL (no quotes) ? That should work.
>>>
>>
>> I think COPY (depending on arguments) uses \N by default.
>>
>> Another option is to pull it into a temp table and make fix it up from
>> there.
>>
>> -Andy
>>
>
> humm.. and speaking of arguments, Mark, did you check the help?
>
>
> where option can be one of:
>
> NULL 'null_string'
>
> so, perhaps just:
>
> COPY tbl from 'dump.csv' with NULL '00-00-00 00:00:00';
Thought of that one too, but it'll break all the other fields if they
have NULLs in them.
From | Date | Subject | |
---|---|---|---|
Next Message | Aleksey Tsalolikhin | 2012-03-15 02:24:57 | Re: A 154 GB table swelled to 527 GB on the Slony slave. How to compact it? |
Previous Message | Andy Colson | 2012-03-15 01:37:49 | Re: copy in date string "00-00-00 00:00:00" |