Copying CSV into Postgres table - invalid input syntax for type timestamp with time zone: “1”

Question

I'm creating a new table in Postgres that I want to copy data into, from a CSV file. The CSV data is like this:

nodeid,usertoken,application,starttime,endtime,count1,count2,count3,count4,count5,utctimestamp,timestamp
ABCDE,84a6f486-97f1-4449-ad63-ae36e51392bd,AB,2019-09-15 00:09:10.365+00,2019-09-15 00:11:57.403+00,1,0,0,1,2,2019-09-15 00:11:57.403+00,2019-09-15 00:11:57.403+00

I create the Postgres table like this:

CREATE TABLE test_table

(

  nodeid text,

  usertoken text,

  application text,

  starttime timestamp with time zone,

  endtime timestamp with time zone,

  count1 integer,

  count2 integer,

  count3 integer,

  count4 integer,

  count5 integer,

  utctimestamp timestamp with time zone,

  "timestamp" timestamp with time zone,

  msgid serial NOT NULL,

  CONSTRAINT test_table_pkey PRIMARY KEY (msgid)

)

WITH (

  OIDS=FALSE

);

I then use this command using psql, to try to copy the data from the CSV file (where myfilename is the name of the csv file)

COPY test_table(nodeid, usertoken, application, starttime, endtime, count1, count2, count3, count4, count5, utctimestamp, timestamp) FROM 'myfilename.csv' DELIMITER ',' CSV HEADER;

I get the error:

ERROR:  invalid input syntax for type timestamp with time zone: "1"
CONTEXT:  COPY test_table, line 2, column endtime: "1"

The error appears to be indicating that the count1 value is being used as the value for insertion as endtime, however I'm not seeing why that would be the case

which version of Postgres is this? I tried on my end with v.11.5, and it works fine. One possibility is that you may have two copies of myfilename.csv, and you're reading from the wrong one -- maybe try using an absolute path to the one you think you're using? — richyen

Chris Halcrow Chris Halcrow · Accepted Answer · 2019-12-06T03:10:05

Thanks @richyen for the hint. I had previously been using .csv file that had the date data in a different format. I was copying a new .csv file to the Postgres server, however there turned out to be a cacheing issue that meant that the new file wasn't actually being copied. I was able to see this by using head filename.csv to inspect the csv file. Terminating the linux shells I was using and starting again with new sessions allowed me to successfully copy the new file.

Copying CSV into Postgres table - invalid input syntax for type timestamp with time zone: “1”

1 Answers