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@xxxxxxxxxxx> 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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
