Postgresql - merge time and date of csv on import to timestamp

Question

I'm trying to import data from a csv file into postgresql, unfortunately it's not perfectly formatted.

Example:

DATE,TIME,NUMERIC,NUMERIC,NUMERIC,NUMERIC,INTEGER

I want to import the data into a table with the columns:

timestamp with timezone, numeric, numeric, numeric, numeric, integer

For importing the data I use copy:

COPY tabledata FROM 'c:\Users\Public\Downloads\test.csv' DELIMITERS ',' CSV;

Of course it does not work, since date and time are separated by ',' so postgresql assumes that those are 2 separate data fields.

I've managed it to import the .csv into a table that is setup just like the .csv, however I need to have date and time as timestamp with timezone later on, and because I will be handling huge amounts of data a post import conversion would be my last choice because of performance issues.

I've tried to edit the .csv file prior to import with "sed 's/,/ /' EURUSD30.csv > EURUSD30E.csv" however I get an error saying that the command "'s/" is typed wrong or could not be found. I'm rather clueless because this seems to work for everybody else, but this would not be my first choice either.

It would be easier if postgresql could simply be told upon import to merge time and date into one timestamp value. Any ideas?

Thanks

The copy command doesn't have any capacity for changing the data. If you are looking to do it as it imports, you could try adding a trigger to the table which would fire after each row insert and merge the columns. Unfortunately, in the end, that would not be any better performance-wise than doing the operation after the import. Your best bet is to clean up the CSV file prior to importing. — jcern
– jcern, Commented Oct 2, 2012 at 13:25

Craig Ringer · Accepted Answer · 2012-10-02 13:36:37Z

4

Your best bet is to COPY the data to a TEMPORARY or UNLOGGED table, and from there do an:

INSERT INTO real_table
SELECT col1, col2, col3, datecol + timecol, col6, ...
FROM temp_copy_table;

You can do any other required data massaging during that process too.

Consider timezones when merging. DATE + TIME produces a timestamp without time zone:

regress=# SELECT pg_typeof(DATE '2012-02-01' + TIME '10:00');
          pg_typeof          
-----------------------------
 timestamp without time zone
(1 row)

and you probably want to store a timestamp with time zone, so you might want to tell Pg to interpret the date+time as being at a particular time zone with:

(datecol + timecol) AT TIME ZONE 'UTC'

or whatever the local time zone of the time stamps is.

answered Oct 2, 2012 at 13:36

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Moseleyi Over a year ago

The best option to go for if you have to mix data from csv and for example timestamps of now. Also it allows you to verify data in the temp table before merging to the destination table

Collectives™ on Stack Overflow

Postgresql - merge time and date of csv on import to timestamp

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related