0

My goal is to move my data from mongodb to postgresql 9.1.13. I used mongoexport to export my collections from mongodb to csv files. Later I was creating tables with pgAdmin 1.18.1 and importing from csv files using pgAdmin's import.

It worked fine till I reached the csv with datetime fields. In csv file the column (called "start" in my case) has the following format 2014-02-04T19:50:35Z. The format I have set for the corresponding column in pgAdmin is timestamp without time zone (I have tried also timestamp with time zone). What I get from pgAdmin is:

ERROR:  invalid input syntax for type timestamp: "start"
CONTEXT:  COPY context, line 1, column start_time: "start"

My question is: shall I export from mongodb somehow differently or shall I alter the column type in pgAdmin?

I have checked this, this and this solutions, but not sure what is a good way to go, because I am going to have more imports from mongodb and want to use the most standard, universal timestamps format and avoid defining custom datetime formats.

P.S. I am a noob in SQL and postgres.

Community
  • 1
  • 1
gevra
  • 737
  • 2
  • 14
  • 26
  • That's really odd, `2014-02-04T19:50:35Z` is accepted by PostgreSQL http://sqlfiddle.com/#!15/d41d8/2546 -- it seems, import may want to parse a `'start'` string as a timestamp. Did you checked the *Header* checkbox at *Misc. options*? – pozs Jul 11 '14 at 12:35
  • aaaaaaaaaaah, thanks @pozs! that was the Header checkbox at Misc. options. After checking it, it worked... my first day with postgres... Answer the question, so I can mark it as solved – gevra Jul 11 '14 at 12:39
  • then you should check your already imported tables too, they may contain header rows. – pozs Jul 11 '14 at 12:41
  • yupp, that was the case... merci – gevra Jul 11 '14 at 12:45

2 Answers2

1

You should use the the Header checkbox at Misc. options, if your csvs contains headers.

You should check your already imported tables too, they may contain header rows.

pozs
  • 34,608
  • 5
  • 57
  • 63
0

Use the HEADER option to the copy command to make it ignore the first line

http://www.postgresql.org/docs/current/static/sql-copy.html

enter image description here

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260