Importing a csv file into postgresql

Question

I have a *.csv file which I want to import into my database. basically it looks like that:

2013.11.07,11:50,1.35163,1.35167,1.35161,1.35163,15
2013.11.07,11:51,1.35166,1.35173,1.35165,1.35170,21
2013.11.07,11:52,1.35170,1.35170,1.35163,1.35163,11

I am using:

DROP TABLE table;

CREATE TABLE table
(
id SERIAL primary key,
Date Date,
Time Time,
Open double precision, 
High double precision, 
Low double precision,
Close double precision,
Volume bigint
);

COPY table FROM 'C:\\Users\\user\\EURUSD1.txt' DELIMITER ',' CSV;

However I am getting at the Date:

ERROR: invalid input syntax for integer: "07/11/2013" CONTEXT: COPY eurusd_m1, line 1, column id: "07/11/2013"

I really appreciate your help on how to fix that?

File format and Table Column order do not match neither do the number of columns. You need to create a table just like the data you want to load. — Abhinav
– Abhinav, Commented Jan 17, 2014 at 18:52
@Abhinav Thx for your answer! However, thats not correct! Number of columns of the create statement matches the csv(id is SERIAL). If I am wrong please show me! — user2051347
– user2051347, Commented Jan 17, 2014 at 18:55
Looking at the error it doesn't seem to be the case. Error says the COPY command is trying to assign Date data to id column. — Abhinav
– Abhinav, Commented Jan 17, 2014 at 18:59

yieldsfalsehood · Accepted Answer · 2014-01-17 18:58:23Z

4

Just because id is serial doesn't mean it's disregarded when loading - you should be specifying your columns in your copy statement, e.g.

COPY table (Date, Time, Open, High, Low, Close, Volume)
  FROM 'C:\\Users\\user\\EURUSD1.txt' DELIMITER ',' CSV;

answered Jan 17, 2014 at 18:58

yieldsfalsehood

3,1051 gold badge21 silver badges15 bronze badges

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

Collectives™ on Stack Overflow

Importing a csv file into postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related