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

From: Andy Colson <andy(at)squeakycode(dot)net>
To: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
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:32:53
Message-ID: 4F6146C5.6040708@squeakycode.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andy Colson 2012-03-15 01:37:49 Re: copy in date string "00-00-00 00:00:00"
Previous Message Scott Marlowe 2012-03-15 01:16:19 Re: copy in date string "00-00-00 00:00:00"