Re: copy in date string "00-00-00 00:00:00"

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.