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

чт, 10 нояб. 2022 г. в 01:32, Peter J. Holzer <hjp-pgsql(at)hjp(dot)at>:

> On 2022-11-09 12:57:23 -0600, Ron wrote:
> > On 11/9/22 10:17, Peter J. Holzer wrote:
> > > On 2022-11-07 14:40:40 -0600, Ron wrote:
> > > > On 11/7/22 10:57, Вадим Самохин 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. Has anything changed in the last ten years?
> > > > Or, is there a better way to copy file contents in a remote
> > > > database?
> > > >
> > > >
> > > > I'd write a small Python script, using the csv module to read the
> data and
> > > > psycopg2 to load it.
> > > If you use insert statements it will be significantly slower (which may
> > > not matter for small files or one-off actions). If you use copy_from()
> > > you don't have to parse it (but then why use Python at all?)
> >
> > If OP does not want to embed psql in his app, then he must find a
> different
> > solution. Python is such an option.
>
> Invoking a program written in Python is just as hard (or simple) as
> invoking a program written in C (psql). But that Python
> program is additional code in their project which has to be first
> written and then maintained.
>
> What they probably should do is write the code in the programming
> language they are already using for their app. And as I wrote just using
> copy (from within their app, not from psql or a python script or
> whatever) is probably the simplest solution. But since the OP chose not
> to tell us what programming language or library they use, it's hard to
> be more specific.
>
> 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!"
>

Fantastic, never thought pdo could have this kind of method --
https://www.php.net/manual/en/pdo.pgsqlcopyfromfile.php! Haven't checked
yet, but it seems it does exactly what I need -- and what its name implies!
Thanks a lot!