Re: copy file from a client app to remote postgres isntance

On 2022-11-07 19:57:04 +0300, Вадим Самохин wrote:
> I have an application that must copy a local file in csv format to a postgres
> table on a remote host. The closest solution is this one (https://
> stackoverflow.com/a/9327519/618020). It boils down to specifying a \copy
> meta-command in a psql command:
>
> psql -U %s -p %s -d %s -f - <<EOT\n here hoes a \copy meta-command \nEOT\n
>
> and executing it. But it's quite an unnatural way to write database
> code.

This looks like something you would use in another programming language
(maybe C or Python?) to construct a shell command.

Do you want do this once (from the shell) or from code?

If the former, starting psql and typing

\copy table_name from 'filename.csv' ...

Doesn't seem that unnatural to me.
(That just invokes COPY ... FROM STDIN on the server and feeds data to
it over the existing SQL connection.)

If it's the latter, your programming language's postgresql library
probably has a method for invoking copy.

> Has anything changed in the last ten years? Or, is there a
> better way to copy file contents in a remote database?

COPY is the fastest way to load data.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"