0

When I try to write the following date time format to Postgres using Pgadmin 2018-04-18 05:40:28

I get the following error. ERROR: invalid input syntax for type timestamp: "2018-04-18 05:40:28" CONTEXT: COPY timestamp, line 1, column date: "2018-04-18 05:40:28" I am trying to write the data using the timestamp format within Postgres. Any pointers on where I am going wrong would be much appreciated. Thank you.

Keith C.
  • 1
  • 1
    Show us your `insert` statement –  May 26 '18 at 09:05
  • Here it is, thanks. ALTER TABLE public."timestamp" ADD COLUMN date2 timestamp; – Keith C. May 26 '18 at 09:08
  • 1
    That's not the statement that generates the error (and it's not an insert statement to begin with) –  May 26 '18 at 09:26
  • I'm using the import facility in PGAdmin, " C:\\Program Files (x86)\\pgAdmin 4\\v3\\runtime\\psql.exe" --command " "\\copy public.\"timestamp\" (date) FROM 'C:/data/SEDEX2~1/TIMEST~1.TXT' CSV QUOTE '\"' ESCAPE '''';"" Not sure if that's enough or perhaps I should try using an import statement instead of the import utility – Keith C. May 26 '18 at 09:57
  • @KeithC.: All defining information should go into the *question*. Please [edit] it. And have a look at this helpful site: https://stackoverflow.com/editing-help – Erwin Brandstetter May 26 '18 at 14:21

1 Answers1

0

My educated guess: you have a leading BOM (byte order mark) in the file that should be removed.

Or some exotic whitespace or non-printing character that should be removed or replaced.

And the offending character (well, the BOM is not a "character", strictly speaking, it's just mistaken for one) was not copied to the question. That would explain the otherwise contradicting error message.

To test, copy the "2018-04-18 05:40:28" part from the error message and paste it in a pgAdmin SQL editor window (which you seem to be using) and test:

SELECT '"2018-04-18 05:40:28"' = '"2018-04-18 05:40:28"';
---------^  BOM here?

I added a leading BOM to demonstrate in the first string. Type the second string by hand to be sure it's plain ASCII. If you get false, we are on to something here.

But we cannot be too sure, your question is confusing and essential information is missing. Don't use the basic type names timestamp and date as identifiers, for sanity.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thank you Erwin, I realized eventually that I had to change the timestamp format to match my data e.g. DMY to YMD. Easily done. Will add the additional info to my earlier question. – Keith C. Jun 06 '18 at 13:04