Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

I'm trying to import a CSV file via pgadmin dn it keeps throwing an error on the first line

ERROR:  invalid input syntax for type bigint: "8668394"

I recognize the "" say it is is trying to import the integer as a string but I can't understand why this is the case. When I open the CSV in notepad++ it doesn't show any quote marks or formating that might be causing this. This is what I see in notepad++

8668394,Damoujia,China,Asia/Shanghai,36.54356,119.81326 CR LF
9123882,Shanwang,China,Asia/Shanghai,36.5454,118.711 CR LF
11969451,Katang Ailieke,China,Asia/Urumqi,38.15279,85.51784 CR LF
12110889,Wulong,China,Asia/Shanghai,29.32431,107.76056 CR LF
12324243,Yingqiu,China,Asia/Shanghai,36.52663,118.99285 CR LF

Does anyone have any idea what is going wrong here?

By loading an intentionally defective string, you can see that the quote marks are part of the error message framing, not part of the data being complained about. The problem is likely some control character which notepad++ doesn't make clear. Try a different tool, like od – jjanes Mar 9, 2022 at 18:25 @jjanes thank you for putting me on the right track. The issue was actually the encoding being used was UTF8 BOM, which of course hides a Byte Order Mark at the start of the file. – Aaron Harker Mar 9, 2022 at 23:27

Thanks for contributing an answer to Stack Overflow!

  • Please be sure to answer the question. Provide details and share your research!

But avoid

  • Asking for help, clarification, or responding to other answers.
  • Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.