Re: Question about copy from with timestamp format - Mailing list pgsql-general

From Sherrylyn Branchaw
Subject Re: Question about copy from with timestamp format
Date
Msg-id CAB_myF4kbPYyv0LG+mVygqPPEPGz0t86vF1QKLSJbK=xN_fV4A@mail.gmail.com
Whole thread Raw
In response to Re: Question about copy from with timestamp format  (Adrian Klaver <adrian.klaver@aklaver.com>)
Responses Re: Question about copy from with timestamp format  (Alban Hertroys <haramrae@gmail.com>)
List pgsql-general
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.klaver@aklaver.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.klaver@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Question about copy from with timestamp format
Next
From: Michael Paquier
Date:
Subject: Re: xmin horizon?