Re: Question about copy from with timestamp format

Based on your PS asking about data types and commenting that you don't want
to put hour in a separate column, it sounds like this is a brand-new table
you're creating. If so, and if this is a one-time COPY operation, you can
create a text column for the initial import. Then after you're done
importing, you can execute

ALTER TABLE ts_test ALTER COLUMN ts_fld TYPE TIMESTAMP USING
(to_timestamp(ts_fld, 'YYYYMMDDHH24'));

to convert the format of the imported data to a timestamp. Then you're set.

If there will be ongoing imports of more files like this, though, you'll
need the intermediate table solution offered by Adrian.

I was going to suggest a trigger, but it turns out that the data type
checking happens even before the BEFORE trigger fires, so you don't get a
chance to massage your data before actually inserting it. I got 'ERROR:
date/time field value out of range: "2015072913"' before the trigger even
fired. I wonder if that's deliberate? I was able to implement a workaround
by adding a raw_ts_fld column of type text, but an extra column might be
too ugly for you relative to a temp table, I don't know.

Sherrylyn

P.S. Yes, you're right that the date data type won't work if you want to
keep the hour value in the same column.

On Wed, Jul 29, 2015 at 7:47 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 07/29/2015 03:55 PM, Murali M wrote:
>
>> How do I specify that when I use copy from? this is what I am trying
>> right now..
>> copy myTable (myTimeCol, col2) from myFile delimiter as '\t'
>>
>> I am not sure how to specify the time format..
>>
>
> My previous post would have been more useful if I had added that the
> temporary/staging table should have the 'timestamp' field set to
> varchar/text so you could get the data in.
>
>
>> thanks, murali.
>>
>>
>>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>