|Subject:||Re: Postgres \copy command
|Date:||Sat, 27 Jun 2020 09:18:26 +1000
|From:||Robert MacMillan <firstname.lastname@example.org>
On 27/06/2020 1:57 AM, Mark Bannister wrote:
> Psql is a terminal based front end
> (https://www.postgresql.org/docs/9.2/app-psql.html). If you have it
> installed locally you are going to have to issue a Windows command, not
> a sql command.
> On 6/26/2020 12:33 AM, Robert MacMillan wrote:
>> How does one correctly format the sql statement so that the "\" does not
>> cause issues.
>> The documentation says
>> "Otherwise, a more flexible approach is to replace the SQL's COPY
>> command with the psql's "meta-command" called \copy which which takes
>> all the same options as the "real" COPY, but is run inside the client
>> (with no need for ; at the end):
>> psql -c "\copy tbname FROM '/tmp/the_file.csv' delimiter '|' csv"
>> As per docs, the \copy command:
>> Performs a frontend (client) copy. This is an operation that runs an SQL
>> COPY command, but instead of the server reading or writing the specified
>> file, psql reads or writes the file and routes the data between the
>> server and the local file system. This means that file accessibility and
>> privileges are those of the local user, not the server, and no SQL
>> superuser privileges are required.
>> In addition, if the the_file.csv contains the header in the first line,
>> it can be recognized by adding header at the end of the above command:"
>> I have tried many different variations of paths and names etc but the
>> slash infront of the lower case copy seems to be causing the issues.
>> So this fails.
>> \copy public.syssels
>> FROM C:\PdoxRd\BotSysSels.csv
>> DELIMITER ',' csv
>> executesql(dbPG, sqlcmd)
>> get the error stack etc.
>> The error stack says
>> 1. An error was triggered in the 'executeSQL' method on an object of SQL
>> 2. ERROR: syntax error at or near "\";
>> Error while executing the query