pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
copy file from a client app to remote postgres isntance
11+ messages / 5 participants
[nested] [flat]

* copy file from a client app to remote postgres isntance
@ 2022-11-07 16:57  Вадим Самохин <samokhinvadim@gmail.com>
  0 siblings, 4 replies; 11+ messages in thread

From: Вадим Самохин @ 2022-11-07 16:57 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

Hi all,
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?

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: copy file from a client app to remote postgres isntance
@ 2022-11-07 17:29  Rob Sargent <robjsargent@gmail.com>
  parent: Вадим Самохин <samokhinvadim@gmail.com>
  3 siblings, 1 reply; 11+ messages in thread

From: Rob Sargent @ 2022-11-07 17:29 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 11/7/22 09:57, Вадим Самохин wrote:
> Hi all,
> 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 
> <http://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. B|ut it's quite an unnatural way to write database 
> code. Has anything changed in the last ten years? Or, is there a 
> better wayto copy file contents in a remote database?
There are bulk copy routines available. What is your architecture?

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: copy file from a client app to remote postgres isntance
@ 2022-11-07 17:51  Вадим Самохин <samokhinvadim@gmail.com>
  parent: Rob Sargent <robjsargent@gmail.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Вадим Самохин @ 2022-11-07 17:51 UTC (permalink / raw)
  To: Rob Sargent <robjsargent@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

Well, actually, just ordinary 3 tier architecture. Simple UI connected via
restful API with backend written in php, which copies some data in a remote
database, that's pretty much it.

пн, 7 нояб. 2022 г. в 20:30, Rob Sargent <robjsargent@gmail.com>:

> On 11/7/22 09:57, Вадим Самохин wrote:
>
> Hi all,
> 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\nand 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?
>
> There are bulk copy routines available.  What is your architecture?
>

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: copy file from a client app to remote postgres isntance
@ 2022-11-07 18:05  Rob Sargent <robjsargent@gmail.com>
  parent: Вадим Самохин <samokhinvadim@gmail.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Rob Sargent @ 2022-11-07 18:05 UTC (permalink / raw)
  To: Вадим Самохин <samokhinvadim@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

On 11/7/22 10:51, Вадим Самохин wrote:
> Well, actually, just ordinary 3 tier architecture. Simple UI connected 
> via restful API with backend written in php, which copies some data in 
> a remote database, that's pretty much it.
>
> пн, 7 нояб. 2022 г. в 20:30, Rob Sargent <robjsargent@gmail.com>:
>
>     On 11/7/22 09:57, Вадим Самохин wrote:
>>     Hi all,
>>     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
>>     <http://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. B|ut it's quite an unnatural way to write
>>     database code. Has anything changed in the last ten years? Or, is
>>     there a better wayto copy file contents in a remote database?
>     There are bulk copy routines available. What is your architecture?
>
We generally "bottom post" in this group.

Most things I find on the web suggest send the csv file to server and 
run COPY there.  Some show iterating over the csv, but critical to get 
csv off the client.

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: copy file from a client app to remote postgres isntance
@ 2022-11-07 18:24  Adrian Klaver <adrian.klaver@aklaver.com>
  parent: Вадим Самохин <samokhinvadim@gmail.com>
  3 siblings, 0 replies; 11+ messages in thread

From: Adrian Klaver @ 2022-11-07 18:24 UTC (permalink / raw)
  To: Вадим Самохин <samokhinvadim@gmail.com>; pgsql-general@lists.postgresql.org

On 11/7/22 8:57 AM, Вадим Самохин wrote:
> Hi all,
> 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 
> <http://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. B|ut it's quite an unnatural way to write database 
> code. Has anything changed in the last ten years? Or, is there a better 
> wayto copy file contents in a remote database?
> 

1) Set up postgres_fdw:

https://www.postgresql.org/docs/current/postgres-fdw.html

on local machine to point at table on remote machine and then \copy or 
COPY to local machine.

2) Copy the CSV file to remote machine and then do \copy or COPY there.

-- 
Adrian Klaver
adrian.klaver@aklaver.com





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: copy file from a client app to remote postgres isntance
@ 2022-11-07 20:40  Ron <ronljohnsonjr@gmail.com>
  parent: Вадим Самохин <samokhinvadim@gmail.com>
  3 siblings, 1 reply; 11+ messages in thread

From: Ron @ 2022-11-07 20:40 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 11/7/22 10:57, Вадим Самохин wrote:
> Hi all,
> 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 
> <http://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. B|ut it's quite an unnatural way to write database 
> code. Has anything changed in the last ten years? Or, is there a better 
> wayto 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.

-- 
Angular momentum makes the world go 'round.

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: copy file from a client app to remote postgres isntance
@ 2022-11-09 16:15  Peter J. Holzer <hjp-pgsql@hjp.at>
  parent: Вадим Самохин <samokhinvadim@gmail.com>
  3 siblings, 0 replies; 11+ messages in thread

From: Peter J. Holzer @ 2022-11-09 16:15 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: copy file from a client app to remote postgres isntance
@ 2022-11-09 16:17  Peter J. Holzer <hjp-pgsql@hjp.at>
  parent: Ron <ronljohnsonjr@gmail.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Peter J. Holzer @ 2022-11-09 16:17 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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?)

        hp

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

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: copy file from a client app to remote postgres isntance
@ 2022-11-09 18:57  Ron <ronljohnsonjr@gmail.com>
  parent: Peter J. Holzer <hjp-pgsql@hjp.at>
  0 siblings, 1 reply; 11+ messages in thread

From: Ron @ 2022-11-09 18:57 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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.


-- 
Angular momentum makes the world go 'round.





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: copy file from a client app to remote postgres isntance
@ 2022-11-09 22:32  Peter J. Holzer <hjp-pgsql@hjp.at>
  parent: Ron <ronljohnsonjr@gmail.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Peter J. Holzer @ 2022-11-09 22:32 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: copy file from a client app to remote postgres isntance
@ 2022-11-10 16:13  Вадим Самохин <samokhinvadim@gmail.com>
  parent: Peter J. Holzer <hjp-pgsql@hjp.at>
  0 siblings, 0 replies; 11+ messages in thread

From: Вадим Самохин @ 2022-11-10 16:13 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

чт, 10 нояб. 2022 г. в 01:32, Peter J. Holzer <hjp-pgsql@hjp.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@hjp.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!

^ permalink  raw  reply  [nested|flat] 11+ messages in thread


end of thread, other threads:[~2022-11-10 16:13 UTC | newest]

Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-07 16:57 copy file from a client app to remote postgres isntance Вадим Самохин <samokhinvadim@gmail.com>
2022-11-07 17:29 ` Rob Sargent <robjsargent@gmail.com>
2022-11-07 17:51   ` Вадим Самохин <samokhinvadim@gmail.com>
2022-11-07 18:05     ` Rob Sargent <robjsargent@gmail.com>
2022-11-07 18:24 ` Adrian Klaver <adrian.klaver@aklaver.com>
2022-11-07 20:40 ` Ron <ronljohnsonjr@gmail.com>
2022-11-09 16:17   ` Peter J. Holzer <hjp-pgsql@hjp.at>
2022-11-09 18:57     ` Ron <ronljohnsonjr@gmail.com>
2022-11-09 22:32       ` Peter J. Holzer <hjp-pgsql@hjp.at>
2022-11-10 16:13         ` Вадим Самохин <samokhinvadim@gmail.com>
2022-11-09 16:15 ` Peter J. Holzer <hjp-pgsql@hjp.at>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox