copy file from a client app to remote postgres isntance

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&gt;). 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?

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 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&gt;). 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.

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&gt;). 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

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&gt;). 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.

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!"

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!"

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.

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!"

чт, 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!