Importing CSV files into Postgres with datetime field that contains milliseconds?

Question

I need to import a CSV file into Postgres (version 9.6.3) and I need help in understanding the best way to do this.

The formatting of the CSV is as is shown below and as well as understanding the best way to actually import the file, I am also unsure of the datatype I should use in the table for storing the 'time' field as it contains milliseconds and is formatted as 'YYYY.MM.DD HH:MM:SS.MS'

Time,Col1,Col2,Col3,Col4
2017.05.01 00:00:02.851,1.09062,1.09057,4.35,5.42
2017.05.01 00:00:03.368,1.09062,1.09058,3.22,1
...

I have the pgadmin client so could use that but I am also open to using raw SQL from the command line or using Python (Python 3.6) to create the table and import the data.

The files I would like to import range in size from 20mbs to hundreds of mbs in size so I would like to find the quickest method of doing this.

klin · Accepted Answer · 2017-05-28 13:09:20Z

2

Your table may look like this (choose one of the numeric types for the columns):

create table my_table(
    time timestamp, 
    col1 numeric, 
    col2 numeric, 
    col3 numeric, 
    col4 numeric);

Use copy command, e.g.:

copy my_table from '/data/my_file.csv' (format csv, header);

select * from my_table;

          time           |  col1   |  col2   | col3 | col4 
-------------------------+---------+---------+------+------
 2017-05-01 00:00:02.851 | 1.09062 | 1.09057 | 4.35 | 5.42
 2017-05-01 00:00:03.368 | 1.09062 | 1.09058 | 3.22 |    1
(2 rows)

If the file is very large you can import it to the unlogged table and after that alter table to logged (see alter table). This can reduce the time of the import.

alter table my_table set unlogged;
copy my_table from '/data/my_file.csv' (format csv, header);
alter table my_table set logged;

The operating system user who owns Postgres must have read access to the file.

edited May 28, 2017 at 13:09

answered May 28, 2017 at 13:02

klin

123k15 gold badges241 silver badges263 bronze badges

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

2 Comments

Mark Smith Over a year ago

Thank you Klin, I will test this out shortly and mark as 'correct answer' if all goes well. I am unsure if the formatting of the time field in the CVS file will import as in your example is shows YYYY MM, etc as separated using dashes '-' but in my CSV it uses full stops/periods '.' - I'm hoping that this wont cause any problems and will be converted during the import process... thanks for your help it is appreciated...

Mark Smith Over a year ago

Thanks Klin, I have just completed the import using the info you very kindly supplied above - I have marked this as the correct answer. Thank you

Collectives™ on Stack Overflow

Importing CSV files into Postgres with datetime field that contains milliseconds?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related