1

I am trying to use the copy command in postgres to upload the CSV file into the database using the below command

psql -h 127.0.0.1 -d target -U postgres -c "\copy test FROM 'E:\pg_test\test.csv'  delimiter ';'"

Whereas when using the multiple delimiter following error is observed.

ERROR:  COPY delimiter must be a single one-byte character

Is there a way to use multiple delimiter? Input file (CSV) couldn't be changed as the user doesn't have write access

Karthick88it
  • 601
  • 2
  • 12
  • 28
  • Probably not, but what does the file look like? – Laurenz Albe Jun 05 '23 at 07:37
  • 1
    You can pipe the file through a stream editor, that way the change is applied on the fly before you hand it over to `psql`, without writing or requiring the write permission. If the two delimiters are a plus `+` and a semicolon `;`, and you wish to use only the semicolon`;`, then `sed "s/+/;/g" E:\pg_test\test.csv | psql -h 127.0.0.1 -d target -U postgres -c "\copy test FROM stdin delimiter ';' "`. The command you're showing does use a single one-byte character, so it would be helpful to show the actual command that gave you the error message. – Zegarek Jun 05 '23 at 07:56
  • 1
    Following on @Zegarek 's comment but noting that you are on a Windows box, you should first copy the file to a writable location, then open the file in Notepad or some other plain text editor and "replace all" offending delimiters to the one you want to work with, save the file and then use that with `psql` – Patrick Jun 05 '23 at 08:18
  • 1
    @Patrick Your satire on typical Windows workflow is appreciated, although I missed the extended scenes where the user gives up, decides to print the file, grabs a pen and starts correcting by hand. Jokes aside, a manual workaround to an automated process is not a solution, so [`wsl --install`](https://learn.microsoft.com/en-us/windows/wsl/install), or join the [PowerShell cult](https://stackoverflow.com/q/127318/5298879). – Zegarek Jun 05 '23 at 08:53

0 Answers0